php - 在多个表中搜索关键字

Question

我有下表，

state : state_id, state_name
city  : city_id, city_name, state_id
category : category_id, category_name
products : product_id, product_name, cateogory_id, product_description, city_id

我想在所有表格中搜索给定的关键字。

我尝试使用 LIKE，它返回许多行，

我尝试过的代码，

keyword = 'apple';

SELECT 
    * 
FROM 
    products AS p, 
    categories AS cat, 
    city AS c, 
    state as s 
WHERE 
    p.category_id=cat.category_id 
    AND p.city_id=c.city_id 
    AND c.state_id=s.state_id 
    AND p.product_name LIKE '%apple%' 
    OR p.product_description LIKE '%apple%' 
    OR cat.category_name LIKE '%apple%' 
    OR c.city_name LIKE '%apple%' 
    OR s.state_name LIKE '%apple%' 

有什么帮助吗？

Answer 1

如果要查询精确匹配，请使用=:

SELECT 
    * 
FROM 
    products AS p, 
    categories AS cat, 
    city AS c, 
    state as s 
WHERE 
    p.category_id=cat.category_id 
    AND p.city_id=c.city_id 
    AND c.state_id=s.state_id 
    AND p.product_name = 'apple' 
    OR p.product_description = 'apple'  
    OR cat.category_name LIKE = 'apple' 
    OR c.city_name LIKE = 'apple' 
    OR s.state_name LIKE = 'apple' 

如果您想返回包含“apple”单词的所有行，请尝试在其周围添加空格，如下所示:

SELECT 
    * 
FROM 
    products AS p, 
    categories AS cat, 
    city AS c, 
    state as s 
WHERE 
    p.category_id=cat.category_id 
    AND p.city_id=c.city_id 
    AND c.state_id=s.state_id 
    AND p.product_name LIKE '% apple%' 
    OR p.product_name LIKE '%apple %' 
    OR p.product_description LIKE '% apple%' 
    OR p.product_description LIKE '%apple %' 
    OR cat.category_name LIKE '% apple%' 
    OR cat.category_name LIKE '%apple %' 
    OR c.city_name LIKE '% apple%' 
    OR c.city_name LIKE '%apple %' 
    OR s.state_name LIKE '% apple%'
    OR s.state_name LIKE '%apple %'