英文:
MYSQL LEFT JOIN with multiple rows (php search function)
问题
我有这两个表格：
products:
| id | name |
|---|---|
| 1 | 产品 1 |
| 2 | 产品 2 |
和 featuretoproduct:
| id | productid | featureid | featurecat |
|---|---|---|---|
| 1 | 1 | 7 | 3 |
| 2 | 1 | 5 | 3 |
| 3 | 2 | 5 | 3 |
我尝试搜索具有某些特征ID的产品，有三个选项：
如果我搜索特征ID 7，我只需要获取产品 1。
如果我搜索特征ID 5，我需要获取产品 1 和 2。
如果我搜索特征ID 5 和特征ID 7，我需要获取产品 1。
这是我的PHP函数：
function Get_products3($id, $q)
{
$query = "SELECT p.*
FROM products p
INNER JOIN featuretoproduct fp
ON p.id = fp.productid
" . $q . "
GROUP BY fp.productid ORDER BY p.id ASC";
$Container_db = Sdba::db();
$Container_db->query($query);
return $Container_db->result();
}
我有一个字符串处理左连接的最终查询字符串：
第二部分：
$query = " WHERE ( ";
特征部分：
$features = ["7", "5"]; // 这可以更改为不同的变化，比如 ["5"] 或 ["7"] 或 ["7", "5"]...
foreach ($features as $item):
$query .= "fp.featureid = " . $item . " AND ";
endforeach;
// 最后部分（关闭查询字符串并从最后循环中删除 " AND "）：
$query = substr($query, 0, -5) . " )";
在这里，我调用函数：
$checker = Get_products3($_GET["id"], $query);
我尝试在循环中使用 OR 替代 AND，但如果我选择 ["7", "5"]，则会得到两个产品而不仅仅是产品 1。
希望对您有所帮助。
英文:
i have these two tables:
products:
| id | name |
|---|---|
| 1 | product 1 |
| 2 | product 2 |
and featuretoproduct:
| id | productid | featureid | featurecat |
|---|---|---|---|
| 1 | 1 | 7 | 3 |
| 2 | 1 | 5 | 3 |
| 3 | 2 | 5 | 3 |
i am trying to search a product that have some feature id, and i have 3 options:
if i search featureid 7, i need to get product 1 only.
if i search featureid 5, i need to get product 1 and 2.
if i search featureid 5 and featureid 7, i need to get product 1 only.
this is my php function:
function Get_products3($id,$q)
{
$query = "SELECT p.*
FROM products p
INNER JOIN featuretoproduct fp
ON p.id = fp.productid
".$q."
GROUP BY fp.productid ORDER BY p.id ASC";
$Container_db = Sdba::db();
$Container_db->query($query);
return $Container_db->result();
}
and i have a string that handle the final query string of left join:
$query = " WHERE ( ";
second part:
$features = ["7","5"]; //this can be change to different variations like ["5"] or ["7"] or ["7","5"]...
foreach($features as $item):
$query.="fp.featureid = ".$item." AND ";
endforeach;
//last part (close the query string and remove " AND " from last loop):
$query = substr($query, 0, -5)." )";
here i am calling the function:
$checker = Get_products3($_GET["id"],$query);
i tryed to put OR instead AND in the loop but then if i choose
["7","5"]
i get the two products instead of product 1 only.
any help please?
答案1
得分: 0
你需要计算每个产品的匹配记录数量，并确保该数量与你要搜索的功能数量一样多。
你的WHERE子句需要使用OR（或者使用IN()）选择具有匹配功能ID的记录，因为你想要获取所有与给定功能匹配的产品记录。然后，你仍然要按产品进行分组，并要求该组中的记录数与你的条件匹配，即HAVING COUNT(*) = 2。
这个2是动态确定的，基于你在$features中有多少条目。
英文:
You need to count the number of matching records per product, and make sure that number is as big as the number of features you are searching for.
Your WHERE clause needs to select the records with matching feature IDs using OR (or via IN()), because you want all the records for the product, that match the given features. You still GROUP BY your product, and then demand that the COUNT of records in the group matches that of your criteria - HAVING COUNT(*) = 2
That 2 you'll determine dynamically, based on how many entries you have in $features.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论