Count rows from results of a "mysql_query"

If I have this:

$results = mysql_query("SELECT * FROM table_name WHE开发者_如何学编程RE id=$id");

is there then any way to check how many rows which have a field-value of "Private" or "Company" ?

I need to show the user how many "Private" and "Company" records where found, without making another query. (There is a column called 'ad_type' which contains either "private" or "company")

I already know the mysql_num_rows for counting all rows!

EDIT: There are 500thousand records! So maybe an iteration through the result is slow, what do you think?

Thanks for all help :)

The above answers are great and all, but the currently checked answer will work very inefficiently should you be dealing with a large amount of data

Example of the above answer (via Gal)

$results = mysql_query("SELECT *,(SELECT COUNT(*) FROM table_name WHERE column=$value) count FROM table_name WHERE id=$id");

It's good and all, and it returns what you need but the obvious design flaw is that making your SQL server return the results then re-return them and look at just the count is very inefficient for large amounts of data.

Simply do this:

$results = mysql_query("SELECT * FROM table_name WHERE column=$value");
$num_rows = mysql_num_rows($result);

It will yield the same results and be much more efficient in the long run, additionally for larger amounts of data.

You can do something like:

$results = mysql_query("SELECT *,(SELECT COUNT(*) FROM table_name WHERE column=$value) count FROM table_name WHERE id=$id");

in order to fetch the number with sql.

If you don't want to change your query you could do a

$results = mysql_query("SELECT * FROM table_name WHERE id=$id");
$count = mysql_num_rows($results);

steps to get a count():

1. use mysql_query() to get count,
2. use mysql_fetch_array() to get the only 1 row
3. get the only one column of the row, this is the count,

here is an example, which check whether the email is already used:

// check whether email used
$check_email_sql = "select count(*) from users where email='$email'";
$row = mysql_fetch_array(mysql_query($check_email_sql));
$email_count = $row[0];

Iterate through the result set of rows and count the number of occurences of Private and Company in ad_type, respectively?

You can do

SELECT COUNT(*) FROM table_name WHERE id=$id GROUP BY fieldvalue HAVING fieldvalue = "Private"
SELECT COUNT(*) FROM table_name WHERE id=$id GROUP BY fieldvalue HAVING fieldvalue = "Company"

but that would be another query. But if you process the data anyway, you could simply sum up the number of "Private" and "Company" rows after doing the query.

In the case you don't have to get all results, use this.

SELECT ad_type, COUNT(*)
FROM table_name
WHERE (id=$id)
GROUP BY ad_type
HAVING ((ad_type = 'Private') OR (ad_type = 'Company'))

If you still have to fetch all the records where id = $id, it won't work. But executing such a query (once) before fetching the real data should be more efficient than using a subquery.

I guess this query would do the job:

SELECT ad_type, count(*) FROM table_name WHERE id=$id GROUP BY ad_type; 

I don't see any reason so far to use HAVING, since you probably want to show the user an overview of all the ad_type's found in DB (at least you didn't mention that there are other values for ad_type then the two given). I also strongly suggest NOT to use sub-queries; always try to use just one. If there's one thing that will slow your query down, it's a subquery (or subqueries).

Good luck!

Iterate through the results of the query and keep a count of how many of each show up in local variables.