...

View Full Version : Resolved How to count mysql_num_rows



masterofollies
03-14-2010, 04:29 PM
Need some help. I can check if there is 0 matching for using mysql_num_rows but how do I get it to show the total number matching?


$fquery = mysql_query("SELECT currentaction FROM people WHERE currentaction='Shopping' AND currentaction='Purchasing'");



if (mysql_num_rows($fquery) == '0')
{
$amount = 0;
}
else {

}

So basically I want it to show how many people are currently shopping and purchasing in total.

koko5
03-14-2010, 05:18 PM
So basically I want it to show how many people are currently shopping and purchasing in total.

Hi, try something like this:

$fquery = mysql_query("SELECT username,COUNT(*) AS CNT FROM people
WHERE currentaction='Shopping' AND currentaction='Purchasing'
GROUP BY username ORDER BY username ASC");

p.p.: You can use userid instead of username.
Also I'm not sure about 'Shopping' AND 'Purchasing' or 'Shopping' OR 'Purchasing' -> depends on your database schema
Regards :)
Edit:Oh, maybe you are searching "with rollup"?

$fquery = mysql_query("SELECT username,COUNT(*) AS CNT FROM people
WHERE currentaction='Shopping' AND currentaction='Purchasing'
GROUP BY username WITH ROLLUP");

Nightfire
03-14-2010, 05:42 PM
$amount = mysql_num_rows($fquery);
echo $amount;

masterofollies
03-14-2010, 05:51 PM
Now it gives me an amount of 1. However there is at least 2 I saw "shopping".

DaiLaughing
03-14-2010, 05:57 PM
But were those shopping also purchasing? That's what the AND means.

masterofollies
03-14-2010, 07:02 PM
Nope, I am looking to count both. So if 3 are shopping and 2 are purchasing. I want it to display 5.

DaiLaughing
03-14-2010, 07:23 PM
You need an OR instead of the AND then.

masterofollies
03-14-2010, 07:47 PM
Still getting a 1. Here is my code.


$fquery = mysql_query("SELECT currentaction,COUNT(*) AS CNT FROM people WHERE currentaction='Shopping' OR currentaction='Purchasing'");
$amount = mysql_num_rows($fquery);

echo "Number of people shopping: $amount";

abduraooft
03-14-2010, 08:01 PM
$amount = mysql_num_rows($fquery);

echo "Number of people shopping: $amount";
That query would return only one row, containing a value as the number of rows matched. So, you need to fetch (http://php.net/mysql_fetch_assoc) the value from the mysql result.

masterofollies
03-14-2010, 11:15 PM
I don't quite understand?

MattF
03-15-2010, 12:03 AM
I don't quite understand?

In your original query, num_rows would return the number of rows matched. In that second query using COUNT(), only one result is returned, and that results value contains the number of rows matched, so you need to fetch the value of that second query to find out what the count is.

masterofollies
03-15-2010, 12:16 AM
So like this?


$fquery = mysql_query("SELECT currentaction,COUNT(*) AS CNT FROM people WHERE currentaction='Shopping' OR currentaction='Purchasing'");
$test = mysql_fetch_array($fquery);
$amount = mysql_num_rows($test);

echo "Number of people shopping: $amount";

abduraooft
03-15-2010, 07:31 AM
Nop!

$test = mysql_fetch_array($fquery);
echo $test['CNT'];

koko5
03-15-2010, 07:46 AM
$fquery = mysql_query("SELECT currentaction,COUNT(*) AS CNT FROM people WHERE currentaction='Shopping' OR currentaction='Purchasing'");
$test = mysql_fetch_array($fquery);
$amount = mysql_num_rows($test);

echo "Number of people shopping: $amount";
This query with no group by clause is not right. However, MySQL accepted it and applied group by internally, so result can be 1 or 2 ("Shopping" and "Purchasing" if any).
As I wrote in my previous post, to find "Number of people shopping" group by must be performed on username/userid + with rollup or you can do simply:


echo mysql_result(mysql_query("SELECT COUNT(DISTINCT userid) AS CNT FROM people
WHERE currentaction IN('Shopping','Purchasing')"),0);

Regards

masterofollies
03-15-2010, 03:02 PM
Got it, works. The echo $test['CNT'] one. Thanks



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum