TalkPHP - View Single Post

Village Idiot · 08-15-2009, 06:58 PM

A query like this should work:

Code:

SELECT 
    name,
    substring(name,1) as first_letter
FROM
    table
WHERE
    [conditions]
ORDER BY 
    name

Sample result (each space separates columns)

Code:

Apple A
Apricot A
Bannana B
Guava G

This will give you each name in alphabetical order and the letter it starts with (so save you some data processing on the script end). There are countless ways to work with this, I'll let you figure which one you want out. But here is one way to sort it into an array (my PHP is very rusty and this is not tested, so look at it as more of a concept):

PHP Code:


			
<?
foreach($array_results as $x)
{
    $results[$x["first_letter"][] = $x["name"];

}
?>

Dont run 26 queries for each letter of the alphabet, it will be much much faster to grab all the data in an orderly fashion then work with it once you have it.

08-15-2009, 06:58 PM	#7 (permalink)
Village Idiot Wizard Join Date: Sep 2007 Posts: 1,299 Thanks: 17	A query like this should work: Code: SELECT name, substring(name,1) as first_letter FROM table WHERE [conditions] ORDER BY name Sample result (each space separates columns) Code: Apple A Apricot A Bannana B Guava G This will give you each name in alphabetical order and the letter it starts with (so save you some data processing on the script end). There are countless ways to work with this, I'll let you figure which one you want out. But here is one way to sort it into an array (my PHP is very rusty and this is not tested, so look at it as more of a concept): PHP Code: `<? foreach($array_results as $x) { $results[$x["first_letter"][] = $x["name"]; } ?>` Dont run 26 queries for each letter of the alphabet, it will be much much faster to grab all the data in an orderly fashion then work with it once you have it. __________________