4

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.

Improve this question

4 Answers 4

Reset to default
4

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>';
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I like this approach better than i-g's because it separates out the presentation from the data retrieval & munging better, but I don't think $tharray[id] is going to have anything in it because your select statement only asks for the name and the state. Still you could just use $states[$thearray[state]][] = $thearray[alphaname]; and leave the IDs out of it. (In terms of output, one could make a case for using a list of lists as i-g has done.)
@sprugman, thanks for the correction. I should SELECT id from the query, as ID usage is part of @Voodoo's requirement. and Yes, $states[$thearray[state]][] would also work to leave IDs out of it.
@sprugmanI was using ID as a variable to create links so I did include it.
1

Update your SQL query to use:

ORDER BY state, alphaname

This will make your database return results as:

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

...assuming using SELECT state, alphaname AS city for the purposes of an example.

The presentation has to be handled in 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>";
}

...or you could look at i-g's post.

Improve this answer

1 Comment

yep, i would recommend this approach too.
0

Sort all rows by state. Then, traverse all rows in order. On the first row, and on any row that has a different state than the previous row, output the state name before you output the city name.

Improve this answer

Comments

-2

SQL won't return more than 2 dimensional data. What you want is to get some kind of pivot column. The answer to your problem is to fetch all rows with:

SELECT * FROM regional ORDER BY state;

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

Improve this answer

1 Comment

Nothing to do with pivoting data, and your ORDER BY is identical to what the OP provided.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.