Results 1 to 10 of 10
-
08-24-2008, 08:24 PM #1Web Hosting Master
- Join Date
- Jun 2008
- Posts
- 1,471
PHP/MySQL Alphabetical List with Headings
I'm trying to do something like this, a database holds lets say the names of various music albums and I want to display them on a page alphabetically with headings for each letter of the alphabet.
A
-----
A Better Something
Alpha Something
Already Blah
B
-----
By the Way
Better Than Carp
(you get the idea)
The part I'm lost on is how to separate out stuff like @Name or !Test!, I would like to sort out all the stuff that doesn't start with a number or a letter of the alphabet. Not sure how I would do that with SQL. Any help would be appreciated.
Also, is the the best way of pulling out all entries starting with the letter A:
PHP Code:"SELECT name FROM albums WHERE name LIKE 'A%' ORDER BY name ASC"
-
08-25-2008, 07:02 PM #2Newbie
- Join Date
- Aug 2008
- Posts
- 10
can you explain what the @name and !test! is needed for, im kinda lost as to why those need to be in your database.
-
08-25-2008, 07:15 PM #3Web Hosting Master
- Join Date
- Jun 2008
- Posts
- 1,471
Those are examples of stuff that does not start with an alphanumberic (A-Z). I need to get everything that does not start with an alphanumeric out of the database and display it in a special heading.
-
08-25-2008, 07:17 PM #4Web Hosting Master
- Join Date
- Jun 2008
- Posts
- 1,471
Example:
#
------
!ROck!
%Album Name*
#30 Rock
See how all those dont start with an alphanumeric, how do I query all of those out of the database easily?
Thanks in advance.
-
08-25-2008, 08:00 PM #5Web Hosting Master
- Join Date
- Aug 2003
- Location
- California, USA
- Posts
- 582
PHP Code:<?php
$a_z = range('A','Z');
$query = "SELECT name FROM albums WHERE upper(name) ";
foreach ($a_z as $letter)
{
$query .= "NOT LIKE '". $letter . "%'";
if ($letter != 'Z')
$query .= ' AND upper(name) ';
}
$query .= " ORDER BY name ASC"
?>Last edited by etogre; 08-25-2008 at 08:05 PM.
-
08-25-2008, 08:05 PM #6Newbie
- Join Date
- Aug 2008
- Posts
- 10
This should also work for what you want
SELECT * from music WHERE ord(name) < 65 || ord(name) > 90 and ord(name) < 97 || ord(name)>122
-
08-25-2008, 08:05 PM #7Junior Guru Wannabe
- Join Date
- Nov 2005
- Location
- Egypt.
- Posts
- 76
Man, you should think about "Group By", here's a tutorial about it :
http://www.tizag.com/mysqlTutorial/mysqlgroupby.php
-
08-25-2008, 11:36 PM #8Web Hosting Master
- Join Date
- Jun 2008
- Posts
- 1,471
-
08-26-2008, 01:57 AM #9WHT Addict
- Join Date
- Nov 2005
- Posts
- 123
Here's another version (to include A-Z/a-z/0-9), with some test code:
PHP Code:$qry = "SELECT name, LEFT(name, 1) AS first_char FROM albums
WHERE UPPER(name) BETWEEN 'A' AND 'Z'
OR name BETWEEN '0' AND '9' ORDER BY name";
$result = mysql_query($qry);
$current_char = '';
while ($row = mysql_fetch_assoc($result)) {
if ($row['first_char'] != $current_char) {
$current_char = $row['first_char'];
echo '<br />' . strtoupper($current_char) . '<br />-----<br />';
}
echo $row['name'] . '<br />';
}
Last edited by bigfan; 08-26-2008 at 02:12 AM.
-
08-26-2008, 02:26 AM #10WHT Addict
- Join Date
- Nov 2005
- Posts
- 123
Sorry, I screwed that up when I tried to edit it.
PHP Code:$qry = "SELECT name, LEFT(name, 1) AS first_char FROM albums
WHERE UPPER(LEFT(name, 1)) BETWEEN 'A' AND 'Z'
OR LEFT(name, 1) BETWEEN '0' AND '9' ORDER BY name";