Quick question, which I think has a very easy solution for someone who has anything above the most rudimentary knowledge of PHP/MySQL as I do.

I have a list of cities in a variety of states stored in a database with city, state and some other variables. Right now they get pulled as a list sorted by city name:

  • Anchorage, AK
  • Baltimore, MD
  • Chicago, IL etc etc.

I want to be able to group by state first, then list all the cities that have that state value. So it'd look like:

AK

  • Anchorage
  • Juneau

CA

  • Los Angeles
  • San Diego
  • San Francisco
  • etc etc

I know I need to do some sort of foreach and have searched online, but haven't found an example that I can get to work.

Here's what I have to pull the basic list:

  $list = mysql_query("SELECT id, alphaname, state FROM regional ORDER BY alphaname",$db);

while ($thearray = mysql_fetch_array($list)) {
  echo "<li><a href='info.html?id=$thearray[id]'>$thearray[alphaname], $thearray[state]</a></li>";
  } 

The only real way I know how to do it would be to run a query for each state which would be a pain and totally stupid...

Thanks for any help!

Update - solved. I went with rockacola's approach though i-g's worked as well.

Accepted Answer

Try this..

Query all city with state, order by state first then by city:

SELECT id, alphaname, state 
FROM regional 
ORDER BY state ASC, alphaname ASC

Organise your dataset into 2 dimension array:

$states = array();
while($thearray = mysql_fetch_array($list)) 
{
    $states[$thearray[state]][$thearray[id]] = $thearray[alphaname];
} 

Now contents of your $states should look something like:

Array
(
    [AK] => Array (
        [id_1] = Anchorage
        [id_2] = Juneau
    )
    [CA] => Array (
        [id_3] = Los Angeles
        [id_4] = San Diego
        [id_5] = San Francisco
    )
)

Generate your HTML presentation:

NOTE: Add anchor to reflect proposed question.

foreach($states as $state_name => $cities)
{
    echo '<h3>'.$state_name.'</h3>';
    echo '<ul>';
    foreach($cities as $id => $city_name)
    {
        echo '<li><a href="info.html?id='.$id.'">'.$city_name.'</a></li>';
    }
    echo '</ul>';
}
Written by rockacola
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki