SQL return ORDER BY result as an array
- by EarthMind
Is it possible to return groups as an associative array? I'd like to know if a pure SQL solution is possible. Note that I release that I could be making things more complex unnecessarily but this is mainly to give me an idea of the power of SQL.
My problem: I have a list of words in the database that should be sorted alphabetically and grouped into separate groups according to the first letter of the word.
For example:
ape
broom
coconut
banana
apple
should be returned as
array(
'a' => 'ape', 'apple',
'b' => 'banana', 'broom',
'c' => 'coconut'
)
so I can easily created sorted lists by first letter (i.e. clicking "A" only shows words starting with a, "B" with b, etc. This should make it easier for me to load everything in one query and make the sorted list JavaScript based, i.e. without having to reload the page (or use AJAX).
Side notes: I'm using PostgreSQL but a solution for MySQL would be fine too so I can try to port it to PostgreSQL. Scripting language is PHP.
