PHP MySQL fails whenever WHERE statement is present

Question

I am trying to make a simple query to a small MYSQL table, but when I insert the Where clause, I suddenly get an invalid query error (added a while(mysql_fetch_array ){} when working without the where). The MYSQL console gives a 1064 (syntax) error, however, I checked the MYSQL documentation and I am using the proper syntax as far as I can determine.

<?php
$ind=rand(1,3);
$quote=Null;
$sign=Null;
$afil=Null;
$con=mysql_connect(localhost,root,********);//connect to database
mysql_select_db("phone_site",$con);//select table
$query="SELECT * FROM quotes WHERE index=$ind";//get the row for that index
$data=mysql_query($query);
//print out text
print ("<p id=\"quote\">" . $data['quote'] . "</p>");
print ("<p id=\"ename\">" . $data['sign'] . "</p>");
print ("<p id=\"afill\">--  " . $data['afil'] . "</p>");
mysql_close($con);//close connection
?>

Anyone know what the problem is? I'm using XAMPP. Is there something wrong with its MYSQL?

Answer 1

INDEX is one of MySQL's Reserved Words :

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions.

Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Schema Object Names”

If you have a column with that kind of name, you must quote the column name -- see Schema Object Names :

The identifier quote character is the backtick (“`”)

Which means your query should look like this :

$query="SELECT * FROM quotes WHERE `index`=$ind";

To complete my answer, after seeing dustmachine's one, who said :

I'm a little surprised that it didn't complain when the table was created.

I suppose the table has been created with some tool like phpMyAdmin or MySQL Workbench ; and those generaly (always, for some) quote column names, to avoid that kind of problem.

Edit after the comment : I didn't see, but you are using this kind of code :

$data=mysql_query($query);
print ("<p id=\"quote\">" . $data['quote'] . "</p>");

The mysql_query function doesn't diretly return the data : it only returns a "ressource" (quoting the manual's page) :

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Not sure it'll solve all your problems (nothing being displayed, not even the raw HTML tags is odd), but it might at least help a bit...

Answer 2

I think the problem is the use of the word index -- that's a reserved word with special meaning and shouldn't be used as a column name. I'm a little surprised that it didn't complain when the table was created.