Display results for each letter

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
corvenus
Forum Newbie
Posts: 4
Joined: Thu Sep 23, 2004 1:31 pm

Display results for each letter

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
corvenus
Forum Newbie
Posts: 4
Joined: Thu Sep 23, 2004 1:31 pm

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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]
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
corvenus
Forum Newbie
Posts: 4
Joined: Thu Sep 23, 2004 1:31 pm

Post by corvenus »

Thanks for all the tips, it works perfectly now!
corvenus
Forum Newbie
Posts: 4
Joined: Thu Sep 23, 2004 1:31 pm

Post 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
Post Reply