COUNT SELECT statement

[LITHIA]

Hi,

I am trying to count the results you will get from my query, but it doesn't seem to let me.

$query = mysql_query("SELECT COUNT(*) FROM messages WHERE username = '".$_SESSION[username]."' AND read = '0'", $db); 

When I try doing a result on that (eg. $result = mysql_result($query, 0, 0);), it gives me a parse error:
Warning: mysql_result(): supplied argument is not a valid MySQL result resource

But if I do:

$query = mysql_query("SELECT COUNT(*) FROM messages WHERE username = '".$_SESSION[username]."'", $db); 

it allows it! Which would be ok if I didn't need to specify "read = '0'" too, but I do.

Can anyone help me here? I'm new to php and I'm not sure what I'm doing wrong. If no one can help, I'll try a different, more sloppy, method.

[visualAd]

The error you are getting most probably means that your query has failed. It is good practice to test for faliure and PHP provides a function which will be able to tell you why your query failed. Change you code and see what you get from this:

$query =  $query = "SELECT COUNT(*) FROM messages WHERE username = '".$_SESSION[username]."' AND read = '0'";

if (! $result = mysql_query($query, $db)) {
    echo(mysql_error($db));
    
    exit;
} 

[LITHIA]

Thanks,

The result I have got from that is "Query was empty"

I don't know what that's suppost to mean. Can you see anything wrong with the query? Have I got the AND wrong? I've never used AND in a SELECT statement before, and I only get the error when I include it.

Thank you

[LITHIA]

sorry i misread it. Here's the proper result:

"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 looks like what I thought was the problem... I don't know what's wrong though

[visualAd]

If the read column is of numeric or integer typ then your query should look like this:

"SELECT COUNT(*) FROM 
    messages WHERE username = '".$_SESSION[username]."' AND read=0";

[LITHIA]

Tried that and still get the same error, except without an extra '

[visualAd]

I can't see anything obvious. Can you also post the result of echo($query) too?

[LITHIA]

I did echo($query); and got SELECT COUNT(*) FROM messages WHERE username = 'admin' AND read = 0

Is that what you meant?

[LITHIA]

i think i just realised something. I tried using that sql in phpMyAdmin, and I noticed it highlighted READ in purple and capitals.

I think that means READ is a sql thingy, so maybe that's why it's not working? I'll try changing the field name and see if that helps.

[visualAd]

It doesn't like the word read. MySql appears to have it as a reserved word. So just enclose it in back ticks. `read`.

[LITHIA]

Ok I got it to work now! As READ was a sql thing, I had to put my field which was called read in '' and it works fine now.

End code:[PHP]$query = mysql_query("SELECT COUNT(*) FROM messages WHERE username = '".$_SESSION[username]."' AND 'read' = 0");
$result = mysql_result($query, 0);
echo($result);[/[PHP]
Works great

Thanks for the help! I guess it's a good idea to put '' around your field names... habit of mine not to though

[visualAd]

The single quote is really used for strings you should really use back ticks `` its the key to the left of the number 1.