PHP, MYSQL, Getting a list of a-z into single listing.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

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.

Post by joncampbell »

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

Post by feyd »

Use a WHERE clause with a SUBSTRING() against the first letter of the thing you wish to match against.



Moved to Databases.
joncampbell
Forum Newbie
Posts: 24
Joined: Fri Mar 11, 2005 12:57 pm
Location: Irvine, California, USA

Post by joncampbell »

sorry about the miss-posting.

am having trouble finding the correct syntax for the SUBSTRING() clause, the closest thing I have come up with is GROUP. I am also having trouble finding the syntax for GROUP in a mysql_query command. Can you list a good site for referencing these clauses?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

joncampbell
Forum Newbie
Posts: 24
Joined: Fri Mar 11, 2005 12:57 pm
Location: Irvine, California, USA

Post by joncampbell »

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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

joncampbell
Forum Newbie
Posts: 24
Joined: Fri Mar 11, 2005 12:57 pm
Location: Irvine, California, USA

Post by joncampbell »

This would work, but I feel strange about removing the things I don't need, to leave the things I do. Why cant I just select the things I wan't... like SELECT a*, that would work great.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

oops, wrong substr() :roll:

search SUBSTRING() in mysql reference library as feyd suggested erlier
joncampbell
Forum Newbie
Posts: 24
Joined: Fri Mar 11, 2005 12:57 pm
Location: Irvine, California, USA

Post by joncampbell »

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

&quote;SELECT * FROM movie_reviews WHERE title = title ORDER BY title ASC&quote;

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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Im not a master of mysql but try..

Code: Select all

"SELECT * FROM movie_reviews WHERE SUBSTRING(`title`,0,1) = '".$_GET['letter']."' ORDER BY title ASC"
$_GET['letter'] comes from http://www.domain.com/?letter=a
Last edited by John Cartwright on Thu Mar 17, 2005 7:45 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

... WHERE SUBSTRING(`title`, 0, 1) = 'a' ...
I believe..


nice snipe Phenom.
joncampbell
Forum Newbie
Posts: 24
Joined: Fri Mar 11, 2005 12:57 pm
Location: Irvine, California, USA

Post by joncampbell »

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.

Code: Select all

SELECT * FROM movie_reviews WHERE `listing`='$listing' ORDER BY title ASC&quote;
Also I am using a post and get to change which letter $listing is.

Thanks again for the help.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I am trying to do the same thing and have been unsuccessful...
someone more experienced with mysql please shed some light on this please
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

mysql has the first character as index 1 (not 0 like in php)

thus:

Code: Select all

select * from movie_reviews where substring('title', 1, 1) = 'a'
or

Code: Select all

select * from movie_reviews where title like 'a%'
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

well done, well done :0
Post Reply