PHP选择多个数组表

Question

是否可以对多个数组表进行SELECT。我知道这听起来令人困惑，但这就是我所做的:

首先，我创建了一个表单，它有两个复选框选项，如下所示:

<form action="something.php" method="post">
<input type="checkbox" name="store[]" value="M1">
<input type="checkbox" name="store[]" value="M2">
<input type="submit" value="Go">
</form>

现在提交表单后，我可以通过执行foreach循环来查看选择的商店:

$allstore = $_POST['store'];
 foreach ($allstore as $store=>$value) {
echo $value;
}

到目前为止一切都按需要进行!然而，复选框中的这两个值被视为表名称!现在我怎样才能找到一种方法让 PHP 根据用户选择 $value 选择一个或两个表？

$query = "SELECT * from **{$allstore[0]},{$allstore[1]}** WHERE .....";

正如您所看到的，{$allstore[0]},{$allstore[1]} 应该基于 foreach 循环创建。我似乎找不到办法做到这一点!我可以插入一个函数来帮我做这件事吗？

像这样:$query = "SELECT * from ((( Function Here ))) WHERE .....";

如果您有不同的方法，请分享。

<小时/>

编辑:

M1表

id |item_no |qty |price
1   x1       10   20
2   x2       5    22
3   x3       3    5

M2表

id |item_no |qty |price
1   x1      11    20
2   x9      5     30
3   x10     6     26

输出表应该是

item_no | price |   M1 |  M2
x1         20       10    11
x2         22       5     N/A
x3         5        3     N/A
x9         30       N/A   5
x10        26       N/A   6

这就是我的目标。希望能解决!

Answer 1

这是 2 个表的结构 sqlfiddle我认为您可以从这里添加更多表格。

SELECT T1.item_no,
  COALESCE(M1.price,M2.price) as price,
  M1.qty as M1,
  M2.qty as M2
FROM
  (SELECT item_no FROM M1
    UNION
   SELECT item_no FROM M2
   )T1
   LEFT JOIN M1 ON T1.item_no = M1.item_no
   LEFT JOIN M2 ON T1.item_no = M2.item_no

更新:我对 PHP 不太熟悉，但我查找了一些语法，并且能够基于 ["M1","M2"] 或 ["M1"] 或 ["M2"数组动态生成 SQL ]

DynamicPHPtobuildSQL

<?php
        //Enter your code here, enjoy!

$allstore = ["M2"];
$item = 0;
$sqlpart1 = "";
$sqlpart2 = "";
$sqlpart3 = "";
$sqlpart4 = "";
foreach ($allstore as $store=>$value) {
   $item += 1;
   if ($item > 1){
     $sqlpart1 .= ",";
     $sqlpart2 .= ",";
     $sqlpart3 .= " UNION ";
   }
   $sqlpart1 .= $value . ".price ";
   $sqlpart2 .= $value . ".qty as " . $value . " ";
   $sqlpart3 .= "SELECT item_no FROM " . $value . " ";
   $sqlpart4 .= "LEFT JOIN " . $value . " ON T1.item_no=" . $value . ".item_no ";
}
$SQL = "SELECT T1.item_no,COALESCE(" . $sqlpart1 . ") as price," . $sqlpart2;
$SQL .= "FROM (" . $sqlpart3 . ")T1 " . $sqlpart4;
echo $SQL;
?>