Selecting only certain items in MYSQL by first letter

[gregtheorangeman]

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]

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]

Thanks for the fast response
Ill try it out!

[gregtheorangeman]

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]

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]

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]

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]

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]

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]

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]

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