Page 1 of 1

Back ticks in MySQL query using PHP

Posted: Mon Jan 05, 2009 7:00 pm
by mikebr
I thought that the back tick was an optional in MySQL queries and was surprised to find the following query give me an error:

Code: Select all

<?php
 
// 1st example
 
$qry = ("SELECT tab.*, table_locations.tl_name FROM tab, table_locations WHERE table_locations.tl_id = $tl_id AND tab.t_tl_id = table_locations.tl_id");
 
?> 
but the following two queries did not... unless the tables and columns where back ticked through to as far as the 3rd example there was an error as:

Mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tab, table_locations WHERE table_locations.tl_id = 1' AND `tab`.`t_tl_id`' at line 1

Code: Select all

<?php
 
// 2nd example
 
$the_qry = ("SELECT `tab`.*, `table_locations`.`tl_name` FROM `tab`, `table_locations` WHERE `table_locations`.`tl_id` = $tl_id AND `tab`.`t_tl_id` = `table_locations`.`tl_id`");
 
// 3rd example
 
$the_qry = ("SELECT `tab`.*, `table_locations`.`tl_name` FROM `tab`, table_locations WHERE table_locations.tl_id = $tl_id AND tab.t_tl_id = table_locations.tl_id");
?> 
Can anyone explain why this might be?

Thanks

Re: Back ticks in MySQL query using PHP

Posted: Mon Jan 05, 2009 7:12 pm
by Eran
I'm seeing a hanging single quote in the middle of the query (in the error message) -
WHERE table_locations.tl_id = 1' AND `tab`.`t_tl_id`
If it really is there, it probably came from $tl_id. Check why it's there.

Re: Back ticks in MySQL query using PHP

Posted: Mon Jan 05, 2009 7:24 pm
by mikebr
Don't know to be honest, I have recreated the error again, I have also printed out the query... here is the error:

Query: SELECT `tab`.*, `table_locations`.`tl_name` FROM tab, table_locations WHERE `table_locations`.`tl_id` = 1 AND `tab`.`t_tl_id` = table_locations.tl_id

Mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tab, table_locations WHERE `table_locations`.`tl_id` = 1 AND `tab`.`t_tl' at line 1

and the query used for the above error:

Code: Select all

 
 
$qry = ("SELECT `tab`.*, `table_locations`.`tl_name` FROM tab, table_locations WHERE `table_locations`.`tl_id` = $tl_id AND `tab`.`t_tl_id` = table_locations.tl_id");
 
 
Cheers

Re: Back ticks in MySQL query using PHP

Posted: Mon Jan 05, 2009 7:31 pm
by Eran
offhand, I would say 'tab' is a reserved word. But it doesn't appear in the list of MySQL reserved words... possibly a bug in the version you have? in any case backticks protects against reserved words, so keep on doing what you are doing :)

Re: Back ticks in MySQL query using PHP

Posted: Tue Jan 06, 2009 1:37 am
by josh
back ticks tell mysql to use literal field names and not parse reserved keywords, but it looks like you're running into other unrelated issues