PHP, MYSQL, Getting a list of a-z into single listing.
Moderator: General Moderators
-
joncampbell
- Forum Newbie
- Posts: 24
- Joined: Fri Mar 11, 2005 12:57 pm
- Location: Irvine, California, USA
PHP, MYSQL, Getting a list of a-z into single listing.
I was wondering if there was a way to sort a list comprised of entries from a-z into lets say C, or E only... I know to use "SELECT * FROM <name> ORDER BY <title> ASC". I have a work around (making columns for every letter), but its not a good solution. Any suggestions people have would be very much appreciated.
-
joncampbell
- Forum Newbie
- Posts: 24
- Joined: Fri Mar 11, 2005 12:57 pm
- Location: Irvine, California, USA
-
joncampbell
- Forum Newbie
- Posts: 24
- Joined: Fri Mar 11, 2005 12:57 pm
- Location: Irvine, California, USA
Thank you for the sites, I will save these links. I don't see how this will do what I am trying to do. I have a column with all different names in it, and I am trying to list these in a nice alphabetical listing. I am trying to separate my listing to letter specific listings...
Like 1 page would be C, another page would be s, another L.
This seems to me like I would be trimming the names to a letter?
Like 1 page would be C, another page would be s, another L.
This seems to me like I would be trimming the names to a letter?
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
have you even looked at http://ca3.php.net/manual/en/function.substr.php ?
-
joncampbell
- Forum Newbie
- Posts: 24
- Joined: Fri Mar 11, 2005 12:57 pm
- Location: Irvine, California, USA
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
-
joncampbell
- Forum Newbie
- Posts: 24
- Joined: Fri Mar 11, 2005 12:57 pm
- Location: Irvine, California, USA
I hate to ask so many questions, and I am reading the things you guys are telling me to read, but I am not seeing how this will help me. Below I have attached a copy of the syntaxfor SUBSTRING(), I would much apriciate an example of what you guys are trying to tell me. I have also put the WHERE function in my mysql_query, and its giving syntax errors.
Code: Select all
"e;SELECT * FROM movie_reviews WHERE title = title ORDER BY title ASC"e;Code: Select all
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) , SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax.
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
This function is multi-byte safe.- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Im not a master of mysql but try..
$_GET['letter'] comes from http://www.domain.com/?letter=a
Code: Select all
"SELECT * FROM movie_reviews WHERE SUBSTRING(`title`,0,1) = '".$_GET['letter']."' ORDER BY title ASC"
Last edited by John Cartwright on Thu Mar 17, 2005 7:45 pm, edited 1 time in total.
-
joncampbell
- Forum Newbie
- Posts: 24
- Joined: Fri Mar 11, 2005 12:57 pm
- Location: Irvine, California, USA
I tried that, and again, it doesn't seem to be doing what I am trying to do. I give up though, I put an extra field in MYSQL, called listing, and changed the mysql_query to.
Also I am using a post and get to change which letter $listing is.
Thanks again for the help.
Code: Select all
SELECT * FROM movie_reviews WHERE `listing`='$listing' ORDER BY title ASC"e;Thanks again for the help.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
mysql has the first character as index 1 (not 0 like in php)
thus:
or
thus:
Code: Select all
select * from movie_reviews where substring('title', 1, 1) = 'a'Code: Select all
select * from movie_reviews where title like 'a%'- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact: