COUNT SELECT statement

Hi,

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

PHP Code:

---

$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:

PHP Code:

---

$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.

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:

PHP Code:

---

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

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


---

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

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

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

Code:

---

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

---

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

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

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

Is that what you meant?

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.

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

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 ;)

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