duanque2413 2019-08-02 14:29
浏览 144

pg_query_params不允许带引号的占位符

I'm working on a postgreSQL query that is searching for a column that is contained in a geometry and it works perfectly if run it through pgAdmin but I cannot make it works with pg_query_params through PHP

this is the working query

select a.full_address from streets a, gisgeometry b where b."Name" ILIKE ANY ('{%Barnet%}') AND ST_CONTAINS(b.sp_geometry,a.wkb_geometry) and a.full_address ilike '%high street%' group by a.full_address ORDER BY a.full_address LIMIT 100;

if asked through PHP i can make it work with pg_query

$result = pg_query($connect,'select a.full_address from streets a, gisgeometry b where b."Name" ILIKE ANY ('.$bbox.') AND ST_CONTAINS(b.sp_geometry,a.wkb_geometry) and a.full_address ilike '.$pattern.' group by a.full_address ORDER BY a.full_address LIMIT 100;');

not with pg_query_params

$result = pg_query_params($connect,'select a.full_address from streets a, gisgeometry b where b."Name" ILIKE ANY ($1) AND ST_CONTAINS(b.sp_geometry,a.wkb_geometry) and a.full_address ilike $2 group by a.full_address ORDER BY a.full_address  LIMIT 100;', array($bbox, $pattern));

I do of course sanitation and everything in PHP and with pg_query I can make it works in this way (I mean add the single quotes around the variables)

  $pattern = fixtags($_POST['findthis']);
  $pattern = str_replace(" ", "%", $pattern);
  ## add single quotes and % around the pattern
  $pattern = "'%".$pattern."%'";

  $bbox = fixtags($_POST['ext']);
  $bbox = explode(",", $bbox);

  foreach ($bbox as &$value) {
    $value = "%$value%";
  }

  $bbox = implode(",", $bbox);
  ## add single quotes and {} around the pattern
  $bbox = "'{". $bbox ."}'";

with $pattern = "%".$pattern."%"; and $bbox = "{". $bbox ."}"; without single quotes or with quotes as above the error is:

Got error 'PHP message: PHP Warning:  pg_query_params(): Query failed: ERROR:  array value must start with "{" or dimension information in ...php on line 367
PHP message: ..
  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥20 数学建模,尽量用matlab回答,论文格式
    • ¥15 昨天挂载了一下u盘,然后拔了
    • ¥30 win from 窗口最大最小化,控件放大缩小,闪烁问题
    • ¥20 易康econgnition精度验证
    • ¥15 msix packaging tool打包问题
    • ¥28 微信小程序开发页面布局没问题,真机调试的时候页面布局就乱了
    • ¥15 python的qt5界面
    • ¥15 无线电能传输系统MATLAB仿真问题
    • ¥50 如何用脚本实现输入法的热键设置
    • ¥20 我想使用一些网络协议或者部分协议也行,主要想实现类似于traceroute的一定步长内的路由拓扑功能