[musicalmidget]

Hi.

I'm having a few problems with some code on my site.

Untill now, my "messages" function was...

PHP Code:

function messages() {
$sql = "SELECT * FROM messages";
$query = mysql_query($sql);
$count = mysql_num_rows($query);
?>
<tr>
<td class="table">There are currently <a href="messages.php"><b><?php echo $count ?></b></a> active messages.</td>
</tr>
<?php
}

...and everything worked fine.

However, I recently tried adding a new feature to the function and changed the code to this...

PHP Code:

function messages() {
$sql = "SELECT * FROM messages WHERE read='no'";
$query = mysql_query($sql);
$count = mysql_num_rows($query);
?>
<tr>
<td class="table">There are currently <a href="messages.php"><b><?php echo $count ?></b></a> unread messages.</td>
</tr>
<?php
}

...and the code no longer works. I am presented with this error message instead:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource.

I added the new field to the database and everything so it is set up correctly as far as I can tell, but I just can't understand why I'm getting this error.

I've been trying for a while to fix it and still just can't be sure what is causing the problem, so if someone could enlighten me I would be very grateful.

Thanks.

[mdhall]

Make sure that the values stored in the database match the query criteria, i.e., things like "no" and "No" must be the same. If you have WHERE read='no', then the data in the db must also be saved as 'no', not as 'No' which is capitalized. Sounds like something very basic, I know, but someting to double-check.

[musicalmidget]

It usually is something basic with me.

Unfortunately though, that's not it. "No" is all in lowercase in the database, just as it is written in the query.

[mdhall]

The table name you are querying ( "read" ) is the same as the db field name?

Maybe try something like this and see what you get...

function messages() {
$query = mysql_query("SELECT * FROM messages WHERE read='no'") or die (mysql_error());
$result= mysql_query($query);
$count = mysql_num_rows($result);
if(mysql_num_rows($result) == 0){
echo("Nothing to Display!");
}else{
echo "$count results";
}

[musicalmidget]

Hi.

When I tried the code above I got this error instead:

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='no'' at line 1

The name of the field in the database is definately "read", but there is definately something it doesn't like about it. :-/

[nd2]

you will find that read is actualy a mysql reserved word.
there is however a way arround it by simply placing backticks arround the table name which are normal not required.

SELECT * FROM messages WHERE `read`='no'

[mdhall]

I didn't know that. Thanx.

[musicalmidget]

Brilliant, everything works fine now.

I've seen queries like this before but didn't know why they were written in this way. I'll bare this in mind for the future.

Thanks for the help.

[nd2]

no problem.