Tuesday 4 September 2018

Mysql query does not work in php pdo

This returns results:

$query = $dbh->prepare('SELECT * FROM groups WHERE id LIKE :id ORDER BY id');
$query->bindValue(':id', $this->id.'_');

This does not:
$query = $dbh->prepare('SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id WHERE g.id LIKE :id AND d.desc_type=1 ORDER BY g.id');
$query->bindValue(':id', $this->id.'_');

And yet if I run the SQL query below in WebMin ("0002_" is the value being bound in the above), then I get results.
SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id WHERE g.id LIKE "0002_" AND d.desc_type=1 ORDER BY g.id

So why does the PDO dislike the query in my second piece of code above?

Okay, I nailed it:
As I said in my initial question, the code below doesn't return results:
$query = $dbh->prepare('SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id WHERE g.id LIKE :id AND d.desc_type=1 ORDER BY g.id');
$query->bindValue(':id', $this->id.'_');

This code returns the expected results:
$query = $dbh->prepare('SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id AND d.desc_type=1 WHERE g.id LIKE :id ORDER BY g.id');
$query->bindValue(':id', $this->id.'_');

Strangely, either SQL query will return results when run from within Webmin, but it seems that the PHP PDO doesn't like having the "AND d.desc_type=1" as part of the WHERE clause and needs it in the LEFT JOIN ON clause.

0 comments:

Post a Comment