Multiple LIKE comparisons in MySQL query? - MySQL, PHP

Question

I have a search form field that I want to compare with multiple columns within a table e.g.:

+----------+-------+---------+------+------------+
| name     | owner | species | sex  | birth      |
+----------+-------+---------+------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 |
| Bowser   | Diane | dog     | m    | 1989-08-31 |
| Whistler | Gwen  | cat     | m    | 1997-12-09 |
+----------+-------+---------+------+------------+

The query I made has multiple LIKE comparisons however it only seems to find rows of data for the first two columns.

The first comparison is "name" then "owner" then "species" then "sex".

It only finds rows if you enter a string that matches the first two columns, however if you enter a string that matches one of the last columns it does not return anything.

Here is the query:

SELECT * FROM contact 
WHERE name LIKE \"%$search%\" 
OR owner LIKE \"%$search%\"
OR species LIKE \"%$search%\" 
OR sex LIKE \"%$search%\" 
GROUP BY name, owner,species, sex

My only guess is that MySQL perhaps limits how many "OR"s you can use?

My query should return a row from the table if the string entered by the user matches any column data.

Here is my PHP code for it

include("database.class.php");

    //search string assigned from POST field
    $search = $_POST['contact_search'];

    $db = new database();

    $db->select('quote_system');

    $result = $db->query("SELECT * FROM contact WHERE name LIKE \"%$search%\" OR owner LIKE \"%$search%\" OR species LIKE \"%$search%\" OR sex LIKE \"%$search%\" GROUP BY name, second_name, owner, species, sex");

    print_r($result);

    while ($row = $db->fetch_assoc($result))
    {
        echo $row['name'];
        echo $row['owner'];
        echo $row['species'];
        echo $row['sex'];
        echo "<br/>";

    }

Incase theres any confusion here are some examples to show the problem:

User enters string "claws" --> string matches data in "name" column --> prints row that column is in

User enters string "m" --> string only matches data in "sex" column --> nothing shows!

Thanks for your time!

Just a tip. Don't do this. Your queries have no indexes to use, meaning full table scanes several times. — Layke
– Layke, Commented Jan 24, 2012 at 9:57
There is no limit on OR, and your query should work. However, your PHP example contains a totally different query than your first example (company_name instead of species etc). Try printing your SQL before executing it, you'll see what's wrong fast. Print it, copy paste it into your SQL client, and execute it yourself. — Konerak
– Konerak, Commented Jan 24, 2012 at 10:00
Sorry I pasted the wrong SQL - heres is the new one @Konerak — tomaytotomato
– tomaytotomato, Commented Jan 24, 2012 at 10:03

Justin Yost · Accepted Answer · 2014-04-21 15:17:39Z

3

Your query

$result = $db->query("
  SELECT * FROM contact 
  WHERE first_name LIKE \"%$search%\" 
  OR second_name LIKE \"%$search%\" 
  OR company_name LIKE \"%$search%\" 
  OR email LIKE \"%$search%\" 
  GROUP BY first_name, second_name, company_name, email
");

seems to belong to another table. Also, why do you GROUP BY? Maybe, you meant ORDER BY?

edited Apr 21, 2014 at 15:17

Justin Yost

2,34019 silver badges31 bronze badges

answered Jan 24, 2012 at 10:03

ACNB

8669 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tomaytotomato Over a year ago

i use groupby to remove duplicate rows returned

ACNB Over a year ago

Use Select DISTINCT... for that.

Collectives™ on Stack Overflow

Multiple LIKE comparisons in MySQL query? - MySQL, PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related