homepage Welcome to WebmasterWorld Guest from 54.205.197.66
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Selecting only certain items in MYSQL by first letter
gregtheorangeman




msg:3217603
 7:17 pm on Jan 12, 2007 (gmt 0)

Hi, I am making a review site for a friend, its a movie review website, im using php and mysql (phpmyadmin) and I ran into a problem. I am able to submit, edit, and display reviews, but I have a menu system i want to work but cant figure out how to make it work. The menu system is composed of the letters A-Z and then # for numbers. Each letter is a different button linking to all the reviews starting with that letter. I need to be able to have the user click the letter of choice and then have php read the mysql database and only output tho's movies that start with that first letter or number. I cant find an answer Ive searched and searched and have only found full word matchings. Please Help me, Ive been trying to finish this website and am stuck....

 

jatar_k




msg:3217613
 7:26 pm on Jan 12, 2007 (gmt 0)

Welcome to WebmasterWorld gregtheorangeman,

what you need is LIKE

select columnname from table where columnname LIKE 'A%';

you use like instead of = to use a match. The % can represent any number of characters. You can also use * which represents a single char

[dev.mysql.com...]

gregtheorangeman




msg:3217617
 7:28 pm on Jan 12, 2007 (gmt 0)

Thanks for the fast response
Ill try it out!

gregtheorangeman




msg:3220363
 11:18 pm on Jan 15, 2007 (gmt 0)

K, it worked but now im running into the problem where its bringing in the movies that also start with "the" or "a", how would i have it exclue the letter "a" and word "the" when running that select statement?

scriptmasterdel




msg:3220416
 12:04 am on Jan 16, 2007 (gmt 0)

Hi GregTheOrangeMan,

I am not sure why the results are displaying as you have posted but in my opinion if you would like to collect the first letter if the word then why don't you use the substring function?

---------------------------------------------------------


select * from table where SUBSTRING(columnname, 1, 1) = 'a';

For your info: SUBSTRING(columnname, offset (starts from 1), length);

---------------------------------------------------------

This way you would always be comparing the first letter and not any other characters in the request string.

I hope this helps, good luck!

Del

LifeinAsia




msg:3220425
 12:15 am on Jan 16, 2007 (gmt 0)

how would i have it exclue the letter "a" and word "the" when running that select statement?

I don't know enough about mySQL, but I assume it has some sort of Replace function. In this case, since you're just looking at the first character, something like this should work:
SELECT *
FROM TableName
WHERE Replace(Replace(ColumnName,'The ',''),'A ','') LIKE 'a%'

You'll probably want to add another Replace to take care of titles starting with "An" as well.

A more efficient option would be to make a second column with A, An, and The already stripped out of the title and search on that column (instead of having to do the replaces every time you search).

[edited by: LifeinAsia at 12:15 am (utc) on Jan. 16, 2007]

gregtheorangeman




msg:3220539
 2:26 am on Jan 16, 2007 (gmt 0)

Im Sorry, I guess I wasnt descriptive enough, ill set up an example.

I have a database of movies a movie review website,

The Movies in the DB are:

Alien Versus Preditor
The Aviator
A City of Angels
Anger Management

My sql query is setup like this:
mysql_query("SELECT * FROM reviews WHERE Title LIKE '$a%'",$db);

It Outputs right.... Which would be this:

Alien versus Preditor
A City of Angels
Anger Management

It outputs everything thats starts with a, I want it to output every thing that starts with a, but if it has a single "a" or the word "the" before it, and the next word starts with "a", it will output that too. So in the example it would also output "The Aviator", but it wouldnt output "A City of Angels".

The Final output id be hoping for would be:
Alien Versus Preditor
The Aviator
Anger Management

Thanks,
Greg

phranque




msg:3220793
 9:36 am on Jan 16, 2007 (gmt 0)

gregtheorangeman:
i'm not sure what LifeinAsia did in his edit but it currently looks like he has the correct solution to your question...

mcibor




msg:3220810
 10:12 am on Jan 16, 2007 (gmt 0)

If you check other similar sites, they are dealing with that problem by putting the/a/an at the end of the string:

Alien Versus Preditor
Aviator, The
City of Angels, A
Anger Management

Then search will perform fine, and in the display you can do:

$explode = explode(", ", $title);
if(in_array($explode[1], array("A", "An", "The"))) $title = $explode[1]." ".$explode[0];

However it won't find directly input A City of Angels

Hope this helps you in some way.
Michal

LifeinAsia




msg:3221147
 4:41 pm on Jan 16, 2007 (gmt 0)

i'm not sure what LifeinAsia did in his edit

I just added the afterthought of the 2nd column. Doing the multiple replacing on every single search just seems very inefficient if you can avoid it.

coopster




msg:3221247
 6:01 pm on Jan 16, 2007 (gmt 0)

A related thread on Definite and indefinite articles --the, a, an in beginning of titles [webmasterworld.com] that may be of interest.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved