PHP and MySQL alphabetical listing queries

[Arthur]

So, you want all the unique first letters. How about this;

SELECT DISTINCT LEFT(title,1) FROM table ORDER BY title;

That should get what you want, with only one query. You'll have to sweep anything not in A..Z into one category (#) though.

Arthur
[This message has been edited by arthur (edited 04-28-01@1:11 pm)]

[dank]

I'm thinking you might want to do what Arthur said, but combined with a "GROUP BY" or a "COUNT(*) AS num" (deciding which one and in what combination would require some degree of thinking, which is beyond my current motivation and abilities) to provide a list of those letters with corresponding entries, which could then be built into links.

Dan
[This message has been edited by dank (edited 04-28-01@1:13 pm)]

[Moonlight]

Thanks for the suggestion, I did not think of that method.

I will try to do a "GROUP BY" and "COUNT" method which I think is the easiest way to do it.

For the "Other", I could always do a full COUNT(*), then put in a variable the count for each letter, then do a "$NbTitles - $NbTitlesFound" for the Other Category.

Moonlight

[Moonlight]

I would like to display an alphabetical listing by titles in alphabetical order.

What I would like to have is let say first display a starting letter menu, ie :

a | b | c | d | e ... | z | #[nbsp][nbsp](where # = Other)

It would check in the database if there are any titles starting by "a", if so activate the link, if not, don't show a link but shows the letter.

To be able to do the above, would I have to do 27 queries, and each one has : LIKE "a%"?

Is there any better way to do the above?

When the user would select a letter, it would display all the titles by alphabetical order (but that's easy to do).

The question is only related to the menu.

Thanks,

Moonlight