php - php中的SQL强制WHERE IN查找多次相同的id

Question

我正在尝试制作一个产品具有自定义功能的购物车...为了避免数据库中的大量查询，我尝试在查询后将结果保存在数组中。查询如下所示:

$sql = 'SELECT * 
            FROM cc_restaurants_menu 
            WHERE menu_id 
            IN ("7","50","50") 
            ORDER BY FIELD (menu_id,"7","50","50")';
$result = $conn->query($sql);

if($result->rowCount() > 0) {
    while($row = $result->fetch()) {
        $names[] = $row['menu_product'] .'<br>';
    }
}

但这样做的问题是查询避免了重复的 id。 (我需要重复的 ID，因为人们可以多次添加产品)。

是否有办法多次获取重复的 id？

我唯一的想法是做类似的事情(如果 ids 在数组中):

foreach($ids as $d) {
  //query here the database based in each id
} 

提前致谢

Answer 1

认为您可能需要对每个 menu_id 进行一次查询，并使用 UNION ALL 将结果连接在一起。

类似这样的:-

SELECT * 
FROM cc_restaurants_menu 
WHERE menu_id = "7"
UNION ALL
SELECT * 
FROM cc_restaurants_menu 
WHERE menu_id = "50"
UNION ALL
SELECT * 
FROM cc_restaurants_menu 
WHERE menu_id = "50"
ORDER BY FIELD (menu_id,"7","50","50")

或者在子查询中构建所需的菜单 ID，然后将其连接到主表。

SELECT cc_restaurants_menu.* 
FROM cc_restaurants_menu 
INNER JOIN
(
    SELECT 7 AS a_menu_id UNION ALL SELECT 50 UNION ALL SELECT 50
) sub0
ON cc_restaurants_menu.menu_id = sub0.a_menu_id
ORDER BY FIELD (cc_restaurants_menu.menu_id,"7","50","50")

使用 implode 生成第一个查询将如下完成:-

$sql = "SELECT * FROM cc_restaurants_menu WHERE menu_id = '".implode("' UNION ALL SELECT * FROM cc_restaurants_menu WHERE menu_id = '", id_array)."' ORDER BY FIELD (menu_id,'".implode("','", id_array)."')";

并通过内爆生成第二个查询将完成如下操作:-

$sql = "SELECT cc_restaurants_menu.* FROM cc_restaurants_menu INNER JOIN (SELECT ".implode(" AS a_menu_id UNION ALL SELECT ", id_array) AS a_menu_id ) sub0 ON cc_restaurants_menu.menu_id = sub0.a_menu_id ORDER BY FIELD (cc_restaurants_menu.menu_id,".implode(",", id_array).")";