Solved: SQL Statement Works in PHPMyAdmin but not PHP

A while back I had this problem that took me about a day to resolve. Specifically, I had a SQL Statement that worked in PHPMyAdmin but not in PHP. Which makes no sense.

But, of course, in a world where your entire 30,000 line program can die because you forgot a semi-colon, it actually does make sense.

I was using PDO prepared statements to ensure that the data I was inserting was safe. Through some Object Oriented magic this stuff didn’t look as plain to me as it appears here, and it probably won’t to you either. The point is what matters.

Consider a table (main) with at least one column (id) and another table (other) with at least one column (main_id):

$sql = 'SELECT * FROM main m LEFT JOIN other o ON ( m.id = ? )';
$values = array('o.main_id');
$dbh = new PDO('mysql:host=localhost;dbname=test_db', 'fake_username', 'fake_password');
$sth = $dbh->prepare($sql);
$sth->execute($values);

This query won’t work. Why not? Because the table name (o.main_id) isn’t recognized as a table name. PDO inserts it into the query as a string. No errors are thrown. A working example would be:

$sql = 'SELECT * FROM main m LEFT JOIN other o ON ( m.id = o.main_id )';
$dbh = new PDO('mysql:host=localhost;dbname=test_db', 'fake_username', 'fake_password');
$sth = $dbh->prepare($sql);
$sth->execute();

I hope this helped solve your SQL Statements error (it should work both in PHPMyAdmin and PHP now).