CODEWITHSUNDEEP
mysqlselectsql-like
Anen
Anen

Reputation: 15

MySQL SELECT "LIKE" parameter not working properly

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!

Upvotes: 1

Views: 5995

Answers (2)

Devubha Manek
Devubha Manek

Reputation: 96

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

Upvotes: 1

pazcal
pazcal

Reputation: 938

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?

Upvotes: 0

Related Questions