MySQL SELECT "LIKE" parameter not working properly

Question

I am building a search function in my "search.php" for a database in which I am storing information on ip-addresses and various units. Each unit has a "category" column in the database. My search choices are either searching in a text field, and/or selecting a category in a drop-down list. When I choose a category in my drop-down list and not typing anything in the text field, I get the correct results (returning all rows containing the selected category).

But when I type anything in the text field, I don't get any results.

Query when I ONLY choose category(in this case "IP-adress" is chosen as category):

  SELECT dataid,
       firstname,
       lastname,
       data.name dataname,
       description,
       ip,
       TYPE,
       TIMESTAMP
FROM DATA
INNER JOIN USER ON DATA.userid=USER.userid
WHERE TYPE='IP-adress'
  AND DATA.name LIKE '%%'
  AND description LIKE '%%'
  AND ip LIKE '%%'
  AND TYPE LIKE '%%'
  AND firstname LIKE '%%'
  AND lastname LIKE '%%'
ORDER BY TIMESTAMP DESC

This is returning all rows containing the selected category, as I want to.

Query when I type something in the textfield:

    SELECT dataid,
       firstname,
       lastname,
       data.name dataname,
       description,
       ip,
       TYPE,
       TIMESTAMP
FROM DATA
INNER JOIN USER ON DATA.userid=USER.userid
WHERE TYPE='IP-adress'
  AND DATA.name LIKE '%test%'
  AND description LIKE '%test%'
  AND ip LIKE '%test%'
  AND TYPE LIKE '%test%'
  AND firstname LIKE '%test%'
  AND lastname LIKE '%test%'
ORDER BY TIMESTAMP DESC

I have combinations where I have columns containing the word "test" just as my input, but I get no results.

I have been at it quite some time now and I am quite sure I had the opposite problem before, where I didn't get any results when I chose a category.

Anyway, I hope another set of eyes could help me out here. If you need more code, just ask for it!

Can you also provide a example record of the data which you would expect to return? — pazcal, Oct 29 '13 at 11:17
Are you sure you want only records where __all__ the columns contain `test` …? — CBroe, Oct 29 '13 at 11:20

score 1 · Accepted Answer · answered Oct 29 '13 at 11:24

This is because you have used AND for all the condition. It will search for test in all fiends and if one field does not have test then condition fails. Try this way

SELECT dataid,
       firstname,
       lastname,
       data.name dataname,
       description,
       ip,
       TYPE,
       TIMESTAMP
FROM DATA
INNER JOIN USER ON DATA.userid=USER.userid
WHERE TYPE='IP-adress'
  AND (DATA.name LIKE '%test%'
  OR description LIKE '%test%'
  OR ip LIKE '%test%'
  OR TYPE LIKE '%test%'
  OR firstname LIKE '%test%'
  OR lastname LIKE '%test%')
ORDER BY TIMESTAMP DESC

This worked! At first I just changed the lower "AND's" to "OR" which resulted in multiple results, but I didn't notice the "(" after `WHERE TYPE='IP-adress' AND` and the ")". Thanks a lot for the fast replies! — Anen, Oct 29 '13 at 11:54

score 0 · Answer 2 · answered Oct 29 '13 at 11:22

If I'm looking at the second query:

WHERE type='IP-adress'

and later on:

AND type LIKE '%test%'

That wouldn't give any result back as 'IP-adress' doesn't contain 'test' in it...

Also, the second query means actually that ALL the fields which are given in the where should contain at least the text 'test'. Are you sure that is correct?

MySQL SELECT "LIKE" parameter not working properly

2 Answers2