CODEWITHSUNDEEP
phpmysqlsql-like
tomaytotomato
tomaytotomato

Reputation: 4028

Multiple LIKE comparisons in MySQL query? - MySQL, PHP

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!

Upvotes: 2

Views: 13569

Answers (1)

ACNB
ACNB

Reputation: 846

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?

Upvotes: 3

Related Questions