如何显示SQL表中的所有记录并将这些记录与另一个表匹配？

I have set the PHP variable $accountnumber to be that of the user who is viewing their profile page. On the page, I have a block with the user's information populated from the database, and I have a list of all products that we have, and I want to put a check mark next to each one that the customer has by assigning a class to it.

我已将PHP变量$ accountnumber设置为正在查看其个人资料页面的用户的变量。在页面上，我有一个块，其中包含从数据库填充的用户信息，我有一个包含我们所有产品的列表，并且我想在客户所拥有的每个产品旁边放一个复选标记，方法是将类分配给它。

Here are my tables:

这是我的表格：

products
id | name | url    | weight
100  p1     p1.html  1
101  p2     p2.html  2
102  p3     p3.html  3
103  p4     p4.html  4
104  p5     p5.html  5
105  p6     p6.html  6

products_accounts
account_number | product_id
0000001           100
0000001           104
0000001           105
0000002           101
0000002           103
0000002           104
0000002           105
0000003           100
0000003           102

I tried a LEFT OUTER JOIN, but was not able to determine if the $accountnumber matched an account_number in the products_accounts table for a specific product_id. The only way that I was able to accomplish this was to add a WHERE statement like this:

我尝试了LEFT OUTER JOIN，但无法确定$ accountnumber是否与products_accounts表中的account_number匹配特定product_id。我能够完成此任务的唯一方法是添加一个这样的WHERE语句：

WHERE products_acccounts.account_number = '$accountnumber'

It gave the proper class to the product, but only showed the product that they had instead of all.

它为产品提供了适当的等级，但只展示了他们拥有的产品而不是全部产品。

Here's my code:

这是我的代码：

$sql ="
SELECT
    products.id,
    products.name,
    products.url,
    products_accounts.account_number
FROM
    products
LEFT OUTER JOIN
    products_accounts
ON
    products.id = products_accounts.product_id

";

$sql .="
GROUP BY
    products.id
ORDER BY
    products.weight
";

$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    echo '<span class="'; if($row['account_number'] == '$accountnumber')
    { echo'product_yes">'; } else { echo 'product_no">'; }
    echo '<a href="' . $row['url'] . '">' . $row['name'] . '</a><br /></span>';
}

If a customer has all product except P2 and P5, it SHOULD display like this:

如果客户拥有除P2和P5之外的所有产品，它应该显示如下：

✓P1

✓P1

P2

✓P3

✓P4

P5

✓P6

4 个解决方案

#1

$getproducts = mysql_query("
SELECT id, name, url
FROM products
ORDER BY weight ASC");

while ($rowproducts = mysql_fetch_assoc($getproducts)) {

$product_id = $rowproduct['id'];
$product_name = $rowproduct['name'];
$product_url = $rowproduct['url'];

$getuserhasproduct = mysql_query("
SELECT DISTINCT product_id
FROM products_accounts
WHERE account_number = $accountnumber
AND product_id = $product_id");
$user_has_product = mysql_num_rows($getuserhasproduct);

if($user_has_product){
$class = "checked";
}

echo "<span class='$class'><a href='$product_url'>$product_name</a></span>";
unset($class);
} // end loop 

This might help with performance

这可能有助于提高性能

$getproducts = mysql_query("SELECT id, name, url,
(SELECT DISTINCT product_id
FROM products_accounts
WHERE account_number = '$accountnumber'
AND product_id = products.id) AS product_count
FROM products
ORDER BY weight ASC");

while ($rowproducts = mysql_fetch_assoc($getproducts)) {

$product_id = $rowproduct['id'];
$product_name = $rowproduct['name'];
$product_url = $rowproduct['url'];
$product_count = $rowproduct['product_count'];

if($product_count > 0){
$class = "checked";
}

echo "<span class='$class'><a href='$product_url'>$product_name</a></span>";
unset($class);
} // end loop

#2

It's better to filter out rows using SQL than PHP, like below:

最好使用SQL而不是PHP过滤掉行，如下所示：

$sql ="
SELECT
    p.id,
    p.name,
    p.url,
    pa.account_number
FROM
    products p
LEFT OUTER JOIN
    products_accounts pa
ON
    p.id = pa.product_id
    AND
    pa.account_number = ".mysql_real_escape_string($accountnumber)."
ORDER BY
    p.weight
";


$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    echo '<span class="'; if(!is_null($row['account_number']))
    { echo'product_yes">'; } else { echo 'product_no">'; }
    echo '<a href="' . $row['url'] . '">' . $row['name'] . '</a><br /></span>';
}

#3

SELECT
    products.id,
    products.name,
    products.url,
    products_accounts.account_number
FROM
    products
LEFT OUTER JOIN
    (SELECT * FROM products_accounts WHERE account_number = $account_number) as products
ON
    products.id = products_accounts.product_id
WHERE 
";

$sql .="
GROUP BY
    products.id
ORDER BY
    products.weight
";

i think this is your answer, you need to filter your join table before the join. please check the syntax as i am not that familiar with php.

我认为这是你的答案，你需要在加入之前过滤你的连接表。请检查语法，因为我不熟悉php。

#4

You're trying to use GROUP BY in a context that doesn't make sense if you want to retrieve all of the records. The GROUP BY clause should only be used if you want to aggregate data (i.e. get the sum, average, etc. of a bunch of records).

如果要检索所有记录，则尝试在无意义的上下文中使用GROUP BY。只有在想要聚合数据时才能使用GROUP BY子句（即获取一堆记录的总和，平均值等）。