Php mysql WHERE clause error

Question

Can anyone see why this isn't working?

$category = 1;
$CategoryResult = mysql_query("SELECT * FROM Category WHERE Index = '$category'");
if (!$CategoryResult) 
    die("Could not retrieve results from category $category: " . mysql_error());

Mysql error as follows:

Could not retrieve results from category 1: 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 'Index = '1'' at line 1

Index is an auto-increment primary key of the table Category.

pleasedontbelong · Accepted Answer · 2011-02-22 17:23:00Z

3

maybe index is a reserved word . try this

$CategoryResult = mysql_query("SELECT * FROM Category c WHERE c.`Index` = '$category'");

and check if index is a string or an integer

answered Feb 22, 2011 at 17:23

pleasedontbelong

20.1k12 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pekka Over a year ago

This is probably it. index is a very bad column name, I would change it.

RichardTheKiwi · Accepted Answer · 2011-02-22 18:52:03Z

2

This should be your code

$category = 1;
$CategoryResult = mysql_query("SELECT * FROM Category WHERE `Index` = $category");
if (!$CategoryResult) 
    die("Could not retrieve results from category $category: " . mysql_error());

Normally I would preach about mysql_real_escape_string, but if $category is under your control (such as from another query) - and it is guaranteed to be an int, then this code is safe.

Index is a reserved word - it must be backticked
Do not quote numerics. MySQL does has rules to convert (string) literals being compared to numbers, but unless you are familiar with the rules, just use proper numbers.

Example

select * from
(
select 1 `index`
union all
select 2
union all
select 3
) X
where `index` = '1-2-3-4'
   or `index` = '3.0Y!'

Output

`index`
1
3

edited Feb 22, 2011 at 18:52

answered Feb 22, 2011 at 17:37

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

2 Comments

The Scrum Meister Over a year ago

Your last statement is incorrect. MySql will convert it to a number.

RichardTheKiwi Over a year ago

@The Scrum Meister - You are right. I updated the answer to make it a warning/recommendation instead.

corrodedmonkee · Accepted Answer · 2011-02-22 17:24:18Z

1

Two problems I can see. If it is a primary key, you should be using WHERE Index = " . $category . ")

Secondly, Index is a reserved word in MySQL.

answered Feb 22, 2011 at 17:24

corrodedmonkee

3731 silver badge5 bronze badges

Comments

Halil Özgür · Accepted Answer · 2011-02-22 17:29:52Z

1

You should quote Index, as it's a MySQL keyword; like:

SELECT * FROM Category WHERE `Index` = $category;

And the single quotes are unnecessary and incompatible to ANSI SQL, assuming $category is numeric.

edited Feb 22, 2011 at 17:29

answered Feb 22, 2011 at 17:24

Halil Özgür

16k6 gold badges51 silver badges58 bronze badges

Collectives™ on Stack Overflow

Php mysql WHERE clause error

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related