This MySQL query fails even though it should not

Question

$NumPMs = mysql_query("SELECT * FROM messages WHERE to_user='$name' AND read='0'") or die (mysql_error());
$numofpms = mysql_num_rows($NumPMS);

I get this 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 'read='0'' at line 1

It doesn't make sense. It should be working.

Help would be appreciated!

EDIT:

I changed the query to this: $NumPMs = mysql_query("SELECT * FROM messages WHERE to_user='$name' AND read='0'") or die (mysql_error());

It works. Although, my mysql_num_rows flares an error too: Warning: mysql_num_rows() expects parameter 1 to be resource, null given in /home/recatio2/public_html/Main.php on line 78

Please help.

have you tried printing out your query? Does it turn out how you expect? — Novocaine, Jun 27 '13 at 13:53

Electronick · Answer 1 · 2013-06-27T14:15:21.607

2

you should escape field names and $name before adding it to SQL.

$NumPMs = mysql_query("SELECT * FROM `messages` WHERE `to_user`='". mysql_real_escape_string($name)."' AND `read`='0'") or die (mysql_error());

edited Jun 27 '13 at 14:15

answered Jun 27 '13 at 13:53

Electronick

1,122
8
15

This is probably true (assuming $name wasn't escaped previously), but it does not relate to the error. – Mike Pelley Jun 27 '13 at 13:55
I do this and the query works, although the mysql_num_rows doesn't. It errors with this: Warning: mysql_num_rows() expects parameter 1 to be resource, null given in /home/recatio2/public_html/Main.php on line 78 All of the answers give this. Please help! – Karten Jun 27 '13 at 13:59
Mike Pelley, read full answer. escape $name (means values) and FIELDS (means field names, columns). – Electronick Jun 27 '13 at 14:14
Karten, check register of your variable names. – Electronick Jun 27 '13 at 14:14
@Electronick Yes, my bad! And your edits make that even clearer. – Mike Pelley Jun 27 '13 at 14:22

score 2 · Answer 2 · answered Jun 27 '13 at 13:53

2

read is a reserved keyword in MySQL. You can escape all of your field names with backtick:

$NumPMs = mysql_query("SELECT * FROM messages WHERE `to_user`='$name' AND `read`='0'") or die (mysql_error());

answered Jun 27 '13 at 13:53

Mike Pelley

2,939
22
23

I do this and the query works, although the mysql_num_rows doesn't. It errors with this: Warning: mysql_num_rows() expects parameter 1 to be resource, null given in /home/recatio2/public_html/Main.php on line 78 – Karten Jun 27 '13 at 13:59
Problem there is you return the query to $NumPMs but then use the mysql_num_rows() against $NumPMS - PHP is case sensitive – Kickstart Jun 27 '13 at 14:06
Thank you kickstart! It works now. I didn't notice that. Thanks!!! – Karten Jun 27 '13 at 14:13

score 1 · Answer 3 · answered Jun 27 '13 at 13:55

READ is a mysql_reserved word; you can see the list at: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

One solution is to escape it with backticks:

SELECT * FROM messages WHERE to_user='$name' AND `read`='0'

Another is to rename the field to something else - it's not good practice to use reserved words for column or table names.

score 0 · Answer 4 · answered Jun 27 '13 at 13:56

$NumPMs = mysql_query("SELECT * FROM messages m WHERE m.to_user='$name' AND m.read='0'")

Use an alias for the table to avoid conflicts with reserved MySQL keywords.

It is also a good idea to use an alias to make it explicitly clear which columns are referenced from which table. You will see the benefit of this once your queries become more complex and involve more tables.

This MySQL query fails even though it should not

4 Answers4