[Resolved] counting normalized rows?

[pheonix]

kinda in a jam! sorta a newbie.

So i have 2 tables. The 1st table contains 2 columns (id & states ... states contains all 50 US states). The 2nd table has like 5 columns and is a list of location and misc info. 1 of these columns in the 2nd table contains the id of a state from the 1st table. Basically i want to have a repeating region that displays all 50 states and next to the state name displays a count of the location in that state.

The result would be:
CA (12)
HI (2)
FL (15)
...

PS. i have already joined the tables that wasnt a problem.

Thanks,
Daniel

Connection Script:

<?php
mysql_select_db($database_test, $test);
$query = "SELECT LOCATIONNAME, STATENAME FROM LOCATIONS, STATELIST WHERE LOCATIONS.STATE_ID = STATELIST. STATE_ID";
$result = mysql_query($query, $test) or die(mysql_error());
$row = mysql_fetch_assoc($result);
?>

[pheonix]

IM me @ "atticus1214" if you are interested in the answer. im sure it isnt the best answer but it works

[tomhath]

There's a lot more to SQL than full inner joins. What you need is something like this:

SELECT STATELIST.STATENAME, count(*)
FROM STATELIST
LEFT JOIN LOCATIONS 
ON LOCATIONS.STATE_ID = STATELIST.STATE_ID
WHERE 1=1
GROUP BY STATELIST.STATENAME

[pheonix]

<?php mysql_select_db($database_test, $test);
$query = "SELECT STATE, STATENAME, count(STATE) AS number_of_occurances FROM LOCATIONS, STATELIST WHERE LOCATIONS.STATE = STATELIST.STATE_ID GROUP BY STATE ORDER BY STATE ASC";
$result = mysql_query($query, $test) or die(mysql_error());
$row = mysql_fetch_assoc($result);
?>

SOME HTML GOES HERE ...

<?php
do {
echo $row['STATENAME']  . " (" . $row['number_of_occurances'] . ")<br>";
 } while ($row = mysql_fetch_assoc($result));
?>