Page 1 of 2
PHP, MYSQL, Getting a list of a-z into single listing.
Posted: Thu Mar 17, 2005 6:12 pm
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.
Posted: Thu Mar 17, 2005 6:23 pm
by feyd
Use a WHERE clause with a SUBSTRING() against the first letter of the thing you wish to match against.
Moved to Databases.
Posted: Thu Mar 17, 2005 6:43 pm
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?
Posted: Thu Mar 17, 2005 6:45 pm
by feyd
Posted: Thu Mar 17, 2005 6:59 pm
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?
Posted: Thu Mar 17, 2005 7:08 pm
by John Cartwright
Posted: Thu Mar 17, 2005 7:22 pm
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.
Posted: Thu Mar 17, 2005 7:28 pm
by John Cartwright
oops, wrong substr()
search SUBSTRING() in mysql reference library as feyd suggested erlier
Posted: Thu Mar 17, 2005 7:39 pm
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
"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.
Posted: Thu Mar 17, 2005 7:44 pm
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
Posted: Thu Mar 17, 2005 7:44 pm
by feyd
Code: Select all
... WHERE SUBSTRING(`title`, 0, 1) = 'a' ...
I believe..
nice snipe Phenom.
Posted: Thu Mar 17, 2005 8:22 pm
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"e;
Also I am using a post and get to change which letter $listing is.
Thanks again for the help.
Posted: Sun Mar 20, 2005 3:20 pm
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
Posted: Sun Mar 20, 2005 4:10 pm
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%'
Posted: Sun Mar 20, 2005 4:21 pm
by John Cartwright
well done, well done :0