How to: Pull out values for a field as well as a count() with PHP

[jmreinwald]

Hi folsk,

I'm relatively new to the world of PHP/MySQL. For a simple real-world scenario, I have a pretty simple db created to track some hockey trading cards. For simplicity, I have it set up as follows:
set_name (year and set name)
player_name
number
quantity
box (this is the box I have it stored in)

Ultimately what I want to do is pull a list of DISTINCT Set names and a total Quantity for all cards from a particular Location. In the client, the select statement I have for testing is:

select distinct set_name,count(quantity) from tblmaster where box = 'a' GROUP BY set_name;

The output looks fine, but I don't know how to grab the total quantity value for PHP. I know the set_name is accessible through

while ($row = mysql_fetch_array($result)) {
	$set_name = $row['set_name'];
...}

but how do I get the total count for any set?

 

[azzazzello]

give your count(quantity) a name, and refer to it by that name, like this

select distinct set_name,count(quantity) as mytotal from tblmaster where box = 'a' GROUP BY set_name;

while ($row = mysql_fetch_array($result)) {
$total = $row['mytotal'];
...}

 

[r937]

DISTINCT is redundant in this situation

GROUP BY produces distinct groups, because, well, because that's how GROUP BY works



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)