PHP/MySQL Alphabetical Grouping

[Geat]

I'm creating a glossary page that has the letters of the alphabet listed across the top, each being a bookmark that takes you to the relevant letter section on the page. If there are no entries for a letter in the DB, I don't want the letter to be a link.

At the moment, i do 26 SELECT LIKE statements (e.g. 'a%', 'b%', etc.) - and it works, but it's grossly ineffcient.

Can I do this in a single MySQL statement and then do the processing in PHP? I'm assuming there is some kind of GROUP BY syntax I can use?

[Nevermore]

You can order things when you get them with the mysql query by adding
ORDER BY fieldname
to the query.

[Geat]

I know that thanks, but is there a way of efficiently processing those results, rather than iterating through the array and doing string compares and stuff?

[DaiWelsh]

Geat,

If you mean just finding the count per letter, then something like (untested)

SELECT substring(columnname,0,1) AS FirstLetter,count(*) As NumItems FROM table GROUP BY FirstLetter ORDER BY FirstLetter

should work, thogh you may need to repeat the substring in the group by if it wont recognise the alias (I think it will but...)

HTH,

Dai

(resident of Cardiff age 4-18 :) )

[Geat]

Tidy bud, I'll give that a go!

It was so close! Just the substring call should have been (x, 1, 1) rather than (x, 0, 1). Yup, that's the totally inconsitent world of programming for you!

I'm the opposite of you, I've been living here since I was 18...

[Nevermore]

Maybe you two are actually one person...

[DaiWelsh]

:D

What, in a Fight Club/Angel Heart kind of way? :D

No I tried growing a beard but it never looked that good so that cant be me :p

[JennaMay]

I've managed to use this code successfully but I now need to take the value of FirstLetter and use it in my PHP code. I've tried searching on the web for tips but as I don't quite know what you call the FirstLetter 'variable' (a temp store?) it's making it a little difficult! Please can someone tell me how i would use this in PHP code or what I need to search on the web - I'm using dial up so it takes quite a while!

thanks
JM

PS Lived in Cardiff from age 19-22yrs!

[DaiWelsh]

Assuming you have something like

$rstLetters = mysql_query('SELECT substring(columnname,1,1) AS FirstLetter,count(*) As NumItems FROM table GROUP BY FirstLetter ORDER BY FirstLetter');
while($arrLetters = mysql_fetch_array($rstLetters)) {
.......

then the first letter is in $arrLetters['FirstLetter'] within the while loop and FirstLetter in this scenario is called an "alias".

HTH,

Dai

[JennaMay]

Thanks Dai.
However, I don't completely understand how to fit this in with what I want to do. (bit of a PHP newbie!)

I am trying to create an alphabetical search facility for a music catalogue. So far, I have a page which outputs the first letter of all the artists in the database as a hyperlink. I then want them to be able to click on the hyperlinked letter which will then display all artists which start with that letter. I am a bit stuck on how to create the SQL query to do this using the alias. I've attached the code I have written so far - pls can you take a little look and tell me where I am going wrong!

Thanks
JM

[DaiWelsh]

JM,

The main problem is that you are using

$query = "select DISTINCT artist
from MUSIC where substring(artist,1,1) = '$FirstLetter'";

but $FirstLetter is not set anywhere. if you add this line

$FirstLetter = $_GET['catid'];

before the query, this will ensure that the letter is picked up from the URL parameter (e.g. ?catid=A would yield $FirstLetter = 'A').

Note that for production code there are a number of issues to consider:

You should validate all user input (I haven't in my simple example)
You should check errors after all database queries
functions that you use in both pages should be included in from a common file
etc.


but I have just focused on what you need to get the code working.

Regards,

Dai

[JennaMay]

Woot! That works, thanks v much Dai!

Taken on board your comments, will look into this once main functionality is built. :-)

Thanks again.
JM

[DaiWelsh]

np, glad to help