CODEWITHSUNDEEP
phpsqlmysql
user188962
user188962

Reputation:

Count rows from results of a "mysql_query"

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

Upvotes: 5

Views: 47499

Answers (9)

Eric
Eric

Reputation: 24910

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

Upvotes: 2

David
David

Reputation: 682

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.

Upvotes: 13

itavero
itavero

Reputation: 15

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!

Upvotes: 0

Jakob Stoeck
Jakob Stoeck

Reputation: 624

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

Upvotes: 4

Doug
Doug

Reputation: 6442

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.

Upvotes: 0

AndiDog
AndiDog

Reputation: 70158

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.

Upvotes: 0

Gal
Gal

Reputation: 23662

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.

Upvotes: 3

Alexander Gessler
Alexander Gessler

Reputation: 46607

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

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798676

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

Upvotes: -2

Related Questions