Welcome to WebmasterWorld Guest from 54.198.93.179

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Selecting only certain items in MYSQL by first letter

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

5+ Year Member



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....
7:26 pm on Jan 12, 2007 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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...]

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

5+ Year Member



Thanks for the fast response
Ill try it out!
11:18 pm on Jan 15, 2007 (gmt 0)

5+ Year Member



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?
12:04 am on Jan 16, 2007 (gmt 0)

5+ Year Member



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

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

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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]

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

5+ Year Member



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

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

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



gregtheorangeman:
i'm not sure what LifeinAsia did in his edit but it currently looks like he has the correct solution to your question...
10:12 am on Jan 16, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

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

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.
6:01 pm on Jan 16, 2007 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month