PHP - build multilevel associative array from database (sorting cities by state from db)

Question

I'm somewhat new to php and have been rattling my brain for the past few hours trying to figure this out. I need to sort cities by state from a database. I used the following query to retrieve the data set:

SELECT state, city FROM table ORDER BY state ASC, city ASC  

This shows me the information I need when I run it as a query in phpMyAdmin, however I can't seem to figure out how to get it into an array and loop it.

The format I need to output is

CA
   Los Angeles
   San Francisco
New York
   Brooklyn
   Buffalo

I also need to be able to make each city return a link, but I'm pretty sure I can do that with ".item['$city']."

Any help is greatly appreciated!

Answer 1

Thanks Guys!

Ended up using the following code:

$query = ('SELECT DISTINCT state,city FROM table ORDER BY state ASC, city ASC');
$result = mysql_query($query);
$states = array();
while ($row = mysql_fetch_assoc($result)) {

    if (!isset($exists[$row['state']])) {

        echo "<div id='location'><div class='state'><p>" . $row['state'] . "</p>";

        $exists[$row['state']] = true;
    }
    echo "<div class='city'><p><a href='?city=".$row['city']."' target='_blank'>".$row['city']."</a></p></div>";
}