mysql query - use count or select?
I need to query a DB table to get a total number of instances of data stored.
The data is age, and I want to search for 5 ages
20, 25, 30, 35, 40
Obviously having 5 separate queries is poor coding.
With a SELECT where you DO want to retrieve data, having a loop is an option.
BUT
I don't actually need to retrieve any data other than the total number of instances found for EACH age.
I.E. there are 12 twenty year olds, 10 twenty five year olds etc
So I started looking at COUNT, but again I would have to use a loop.
I also started looking at multiple COUNT in the query, to avoid a loop, but how within the query (if even possible) would I identify the individual total number for each age?
Is it possible to use a multiple COUNT to get multiple counts for different coulmns within the same table?
And another thing, just out of interest
Does select without a fetch cache data?
take this as an example (syntax is missing I know)
<?php
$age = 20;
$query = mysql_query(SELECT * from users where age = $age);
?>
This hasnt actually done anything at all other than make a variable = some text (albeit a valid query function)
So then I do the following
<?php
$numrows = mysql_num_rows($query)
?>
It has now performed a query to the DB and it will return the number of rows found for members who are 20 years old.
That is fine and works. So I could put that in a loop and add 5 to $age each time and store $numrows value in an array
But as I don't actually need to retrieve data, is this the best way? As with that query I need to tell it to select something, like username or whatever, in this case it was *
So has it actually selected (i.e. cached somewhere) the full row data (*) where it matches the criteria (age = 20), or does it just count the number of rows it finds and stores (caches) the running total of that number to return it to the variable ($numrows) simply because I only performed a num_rows function?
Does it only cache the actual row data if specifically told to in for example mysql_fetch_array?
The reason I ask, is because using SELECT I have to tell it to actually select some data, currently everything (*) but I dont need to.
So is COUNT better/faster/more efficient when I don't actually want to retrieve data and only need to count the number of instances it finds, or is num_rows ok to use?
teammatt3 posted this at 16:35 — 29th April 2008.
He has: 2,102 posts
Joined: Sep 2003
For your first questions: Have you looked into a GROUP BY? This would probably work for you:
SELECT Age, COUNT(Age) AS AgeCnt FROM User WHERE Age IN(20, 25, 30, 35, 40) GROUP BY Age
greg posted this at 20:55 — 3rd May 2008.
He has: 1,581 posts
Joined: Nov 2005
Interesting, thanks.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.