[RESOLVED] Count message Help!!

kkwilliam

Hello,

This script works to count all messages a member has, but I also need it to count all the new messages. How do I do that?

My table has a field called status: it reads two ways read and unread I need to count the unread messages they are considered new messages.

Once again Thank you for your help in advanced.

<?php
//Get your private message count
$query = "SELECT count(*) as numRows FROM mail WHERE mem_id_to='$user'";
$result = mysql_query($query) or die('MySQL Error: ' . mysql_error());
$aRow = mysql_fetch_array($result)

echo $aRow['numRows'];

bradgrafelman

Do you want two separate numbers returned, or do you want them added together, or ...? If you want two separate numbers... you'd simply need to execute a separate SQL query to do a COUNT(*) on rows where status='unread' .

kkwilliam

Thank you Brad,

I would like two separate numbers. The code above gives me the total number of messages. I also need the total of unread messages. I was hoping to get this with one SQL query.

How would I write a separate SQL query?

kkwilliam

Would it be like this a separate SQL then?

<?php
//Get your private message count
$query = "SELECT count(*) as numRows FROM mail WHERE mem_id_to='$user' and status='unread'";
$result = mysql_query($query) or die('MySQL Error: ' . mysql_error());
$aRow = mysql_fetch_array($result)

echo $aRow['numRows'];

What would the echo be then?

bradgrafelman

You can only do with a single query if you use sub-queries (so essentially it's two separate queries still).

Your coding example above is only executing one SQL query. You actually need to do an entirely separate SQL query.

kkwilliam

Hello Brad,

What you the separate SQL query be?

kkwilliam

Hello and Thank you so much for the help!

This script counts all messages a member has!

echo $aRow['numRows'];

This script counts all unread messages a member has!

<?php
//Unread message count
$query = "SELECT status, count(*) as su FROM mail WHERE mem_id_to='$user' GROUP BY status='read'";
$result = mysql_query($query) or die('MySQL Error: ' . mysql_error());
$suRow = mysql_fetch_array($result)

<? echo $suRow['su']; ?>

This works for me.... If there is a suggestion on how to make this better please leave a post...... Once again Thank you for the help!