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.