How to fetch data without duplicating products after adding price and stocks

Question

I have a problem in adding price, size and stocks. How can I add price, stocks and size without duplicating?

Here is my database structure:

tblproduct

tblextension

PS: tblproduct product_id and tblproduct_extension product_id are the same.

---

Here is the result after I add price, stocks and price. As you can see there are two 'Nike Lebron 15 Ghost'.

query

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');
            }
        });
    });
}

class.php

    //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);
    }

Code to display products

    //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

$search_query= "SELECT DISTINCT * FROM tblproduct LEFT JOIN tblproduct_extension ON tblproduct_extension.product_id = tblproduct.product_id WHERE tblproduct.segment_id='$id'";

The SELECT DISTINCT statement is used to return only distinct (different) values.

If you don't want duplicates you can trim them from your query.

Answer 2

As you can have multiple product_size, you can display also this information with product name

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

You can do a bit more complex query for achieve what you want (function get_products).

First Select only unique couple of product/extension, with smallest key of extension. From this, you do a second select who fetch data to display.

Those queries were not tested, but that give an idea.

With GROUP BY

-- 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            

With DISTINCT

-- 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