Page 1 of 1

Display results for each letter

Posted: Thu Sep 23, 2004 1:49 pm
by corvenus
Hi everyone,

I am trying to retrieve data from a MySQL db, and then, for each letter of the alphabet, display the results, in a manner such as:

A: list of articles starting with letter A

B: list of articles starting with letter B

etc...

Right now, I'm doing it with MySQL queries like:

"SELECT col FROM table WHERE col LIKE 'A%'"
"SELECT col FROM table WHERE col LIKE 'B%'"

So I have 26 separate queries, which I don't find very effective. I was wondering if there is a more effective way of doing this, by reusing the same array which would contain all the results (i.e. not just for 1 letter), and looping through it to display results by letter.

Thanks for any input!

Posted: Thu Sep 23, 2004 1:56 pm
by feyd
I don't remember what this exactly produced in situations like yours, but try this

Code: Select all

SELECT field FROM table ORDER BY field ASC

Posted: Thu Sep 23, 2004 2:17 pm
by timvw
If you want all the columns:

Code: Select all

SELECT col
FROM table
ORDER BY SUBSTRING(col, 1, 1) ASC
If you only want the first for each letter

Code: Select all

SELECT MIN(col)
FROM table
GROUP BY SUBSTRING(col, 1, 1)
ORDER BY col ASC

Posted: Thu Sep 23, 2004 2:23 pm
by corvenus
Thanks for the advice, but I already have a query like that. With this query I have an array of all results sorted alphabetically. Now, what I'm looking for more specifically is what to do to display the results from such a query in the manner described earlier, i.e. grouped by first letter. I'm guessing it would do something like:

Code: Select all

while ($data = mysql_fetch_array($req))
{
   get first letter from article
   if  (first letter from this article = first letter from previous article)
   {
       echo "," . $article
    }
   else
  {
       echo "first letter from this article<br>"
       echo $article
    }
    
}
Damn, now that I think if it this way, I think it would actually work and would be easy to do!

Posted: Thu Sep 23, 2004 2:29 pm
by feyd
corvenus, just a note for your future reference:
Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url]

Posted: Fri Sep 24, 2004 10:43 am
by pickle

Posted: Tue Sep 28, 2004 8:04 am
by corvenus
Thanks for all the tips, it works perfectly now!

Posted: Tue Sep 28, 2004 8:05 am
by corvenus
feyd wrote:corvenus, just a note for your future reference:
Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/quote]

duly noted