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: ..