like, in, mysql

marian

i am trying to access user information from my database;

here is my select statement:

sql1 = select count(*) as counter, name as name from table1 where information like '%john%'

counter name
6 john

so the above will pull up any information that is pertaining to john.

now what i want to do is do this if i have multiple users like this:

sql2 = select count(*) as counter, name as name from table1 where information in ('john','matt','tom') group by name

so bascially what i want sql2 to do is, count how many times user's information appears in the database and populate like this:

counter name
6 john
5 matt
7 tom

the thing is the column information is a text field which basically holds a text of data about a user, so i will need to use the like statment in sql1, but how can i combine "like" statement with "in"

thanks for all your responses

eoghain

You can't, what you can do is write your query differently:

SELECT count(*) as counter,
       name as name
  FROM table1
 WHERE information LIKE '%john%'
    OR information LIKE '%matt%'
    OR information LIKE '%tom%'

That's basically what IN is doing anyway.

tomhath

If I read the original post correctly, the problem is more complicated that that.

First of all, sql1 is incorrect, there should be a GROUP BY clause:

sql1 = select count(*) as counter, name as name from table1 where information like '%john%'  group by name

So now what happens if you have rows like 'Dajohn' or 'johnson'? You won't get a count of all rows like '%john%', you'll get a row for each unique name.

The query could be written using CASE statements. It would probably be pretty strange looking though.