Counting Unread Messages from Database

[CoryClark]

Okay, before I start: Mysql Version: 5.0.81, PHP Version: 5.2.

 

What I want to do is display the number of unread messages from my `pms` table. By checking `touser` and `read`.

 

The following code, works fine, but it doesn't check to the read field (unread posts are marked 0, while read ones are 1), instead it just displays the number of messages in the inbox, and it works perfectly:

$toCount = "SELECT COUNT(*) FROM pms WHERE touser='$touser'";
$resCount = $database->query($toCount);
$row = mysql_fetch_array($resCount, MYSQL_NUM);
echo "You have <b>" . $row[0] . "</b> messages in your inbox.";

 

However, when I try to check for unread messages, by using the following code:

$toCount = "SELECT COUNT(*) FROM pms WHERE touser='$touser' AND read='0'";
$resCount = $database->query($toCount);
$row = mysql_fetch_array($resCount, MYSQL_NUM);
echo "You have <b>" . $row[0] . "</b> unread messages in your inbox.";

 

I get the following error:

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

 

I have searched the web countless times, and cannot find a solution to my problem. I am fairly new to Mysql and PHP and I don't know if I'm using AND in the wrong place or what...but any help or suggestions would be greatly appreciated.

 

EDIT: $touser is simply declared as $touser = $session->username; further above...

[mikesta707]

seems like your query is failing. try adding

or die(mysql_error());

on the same line as your query statement and see what it returns

[Andy-H]

Try changing your query to this:

 

 

$toCount = "SELECT COUNT(*) FROM pms WHERE touser='$touser' AND `read`='0' ";

Not 100% sure but I think read may be a MySQL keyword.

[CoryClark]

Wow it's funny how two simple ``s can mess with your day. Thank you Andy-H, that fixed everything, I guess it is a keyword.

[Andy-H]

LOL np, I've had that problem thousands of times myself. There's WAY too many reserved keywords in MySQL.

[teynon]

Just my 2 cents, but I dont think that is MySQL's fault. You should shy away from using common words as names. READ, TABLE, SELECT, etc are all obvious things you should not name columns in your mysql database. May I even suggest column prefixes (IE: mail_user)