Sql Query Help

[headmine]

I have a database with 4 fields.

 

Primary Key, Userid, Value1, Value2

 

Userid holds the User Id for the site.

 

Value one is basically anything. It could be state, zip, phone etc.

 

Value2 relates to Value1.

 

I would like to make an sql query that would echo each UNIQUE Value2 and display a Count next to it which represents how many times it's duplicated.

 

Example:

CA(200)

NY(300)

TX(250)

 

etc.

 

Here's what I have so far.

 

SELECT COUNT(DISTINCT value2) FROM mydb WHERE value1 = 'state' 

 

This returns 43

 

Which is probably correct because there are only 50 states in the U.S.

 

Do I need a foreach loop?

 

Please help.

 

Thanks in Advance!

[headmine]

OK! Breakthru!

 

I was able to do this within the SQL database.

 

SELECT value2, COUNT(value1) As total FROM mydb WHERE value1 = 'state' GROUP BY value2

 

When I do this in SQL it works beautifully! It breaks down the states and how many records there are for each.

 

When I do this in php. I only get the first Value! =(

 

How do I get it to display all the values?

 

THANKS!

[headmine]

OK. I've gotten it this far.

 

SELECT value1, COUNT(value2) FROM mydb WHERE value1 = 'state' GROUP BY value1"

foreach ($v as $vn) {
echo $vn->value1 . '<br />';
}

 

This prints out each state listed . How do I get the numbers next to it? Any help would be appreciated!

[headmine]

Ok. I got it. So here it is for anyone else that might run into this problem

 

 

$sql = "SELECT value2, COUNT(value2) as total FROM mydb WHERE value1 = 'state' GROUP BY value2"

foreach ($sql as $state) {
echo $sql->meta_value . ' (' . $sql->total .')<br />';
}

 

This would echo:

 

state (count)

 

CA (3323)

NV (93)

etc.