Count rows from results of a "mysql_query"

Question

If I have this:

$results = mysql_query("SELECT * FROM table_name WHERE 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 :)

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

What's wrong with the code you asked about here? That should achieve your desired affect.

Answer 5

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.

Answer 6

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.

Answer 7

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);

Answer 8

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.

Answer 9

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!