num_rows handing me an Error

[coder007]

Hey,

 

Here is the snippet of code that seems to be causing the problem:

 

		$check_messages = mysql_query("SELECT mid FROM messages WHERE tuid={$_SESSION['uid']} AND read=0");
        $check_messages1 = mysql_num_rows($check_messages);

 

And here is the error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /login.php on line 98

 

 

The error appears to be in the "read=0" at the end of the mysql_query. As soon as I remove this, the query works without error. The "read" and "tuid" values are intergers, not varchar.

 

 

Thanks!

[timmah1]

Maybe putting single quote around the 0

<?php
$check_messages = mysql_query("SELECT mid FROM messages WHERE tuid={$_SESSION['uid']} AND read='0'");
$check_messages1 = mysql_num_rows($check_messages);
?>

[corbin]

Have you tried echoing out the query to see what it looks like?  (To make sure it looks as expected.)

 

 

 

Worst case you could just add "or die(mysql_error());" after the mysql_query call.  Then, if the query fails, it would tell you the error.

 

 

(You would want to take the or die() out once you've fixed the problem.)

[roopurt18]

It's possible that read is a reserved word in MySQL, so escape it in backticks.

 

<?php

$check_messages = mysql_query("SELECT mid FROM messages WHERE tuid={$_SESSION['uid']} AND `read`=0");

$check_messages1 = mysql_num_rows($check_messages);

?>

 

If it's an int field then the single quotes around the zero should have no effect.  It could also be that your $_SESSION['uid'] contains funky characters (like a single quote or semi-colon) that are terminating the statement early.

[corbin]

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

 

 

 

Whoa....  It is....

 

 

In which case I would personally rename the column.  But, if you've already designed some of your program and it depends on the column name being read, that's probably not worth the trouble.

[roopurt18]

I've been bit in the butt so many times by MySQL and reserved words that all of my MySQL statements have backticks around all of my identifiers.  You especially have to worry about upgrading an existing MySQL installation to a new version where new reserved words have been introduced; in that case all sorts of queries that worked forever may break.

 

I haven't used MySQL in about a year and a half now though; we use PostgreSQL at my current company and it doesn't seem to have this problem.

[corbin]

I have a kind of strange habit of prefixing all of my column names.  (For example, if read where in a table named posts I probably would've named it post_read.)

 

So since I do that I hardly ever run into problems.

 

 

Looking through the reserved word list though, some of them probably come up a lot.

[waynew]

He should be using or trigger(mysql_error) anyway.

[corbin]

He should be using or trigger(mysql_error) anyway.

 

 

But then you have to explain how to change error reporting, and how to make sure errors are shown.  I'm lazy.