我尝试执行数据库搜索产品，该产品有6个表，直到我使用product_subcategory表，它都可以正常工作，它返回带有循环的相同1行或重复的行（大约250个相同值的行，可能是因为子类别包含很多条目）。product_subcategory包含subcategory_id，category_id，subcategory_name列。产品子类别的信息以subcategory_id的形式存储在产品表中，格式为（15,24,1,3,8），（所以这可能是即时通讯在做错事情的地方）
我已经尝试了IN或OR的一些变体..但现在我被卡住了..现在需要帮助给出一个主意（$ branch ='Tokyo'和$ query ='Mobiles'），并且很多子类别都包含Mobiles这个词。
我想要的最终结果是我可以使用返回结果
$sql = "SELECT t1.*, t2.*, t3.* , t4.*, t5.*, t6.*
FROM
products t1, product_details t2, product_warehouses t3, product_courier t4, product_category t5, product_subcategory t6
WHERE
t1.product_id = t2.product_id AND
t1.warehouse_id = t3.warehouse_id AND
t4.destination = t3.warehouse_branch AND
t1.category_id = t5.category_id AND
t3.warehouse_branch = ".$branch." AND
t5.category_name LIKE '%".$query."%' UNION
SELECT t1.*, t2.*, t3.* , t4.*, t5.*, t6.*
FROM
products t1, product_details t2, product_warehouses t3, product_courier t4, product_category t5, product_subcategory t6
WHERE
t1.product_id = t2.product_id AND
t1.warehouse_id = t3.warehouse_id AND
t4.destination = t3.warehouse_branch AND
t1.category_id = t5.category_id AND
t3.warehouse_branch = ".$branch." AND
t2.product_name LIKE '%".$query."%' UNION
SELECT t1.*, t2.*, t3.* , t4.*, t5.*, t6.*
FROM
products t1, product_details t2, product_warehouses t3, product_courier t4, product_category t5, product_subcategory t6
WHERE
t1.product_id = t2.product_id AND
t1.warehouse_id = t3.warehouse_id AND
t4.destination = t3.warehouse_branch AND
t1.category_id = t5.category_id AND
t3.warehouse_branch = ".$branch." AND
t1.product_title LIKE '%".$query."%' UNION
SELECT t1.*, t2.*, t3.* , t4.*, t5.*, t6.*
FROM
products t1, product_details t2, product_warehouses t3, product_courier t4, product_category t5, product_subcategory t6
WHERE
t1.product_id = t2.product_id AND
t1.warehouse_id = t3.warehouse_id AND
t4.destination = t3.warehouse_branch AND
t1.category_id = t5.category_id AND
t3.warehouse_branch = ".$branch." AND
t6.subcategory_id IN (t1.subcategory_id) AND //i think this is where im lost
t6.subcategory_name LIKE '%".$query."%'
";
当我从所有联合中删除子类别表时，我可以正确执行查询。希望你们能帮助我。
即时通讯使用codeigniter最终结果$ query = $ this-> db-> query（$ sql）; $ result = $ query-> result_array（）;
查询是错误的，为什么您6次添加6个相同的表？尝试这个：
$sql = 'SELECT `id`, `name`
FROM
`products` `p`
JOIN `product_details` `pd` ON `p`.`product_id` = `pd`.`product_id`
JOIN `product_warehouses` `pw` ON `p`.`warehouse_id` = `pw`.`warehouse_id`
JOIN `product_courier` `pco` ON `pco`.`destination` = `pw`.`warehouse_branch`
JOIN `product_category` `pca` ON `pca`.`category_id` = `p`.`category_id`
WHERE
`pw`.`warehouse_branch` = \'' . $branch . '\' AND
`pd`.`product_name` LIKE \'%' . $query . '%\'';
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句