SELECT WHERE LIKE Mysql Query

Using the following example table;

tablea

field1 - roger rabbit
field2 - cartoon

I want to extract all the rows from table a where field 1 contains the name roger. At the moment I have a query set up like this

SELECT * FROM tablea WHERE "roger" LIKE '%field2%'

but this returns nothing.

Only when I set field 1 to just "roger" do I get it to return any rows.

What am I doing wrong with my query?

 

Show us the column headings and one or two rows of sample data. You're confusing me with the way you posted it.

 

"SELECT * FROM tablea WHERE field2 LIKE '%roger%'"

PHP:

It looks like you just had your query backwards

 

But then is that not effectively saying

SELECT * FROM tablea WHERE "roger rabbit" LIKE '%roger%' which would give no results because "roger rabbit" doesnt exist within the LIKE variable.

I'll try and explain it more clearly for T0PS benefit


I have a table which has several fields;

e.g.

cartoon_characters
----------------------------------------------------
|name | age | description |
----------------------------------------------------
|roger rabbit | 12 | a silly rabbit |
----------------------------------------------------
|donald duck | 13 | a quacky duck |
-----------------------------------------------------

And basically I want all the cartoon characters that have the name "roger" somewhere in the table which is why I would like to use the LIKE option.

So if I have a query;

SELECT * FROM cartoon_characters WHERE "roger" LIKE '%name%'

what am I doing wrong with the query?

 

You should use it the way jestep suggested it. It should work then.

 

SELECT * FROM cartoon_characters WHERE "name" LIKE '%roger%'

 

I think I know what your saying and you can't do it the way you're trying. You'll need to split your where clause a bit like

SELECT 
     * 
FROM 
    cartoon_characters 
WHERE 
     "name" LIKE '%roger%' or "name" LIKE '%robert%'

Code (sql):

So in your PHP you'll build the where clause depending on how many words you need. You may also want to look at full text indexing for both performance and ease. I'm getting a strong sense of deja vu here

 

ok I'll give that a go and see if it works.

Thanks for your help guys

 

Yeah I love that full text index with MySQL v5

Unfortunately the server uses MySQL v4 and won't upgrade.


Thanks for the help

 

ant to serach in table with "y "then i want to retrieve all the record which contain y what i do which query pls tell me

 

Hi savani,
You want to retrieve all names that contain "y"
try this
select * from Table1 where name like '%y%'