Display record and count

beefsqueeze

I have a mysql db that has approx. 97,000 records.

One of the fields is U.S. state name and city name. Each record has a state/city.
So California might have 20,000 records that match it.

I have tried every possible thing I can think of with the php/mysql bibles right in front of me.

example : domain.com/?state=California

I need it to show a list of cities and count how many records are in that city.

so it would return :
Los Angeles (1000)

Can anyone help me with this? I can get it to return all record for that state, but can't list them individually with a count.

LordShryku

$state = "California";

$sql = "SELECT city, 
               count(*) AS cnt
          FROM table
         WHERE state = '".$state."'
      GROUP BY city";

$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) > 0) {
   while($row=mysql_fetch_array($res)) {
      echo $row['city']." (".$row['cnt'].")<br />";
   }
}
else {
   echo "No rows found for ".$state;
}

beefsqueeze

Thanks - Will try it out.
How would I have it recognize any state, not just a specific state?

LordShryku

Using the method you used above
-> domain.com/?state=California

Considering this is on your index.php page, instead of specifying
$state = "California";
Use
$state = $_GET['state'];

beefsqueeze

I'm not really that dumb.

I just realized the answer to my question on the other states.

Thanks for the help. The code works perfectly.

I'm sure I'll be seeking help from you again.

Thanks again.

LordShryku

no prob bob 😃

beefsqueeze

OK -
Now I'm stuck again on a similar problem.

On one page I need to show a list of states and count of records.

I tried to modify the previous info, but it doesn't print out properly for this problem.

So basically I need to query the db and have it show a list of states with the count of records that match that state. (except this time - I'm not specifying anything - it will just list the states)

Then I can link to the next page with the list of cities we previously covered (and works great).

Thanks

LordShryku

$sql = "SELECT state,
               count(*) AS cnt
          FROM table
      GROUP BY state";