Display results for each letter
Moderator: General Moderators
Display results for each letter
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!
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!
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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 ASCIf you want all the columns:
If you only want the first for each letter
Code: Select all
SELECT col
FROM table
ORDER BY SUBSTRING(col, 1, 1) ASCCode: Select all
SELECT MIN(col)
FROM table
GROUP BY SUBSTRING(col, 1, 1)
ORDER BY col ASCThanks 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:
Damn, now that I think if it this way, I think it would actually work and would be easy to do!
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
}
}- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
corvenus, just a note for your future reference:
Please use
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]feyd wrote:corvenus, just a note for your future reference:
Please useCode: 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