Sorting by first character (PHP & Mysql)

Mods: Since im not sure if this is done in MySQL or PHP, I just placed it in the general programming category. Feel free to move it If its wrong.

Hi there,

I have a mysql database containing alot of destinations. When I say alot, i mean thousands, so i can't list them all up on one single page, so I want to sort them alphabetically. On top of the page, I want a list from A to Z. When someone presses A, all destinations starting with A is printed. When someone press B, all destinations starting with B listes, and so on. What would be the best way to do this?

Please, feel free to give some dummy examples.

 

... WHERE SUBSTR(name, 1, 1) = 'A' ORDER BY name

something like that. the manual is a better place to look than my posting

 

This is how I do it.

if letter is A:

$letter = 'A';
WHERE `title` LIKE '$letter%'

or if it is not searchable use < > tags with

$next_letter = $letter++;

Peace,

 

That'll do it, or you can use LEFT. Add in LOWER for case insensitivity:

select * from `your_table` where LOWER(LEFT(`your_column`,1)) = 'a'

Code (markup):

 

If I'm not mistaken, MySQL searches case-insensitively by default anyway.

 

I wasn't certain. I know that ORDER BY is insensitive, but I wasn't certain about the main select clause. And being Saturday, I'm too lazy to look it up

 

Depends on the field. I believe if its defined as text varchar then its fine. If its a blob then its case sensitive. Makes sense really..

 

Yup, you're right about varchar. Just confirmed with a couple of queries.

So the code I posted can be simplified to

select * from `your_table` where LEFT(`your_column`,1)='a'

Code (markup):

 

// this will return all the names that starts with A
SELECT * FROM yourtable WHERE thecolumnname LIKE "A%"

// this will return all the names that starts with B
SELECT * FROM yourtable WHERE thecolumnname LIKE "B%"

PHP: