"how many search results from mysql" help

Question

Hi...

I got some help with gettin the number of rows returned from mysql, and it works fine... BUT, how do I get the number of rows with a certain field value? Do I have to make a new Mysql search query?

Here is the code where I query mysql and display in a table using fetch_array... Also, Im using mysql_num_rows to get number of rows. So how do I get number of rows with certain field value also?

$qry_result = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($qry_result);
while($row = mysql_fetch_array($qry_result))

Thanks for all help

OBSERVE: Im trying to avoid using another SELECT WHERE clause... Is there a way to do what I want withouth another search?

Answer 1

There are several ways to reach this, either run additional queries against MySQL or use programm logic to calculate what you need while iterating over the array.

Fetching the number of rows from MySQL is a task that has several solutions as well. You could blindly call SELECT count(*) FROM table WHERE foo = bar, or use the more advanced SQL_CALC_FOUND_ROWS variable of the database.

If you could explain yourself better, I would be glad to provide a good solution!

Answer 2

In your query, you can use the where clause. (select * from table where column1 = 'value')

Another option would be to have a counter variable that you increment in your while loop:

$counter = 0;
while($row = mysql_fetch_array($qry_result))
{
    if($row[0] == "value")
       $counter++;
}

After you have this counter, reset the result set using mysql_data_seek($qry_result, 0); and then continue with your original while loop.

Answer 3

OBSERVE: Im trying to avoid using another SELECT WHERE clause... Is there a way to do what I want withouth another search?

I'm curious why you don't want to use another SELECT WHERE clause?

From what I interpret of your question, you are asking to have the number of rows of a given query AND, a count of unique variables?

ex:

NAME  AGE
Joe   15
Simon 13
Simon 16
Joe   21
Mary  15
Joe   28

Your row count would be 6 and your count (that you are requesting) would be:

Joe x 3
Simon x 2
Mary x 1

If that is what you are asking, why not use 2 queries, 1 for your set of data, and another query where you GROUP BY 'name' and return only UNIQUE 'name' results? That would get you a count of your "certain fields".

Then again correct me if I miunderstood your question.