Basic PHP MySQL array grouping question


Answers

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.

Question

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.




One step approach to grouping, then sorting, then echoing?

At some point where your dataset isn't too big, sorting with mysql should be faster if indexes are correctly added since default mysql indexes are b-tree. In your case, bringing the data and looping over it you will have a O(n) thus the sorting will take more as the data gets bigger.

I recommend you to take a look to your database schema, use explain to see how indexes are being used in the query you are running and benchmark. AGAIN: this will depend of your data.

now, for a sake of dreaming: Once your data is big enough to make the sorting too slow in mysql then you should switch something where you can sort in parallel using map-reduce or something like that, but given the nature of your post I think you are safe for now.





Tags