Welcome to WebmasterWorld Guest from 54.159.50.111

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)

New User

5+ Year Member

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 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...]

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

New User

5+ Year Member

joined:Jan 12, 2007
posts:4
votes: 0


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

New User

5+ Year Member

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

Full Member

10+ Year Member

joined:Jan 17, 2006
posts:210
votes: 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

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

Moderator from US 

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

joined:Dec 10, 2005
posts:5550
votes: 24


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)

New User

5+ Year Member

joined:Jan 12, 2007
posts:4
votes: 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

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

Administrator

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

joined:Aug 10, 2004
posts:10542
votes: 8


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 26, 2003
posts:1133
votes: 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

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

Moderator from US 

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

joined:Dec 10, 2005
posts:5550
votes: 24


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)

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


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