增加价格和股票后，如何在不重复产品的情况下取数

Question

我有一个问题在添加价格，大小和库存。如何添加价格、库存和大小而不重复？

下面是我的数据库结构：

TBLProduct

这是我将价格、股票和价格相加后的结果。你可以看到有两个‘耐克勒布朗15鬼’。

查询

SELECT * FROM tblproduct LEFT JOIN tblproduct_extension ON tblproduct_extension.product_id = tblproduct.product_id;

Ajax

//Show modal for adding size, price and stocks
function add_product_extension() {
    $('#product_add_extension').click(function(e){
        var product_id = $('#product_id').val();
        var product_price = $('#product_price').val();
        var product_stocks = $('#product_stocks').val();
        var product_size = $('#product_size').val();
        $.ajax({
            type: 'POST',
            url: '../admin/class.php',
            data: {product_id:product_id,product_price:product_price,product_stocks:product_stocks,product_size:product_size},
            success:function(data) {
                $('#addSize').modal('hide');
            }
        });
    });
}

    //Add size, price, stocks
    if(isset($_POST['product_id'])) {
        $product_price = $_POST['product_price'];
        $product_stocks = $_POST['product_stocks'];
        $product_size = $_POST['product_size'];
        $product_id = $_POST['product_id'];

        $insert_query = "INSERT INTO tblproduct_extension (product_stocks, product_price, product_size, product_id)VALUES('$product_stocks', '$product_price', '$product_size', '$product_id')";
        $query = mysqli_query($db_conn, $insert_query);
    }

显示产品的代码

    //Get Products by ID
    function get_products() {
        GLOBAL $db_conn;
        $id = $_GET['id'];
        $search_query= "SELECT * FROM tblproduct LEFT JOIN tblproduct_extension ON tblproduct_extension.product_id = tblproduct.product_id WHERE tblproduct.segment_id='$id'";
        $query = mysqli_query($db_conn, $search_query);

        while ($row = mysqli_fetch_array($query)) {
            ?>
                <div class="products">
                    <a href="details.php?view_product=<?=$row['product_id']?>&id=<?=$row['segment_id']?>"><img class="img_product" src="../admin/<?=$row['product_image']?>" alt="<?=$row['product_name']?>"></a>
                    <?php if($row['product_stocks'] == 0) {?>
                        <span class="stock-label">SOLD OUT</span>
                    <?php } ?>
                    <h4><?=$row['product_name']?></h4>
                    <h3>&#8369;<?=number_format($row['product_price'], 2)?></h3>
                </div>
            <?php
        }
        return $row;
    }

Answer 1

由于可以有多个product_size，因此也可以使用产品名称显示此信息

...
<h4><?=$row['product_name']?> - Size <?=$row['product_size']?></h4>
...

您可以执行稍微复杂一点查询来实现您想要的（函数get_products）。

首先，仅选择唯一的产品/扩展项对，使用最小的扩展项键。在此基础上，进行第二次选择谁提取要显示的数据。

带分组依据

-- 2. After you select your data with unique id get from sub query
SELECT      p.*, pe.*
FROM        tblproduct p
JOIN        (
            -- 1. First select your product_id and smallest product_extension_id
            SELECT      p.product_id, 
                        MIN(pe.product_extension_id) as product_extension_id
            FROM        tblproduct p
            LEFT JOIN   tblproduct_extension pe
            ON          pe.product_id = p.product_id 
            WHERE       p.segment_id='$id'
            GROUP BY    p.product_id
) product_unique
JOIN        tblproduct_extension pe
ON          pe.product_extension_id = product_unique.product_extension_id
WHERE       p.product_id = product_unique.product_id            

带有不同的

-- 2. After you select your data with unique id get from sub query
SELECT      p.*, pe.*
FROM        tblproduct p
JOIN        (
            -- 1. First select your product_id and smallest product_extension_id
            SELECT      DISTINCT
                        p.product_id, 
                        MIN(pe.product_extension_id) as product_extension_id
            FROM        tblproduct p
            LEFT JOIN   tblproduct_extension pe
            ON          pe.product_id = p.product_id 
            WHERE       p.segment_id='$id'
) product_unique
JOIN        tblproduct_extension pe
ON          pe.product_extension_id = product_unique.product_extension_id
WHERE       p.product_id = product_unique.product_id