Counting # of results from MySQL.

1 query, trying to count many different things...

[xKillswitchx]

Hello all,
I am having a bit of a problem. I will try to explain. I use Joomla CMS and have a bridge connecting VBulletin.

I wrote own custom module to retrieve PM number from VBulletin for Joomla userid (the bridge just adds Joomla user to VB and visa versa, and logs in on each system). To do that, I ran query to match Joomla username to VB username, and grabbed userid from that username in VB table.

Now, I ran the query to grab PM info from the userid I got from above query... this is both together...

// First, we attach Joomla username to VB3 username to get userid to match ...
$user_query = "SELECT userid, username FROM vb3user WHERE '" .$my->username. "' = username LIMIT 1";
$user_result = mysql_query($user_query) or die('MySQL Query Error Code 1');
$row = mysql_fetch_object($user_result);

// Second, we use the userid we got from above to retrieve our PM information...
$query = "SELECT userid, messageread FROM vb3pm WHERE '" .$row->userid. "' = userid";
$result = mysql_query($query) or die('Error in query 1');

// Count total # of rows for total # of pms we have...
$count = mysql_num_rows($result);

That was the queries I was using, nothing wrong here. It shows my total # of PM's correctly.

Now, what I want to get from that query is the total # or rows where messageread = 0, for my unread PM #. I really could put together a block of code to count that and looking for a little experience/help :D

I've tried setting an empty variable, then creating a loop with messageread == '0' then incrementing that variable for each row, then echoing off that variable, but no success. Thanks for any help you can give me. Will gladly share results once done with anyone interested.

[PHP_Chimp]

$query = "SELECT userid, messageread FROM vb3pm WHERE '" .$row->userid. "' = userid";
$result = mysql_query($query) or die('Error in query 1');
// Count total # of rows for total # of pms we have...
$count = mysql_num_rows($result);
// [b]NEW STUFF[/b]
$unread = 0;
// loop through all of your results row by row
while ($row = mysql_fetch_array($result)) {
if ($result['messageread'] == 0) {
$unread += 1;
}
}
echo $unread;

[xKillswitchx]

Sweet, thanks alot will try that. I had tried something similar to that already, so I am happy to see I was close. Instead, I had set a variable equal to zero, and ran a loop in which I $unread++.

Thanks for your response, will give it another go.

[ZeroFunk]

That (mysql_num_rows) is one way, but benchmarks show that to be inefficient.

try something like this:

$mycount = mysql_result(mysql_query(SELECT COUNT(item) FROM TABLE WHERE `blah` = "'.$blah.'"),0);

[edited by: ZeroFunk at 4:40 am (utc) on Dec. 2, 2007]