I'm working on a group project from school which requires selecting from a Postgres database with php.
I tested my queries on the psql dbms before trying them in the php interface. This is my test query:
SELECT m.movieid, m.tomatourl FROM movies m WHERE title = 'Beowulf & Grendel';
The query does return the information from the database I need, however when using this in php it returns nothing.
pg_last_error() says nothing.
In what way can I ensure that I can select titles with ampersands(&) in them?
I've tried seperating the string and putting them back together with sql code:
SELECT m.movieid, m.tomatourl FROM movies m WHERE title = 'Beowulf '||chr(38)||' Grendel'
I've tried escaping the string
This is an example of some of my php code:
$query = 'SELECT m.movieid, m.tomatourl FROM movies m WHERE title = $1';
pg_prepare($conn, "getmovie", $query) or die(pg_last_error());
$result = pg_execute($conn, "getmovie", $i) or die("Query failed: ". pg_last_error());
$movie = pg_fetch_array($result, NULL, PGSQL_BOTH);
This will work as long as the string in the $i array does not have an ampersand in it.
I would just change the database to not have an ampersand, but I don't really have control over it.
Is there some way to do a select statement like this using the php postgres functions?
The problem seems to have been caused by the the quotes that are around the string that passed in to the sql, by passing the string directly through the prepared statement it is like this "Beowulf & Grendel"
when it has to be passed to the database like this 'Beowulf & Grendel'
It also seems that even though it wasn't showing in var_dump() directly in the string printout it
was actually sending it as this SELECT m.movieid, m.tomatourl FROM movies m WHERE title = 'Beowulf & Grendel'; The the only thing that gives it away it the character count in the var_dump and not the printout of the string. The fix for this was to do html_entity_decode() on the title passed in.
Special thanks to DarkBee and Daniel Verite for helping solve this issue.