PHP Mysql Help Needed!

[taylorwilsdon]

Any help would be very appreciated.

I would like to select a word from a mysql database, order them by the first letter of the word, and break it down by letter. For example, everything that starts with A under a subcategory "A".

Say I have words: apple, aardvark, billy, cat, couch

I want it to look like

A:
Aardvark
Apple

B:
Billy

C:
Cat
Couch

Thanks!

 

[stomer]

<?php

$query = "SELECT name FROM categories;";
$results = mysql_query($query);

$initial = "";
while ($row = mysql_fetch_assoc($result)) {
 if($initial != strtoupper($row['name'][0]) ) {
   $initial = strtoupper($row['name'][0]);
   echo "\n$initial: \n";
}
 echo $row['name'] . "\n";
}


?>

Something like that would do what you require.

 

[ChrisA]

$query = "SELECT name FROM categories;";
$results = mysql_query($query);

Something like that would do what you require.

I think you might want to sort the names. Add an "order by"

query = "SELECT name FROM categories ORDER BY name;";

With a little more effort you can have SQL extract the initial letter
but I'll leave details of that to you. It would go somthing like...

query = "SELECT 
             makeupercase(functiontogetfirstlettr(name)) as inital,
             name 
             FROM categories 
             ORDER BY name;";

Then you can remove some function calls from inside the while loop. This is really a matter of style. My preference is to always put as much of the functionality into the SQL query and make the other code as simple as possible.

 

[stomer]

My preference is to always put as much of the functionality into the SQL query and make the other code as simple as possible.

I've seen some devs takes this as far as getting the db to spit out html! Thus making code re-use a pain in the arse. Personally I'd get the db to retrieve the raw data, and leave the presentation to application that's retrieving the results.

 

[taylorwilsdon]

You guys are awesome! I've got a working code. Someone on another forum had a semi working version but this is great.