php - 迭代两个不同长度的 POST 数组，查找购买了 x,y,z 但未购买 x,y,x,z 的客户

Question

那里的所有答案都涉及迭代两个或多个相同长度或相同键的数组，但不是这里。我尝试了所有的想法都无济于事。

我在同一个 POST 表单中提交了两个多项选择 - 已购买的产品和未购买的产品。它们包含相同的产品，但由于多重选择很少会在数组中产生相同的项目(想想有多少种组合。)第一个 foreach 工作正常，并从 MySQL 中提取所有拥有任何产品的客户所选“已购买”商品的数量。然而，第二个嵌套的 foreach 不会迭代 - 它只是从“hasn't Bring”数组中获取第一个值。

HTML 部分:

Find customers who have bought:

<form method="post" action="">
<select name="bought[]" size="5" multiple="multiple">
<option value="RD"> Brown T Shirt </option>
<option value="RDX"> Red T Shirt </option>
<option value="HD"> White T Shirt </option>
<option value="CB"> Black T Shirt</option>
<option value="DA"> Green T Shirt </option>
</select>

But who haven't bought:

<select name="notbought[]" size="5" multiple="multiple">
<option value="RD"> Brown T Shirt </option>
<option value="RDX"> Red T Shirt </option>
<option value="HD"> White T Shirt </option>
<option value="CB"> Black T Shirt</option>
<option value="DA"> Green T Shirt </option>
</select>

<div class="buttons>
<button class="large" type="submit">Get the CSV!</button>
</div>
</form>

然后是 PHP:

<?php
foreach($_POST['bought'] as $bought) {
foreach($_POST['notbought'] as $notbought){

$selectsubs = mysqli_query("SELECT First_Name, Last_Name, Email, Territory
                FROM Customers
                WHERE Customer_Type = 'Active'
                AND Products_Bought LIKE '%$bought%'
                AND Products_Bought NOT LIKE '%$notbought%'
                ");


 while($row = mysqli_fetch_assoc($selectsubs))  {

$csvWriter->addRow();
$csvWriter->addCol($row['Email']);
$csvWriter->addCol($row['First_Name']);
$csvWriter->addCol($row['Last_Name']);
$csvWriter->addCol($row['Territory']);
    }
  }
}
$csvWriter->writeFile("customers.csv");
?>

Answer 1

假设 Products_Bought 字段是逗号分隔的产品列表，我很想在一条 SQL 中执行此操作，如下所示(未测试):-

foreach($_POST['bought'] AS $key=>$value) $_POST['bought'][$key] = mysqli_real_escape_string($con, $_POST['bought'][$key]);
foreach($_POST['notbought'] AS $key=>$value) $_POST['notbought'][$key] = mysqli_real_escape_string($con, $_POST['notbought'][$key]);

$bought = 'FIND_IN_SET("'.implode('", REPLACE(Products_Bought, "-", ",")) AND FIND_IN_SET("', $_POST['bought']).'", REPLACE(Products_Bought, "-", ","))';
$notbought = '!FIND_IN_SET("'.implode('", REPLACE(Products_Bought, "-", ",")) AND !FIND_IN_SET("', $_POST['notbought']).'", REPLACE(Products_Bought, "-", ","))';

$selectsubs = mysqli_query("SELECT First_Name, Last_Name, Email, Territory
            FROM Customers
            WHERE Customer_Type = 'Active'
            AND $bought
            AND $notbought");

这应该构建如下 SQL 片段:-

SELECT First_Name, Last_Name, Email, Territory
FROM Customers
WHERE Customer_Type = 'Active'
AND FIND_IN_SET("RD", REPLACE(Products_Bought, '-', ','))
AND FIND_IN_SET("RDX", REPLACE(Products_Bought, '-', ','))
AND !FIND_IN_SET("HD", REPLACE(Products_Bought, '-', ','))
AND !FIND_IN_SET("CB", REPLACE(Products_Bought, '-', ','))

但是，最好将购买的商品拆分到另一个表中，每个客户/商品一行，而不是使用分隔列表。