hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : PHP/MySQL Alphabetical List with Headings
Reply

Forum Jump

PHP/MySQL Alphabetical List with Headings

Reply Post New Thread In Programming Discussion Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 08-24-2008, 08:24 PM
The Universes The Universes is offline
Web 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" 
Thanks!

Reply With Quote


Sponsored Links
  #2  
Old 08-25-2008, 07:02 PM
healthnut101 healthnut101 is offline
Newbie
 
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.

Reply With Quote
  #3  
Old 08-25-2008, 07:15 PM
The Universes The Universes is offline
Web 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.

Reply With Quote
Sponsored Links
  #4  
Old 08-25-2008, 07:17 PM
The Universes The Universes is offline
Web 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.

Reply With Quote
  #5  
Old 08-25-2008, 08:00 PM
etogre etogre is offline
Web Hosting Master
 
Join Date: Aug 2003
Location: California, USA
Posts: 581
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.
Reply With Quote
  #6  
Old 08-25-2008, 08:05 PM
healthnut101 healthnut101 is offline
Newbie
 
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

Reply With Quote
  #7  
Old 08-25-2008, 08:05 PM
m_abdelfattah m_abdelfattah is offline
Junior Guru Wannabe
 
Join Date: Nov 2005
Location: Egypt.
Posts: 75
Man, you should think about "Group By", here's a tutorial about it :
http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

Reply With Quote
  #8  
Old 08-25-2008, 11:36 PM
The Universes The Universes is offline
Web Hosting Master
 
Join Date: Jun 2008
Posts: 1,471
Quote:
Originally Posted by healthnut101 View Post
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
Thanks, works great. Just changed it to exclude numbers as well
Quote:
SELECT * from msuic WHERE ord(name) < 48 || ord(name) > 57 AND ord(name) < 65 || ord(name) > 90 AND ord(name) < 97 || ord(name) > 122

Reply With Quote
  #9  
Old 08-26-2008, 01:57 AM
bigfan bigfan is offline
WHT 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 />';

Getting first_char isn't really necessary, but it makes the PHP code a little simpler.


Last edited by bigfan; 08-26-2008 at 02:12 AM.
Reply With Quote
  #10  
Old 08-26-2008, 02:26 AM
bigfan bigfan is offline
WHT 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"


Reply With Quote
Reply

Related posts from TheWhir.com
Title Type Date Posted
Tesora's OpenStack DBaaS Supports MongoDB, Cassandra, Redis, and MySQL Web Hosting News 2014-05-23 14:53:11
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06
SingleHop Makes CRN Solution Provider 500 List Web Hosting News 2013-07-26 14:42:22
Web Hosts, IT Services Firms Make Inc 5000 Fastest Growing Companies Web Hosting News 2012-09-19 16:45:40


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?