CODEWITHSUNDEEP
phpmysql
James Clare
James Clare

Reputation: 563

Searching a table's multiple columns MySQL

The below code works correctly in Sequel Pro, however when I am trying to run it using PHP MySQLi, it doesn't do anything. Is this the best approach to my problem of searching one query over multiple columns?

Many thanks!

SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE company_name LIKE '%hudd%'
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE additional_contacts LIKE '%hudd%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE company_number LIKE '%hudd%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE city LIKE '%lee%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE email LIKE '%miln.co%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE CONCAT_WS(" ", 'first_name', 'last_name') LIKE '%hudd%'
ORDER BY company_name

Upvotes: 0

Views: 42

Answers (2)

Satender K
Satender K

Reputation: 581

You can try this :

SELECT 
    DISTINCT *
FROM
    suppliers
        JOIN
    users ON suppliers.user_id = users.id
WHERE
    company_name LIKE '%hudd%'
        OR additional_contacts LIKE '%hudd%'
        OR company_number LIKE '%hudd%'
        OR city LIKE '%lee%'
        OR email LIKE '%miln.co%'
        OR CONCAT_WS(' ', 'first_name', 'last_name') LIKE '%hudd%'
ORDER BY company_name

Upvotes: 1

Harshit
Harshit

Reputation: 5157

Use BOOLEAN MODE

SELECT * FROM test WHERE MATCH (column1,column2) 
     AGAINST ('+value1 +value2 +value3' IN BOOLEAN MODE);

Upvotes: 0

Related Questions