php - MySQL 搜索表的多列

Question

下面的代码在 Sequel Pro 中可以正常工作，但是当我尝试使用 PHP MySQLi 运行它时，它没有执行任何操作。这是解决我在多个列上搜索一个查询的问题的最佳方法吗？

非常感谢!

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

Answer 1

你可以试试这个:

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