php - MySQL php 从数据库中获取项目并存储在 XML 中(重复项目)

Question

首先这是我的代码，它可以工作，只是有一个主要缺陷，我不知道如何修复。为了更好的阅读目的，我已经清除了所有元素的代码，除了导致问题的元素。

    $servername = "localhost";
    $username = DB_USERNAME;
    $password = DB_PASSWORD;
    $database = DB_DATABASE;

    // Start of table variables

    // End of table variables
    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);

    $sql = "SELECT pd.name as product_name, p.model as product_model, p.quantity as product_quantity, cd.name as category_name , p.product_id as product_id , p.price as product_price , m.name as product_manufacturer ,  p.image as product_image , pd.description as product_description
            FROM product p
            INNER JOIN product_description pd ON pd.product_id = p.product_id
            INNER JOIN product_to_category ptc ON ptc.product_id = p.product_id
            INNER JOIN category_description cd ON cd.category_id = ptc.category_id
            INNER JOIN manufacturer m ON m.manufacturer_id = p.manufacturer_id
            WHERE pd.language_id = 2 AND cd.language_id = 2 AND p.status = 1 AND p.product_id = p.product_id AND p.manufacturer_id = p.manufacturer_id";

    $conn->set_charset('utf8mb4');
    $result = $conn->query($sql);

    $xml = new XMLWriter();

    $xml->openURI("php://output");
    $xml->startDocument();
    $xml->setIndent(true);

    $xml->startElement('products');

    if ($result->num_rows > 0) {
        while ($product = $result->fetch_assoc()) {
            $product_id             = $product["product_id"];
            $product_category       = $product["category_name"];

                $xml->startElement("product");

                    $xml->startElement("itemid");
                        $xml->writeRaw($product_model);
                    $xml->endElement();


                    $xml->startElement("category");
                        $xml->writeCDATA($product_category);
                    $xml->endElement();
                    //end

                $xml->endElement();

        }
    }else{

    }

    $xml->endElement();

    $xml->flush();

?>

当我运行我的代码时，一切都开始了，它生成了 XML 结构。我在下面发布的 XML 是 ID 为 23793

的第一个产品

代码重复了我的产品 3 次，因为每个产品都有一个主要类别游戏、子类别PS3 和子子类别战斗

<product>
    <itemid>pls-23793</itemid>
    <category>
    <![CDATA[ Games ]]>
    </category>
</product>
<product>
    <itemid>pls-23793</itemid>
    <category>
    <![CDATA[ PS3 ]]>
    </category>
</product>
<product>
    <itemid>pls-23793</itemid>
    <category>
    <![CDATA[ Fighting ]]>
    </category>
</product>

数据库中都是这样存储的

示例:

这是 INNER JOIN INNER JOIN product_to_category ptc ON ptc.product_id = p.product_id

product_to_category 结构:

product_id | category_id

   23793   |  58  (This is the ID of Games)
   23793   |  135 (This is the ID of PS3)
   23793   |  777 (This is the ID of Fighting)

我怎样才能让它不回显同一个产品 3 次，但对于产品 23793 获取这些类别，如游戏 > PS3 > 战斗

我尝试在类别上执行 foreach 循环，但我不认为它来自 php，但问题来自 MySQL Select 代码

有什么想法吗？

Answer 1

您需要将 GROUP_CONCAT 添加到您的查询中

SELECT 
    pd.name as product_name, 
    p.model as product_model, 
    p.quantity as product_quantity, 
    GROUP_CONCAT(cd.name SEPARATOR ' > ') as category_name, 
    p.product_id as product_id, 
    p.price as product_price, 
    m.name as product_manufacturer,  
    p.image as product_image, 
    pd.description as product_description
FROM product p
INNER JOIN product_description pd ON pd.product_id = p.product_id
INNER JOIN product_to_category ptc ON ptc.product_id = p.product_id
INNER JOIN category_description cd ON cd.category_id = ptc.category_id
INNER JOIN manufacturer m ON m.manufacturer_id = p.manufacturer_id
WHERE 
    pd.language_id = 2 
    AND cd.language_id = 2 
    AND p.status = 1 
    AND p.product_id = p.product_id 
    AND p.manufacturer_id = p.manufacturer_id
GROUP BY p.product_id