SQL 和 PHP 问题 - 仅第一类

Question

我在从数据库调用一些数据时遇到问题...

我有每个产品只有 1 个 ID 的正常数据，但我有一个具有两次或多次相同 id 的产品的类别表，因为产品可以有多个类别。我有一个调用数据的查询，但我不知道如何仅选择类别表中的第一个类别。

例如，表：

product_id    |    category_id
2             |  31
2             |  12
2             |  31
3             |  14
3             |  38
4             |  10

我用这个查询来调用其他数据：

SELECT toc_products.products_id, 
       toc_products_description.products_name, 
       toc_products.manufacturers_id, 
       toc_products.products_price, 
       toc_products_description.products_url, 
       toc_manufacturers.manufacturers_id, 
       toc_manufacturers.manufacturers_name 
FROM   toc_products, 
       toc_products_description, 
       toc_manufacturers 
WHERE  toc_products.products_id = toc_products_description.products_id 
       AND toc_products.manufacturers_id = toc_manufacturers.manufacturers_id 
       AND toc_products_description.language_id = 14 
ORDER  BY toc_products.products_id 

所以我需要在 while 中调用所有这些，现在我只有这个：

    while($red = mysql_fetch_array($rezultat)){
    if ($red['products_quantity'] = 0) {$red['products_quantity'] = 'Nicht lagernd';}
    else {$red['products_quantity'] = 'Lagernd';}

    echo $red['products_id']. ", ". $red['products_name']. ", ". $red['products_price']. ", ". $red['manufacturers_name']. ", ". $red['products_url'];
    echo "<br />";
}

有没有办法在查询中仅调用带有 ID 的第一个值，当下一个有相同的ID时跳过它？

请帮忙，抱歉我的英语...谢谢

Answer 1

你基本上有3个选择。

第一个是在 sql 中选择类别 ID，并按其排序。实际上您的订单按 1) 产品和 2) 类别进行。然后你可以检查 php 是否发生了变化，就像这样。

$oldCategory = null;
while($red = mysql_fetch_array($rezultat))
{
    if ( $oldCategory != $red['category'] )
    {
    if ($red['products_quantity'] = 0) $red['products_quantity'] = 'Nicht lagernd';
    else $red['products_quantity'] = 'Lagernd';

    echo $red['products_id'], ", ", $red['products_name'], ", ", $red['products_price'], ", ", $red['manufacturers_name'], ", ", $red['products_url'];
    echo "<br />";
    }
    $oldCategory = $red['category'];
}

第二个：您不更改 sql，并且可以创建一个 php 函数，从数据库中检索每个产品的类别。

第三：你用纯sql来做。祝你好运。

Answer 2

只需将 GROUP BY Category_id 添加到语句底部即可

SELECT toc_products.products_id, 
       toc_products_description.products_name, 
       toc_products.manufacturers_id, 
       toc_products.products_price, 
       toc_products_description.products_url, 
       toc_manufacturers.manufacturers_id, 
       toc_manufacturers.manufacturers_name 
FROM   toc_products, 
       toc_products_description, 
       toc_manufacturers 
WHERE  toc_products.products_id = toc_products_description.products_id 
       AND toc_products.manufacturers_id = toc_manufacturers.manufacturers_id 
       AND toc_products_description.language_id = 14 
ORDER  BY toc_products.products_id
GROUP BY category_id