Mysql select where syntax error

Question

I am having a hard time trying to figure out how to fix the syntax in the code below.

$key = 4;
$column = "five_year";

$query_for_rate = "SELECT $column key FROM $usertable WHERE key=$key";

$get_rate = mysql_query($query_for_rate) or die(mysql_error());

echo $query_for_rate. "<br>";
echo $get_rate;

if($get_rate){
while($row = mysql_fetch_array($get_rate)){

echo $row;

    }
}

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 'key FROM current_rates WHERE key=4' at line 1

Thank you for the replies. The query is now working, I think. When I echo $get_rate I get "Resource id #5". My problem now is I cannot get my while loop to produce any results. Thank you!

`key` is reserved. Rename your column, also columns in the `select` need to be separated by commas, or are you trying to make that an alias? — chris85, Apr 04 '17 at 02:41

Jonathan · Answer 1 · 2017-04-04T02:49:41.313

1

Key is a reserved word. You need to quote it with backticks.

$query_for_rate = "SELECT $column `key` FROM $usertable WHERE `key`=$key";

However I suspect that won't work but rather you'll need to do this instead.

$query_for_rate = "SELECT $column `key` FROM $usertable WHERE `$column`=$key";

Since you need to reference the actual column name not what you aliased it as.

edited Apr 04 '17 at 02:49

answered Apr 04 '17 at 02:42

Jonathan

2,778
13
23

`$column` != `key`. – chris85 Apr 04 '17 at 02:43
According to what he wrote $column is aliased as key. – Jonathan Apr 04 '17 at 02:44
You can't use a column alias in the `where`... or you couldn't.. http://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause As I read it OP wants to select `five_year` and `key`. – chris85 Apr 04 '17 at 02:45
@chris85 please read my answer and you'll see I'm well aware of that. – Jonathan Apr 04 '17 at 02:46
That doesn't show that. `SELECT $column key` is invalid, for multiple reasons. – chris85 Apr 04 '17 at 02:47
We're talking about different things here, but yes I forgot to backtick the alias or quote it. – Jonathan Apr 04 '17 at 02:50

Mysql select where syntax error

1 Answers1