The main issue of grouping a PHP MySQL array

Question

A quick question, which I think has a very simple solution for those who have something above the most basic knowledge of PHP / MySQL, like me.

I have a list of cities in different states stored in a database with the city, state and some other variables. Right now they are being pulled like a list sorted by city name:

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

First, I want to be able to group by state, and then list all cities that have this state value. So it will look like this:

AK

• Anchorage
• Juneau

CA

• Los Angeles
• San diego
• San Francisco
• etc.

I know that I need to do some kind of foreach and search the Internet, but have not found an example that I can get to work.

:

  $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>";
  } 

, , , - , ...

!

- . rockacola, i-g .

Answer 1

.

, :

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

2- :

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

$states :

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

HTML:

. , .

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>';
}

Answer 2

SQL-:

ORDER BY state, alphaname

:

state   city
------------------
AK      Anchorage
AK      Juneau

... SELECT state, alphaname AS city .

PHP:

//Please excuse my hideous attempt at PHP.  Better to think of it as Psuedo code
while ($thearray = mysql_fetch_array($list)) {
  echo $thearray[state]
  echo "<li><a href='info.html?id=$thearray[id]'>$thearray[alphaname]</a></li>";
}

... i-g.

Answer 3

Sort all rows by state. Then move all the lines in order. In the first line and in any line that has a different state than the previous line, print the name of the state before displaying the name of the city.

Answer 4

SQL will not return more than two-dimensional data. You want some sort of summary column. The answer to your problem is to get all the lines with:

SELECT * FROM regional ORDER BY state;

Then sort and group using PHP (or your choice language), but not SQL.