Duplicating products after adding price, stocks and size

[zxcyeezus]

How to fetch data without duplicating products after adding price and stocks? I have two tables where tblproduct have product_id, product_name, product_image, category_id and segment_id. The second table tblproduct_extension have product_extension_id, product_price, product_stocks and product_size and foreign key (product_id) from tblproduct.

tblproduct

tblproduct_extension

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

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

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

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

	while ($row = mysqli_fetch_array($query)) {
		?>
			<div class="product-feature">
				<a href="details.php?view_product=<?=$row['product_id']?>&id=<?=$row['segment_id']?>" class="product-item-photo" tabindex="-1">
					<span class="product-image-container" style="width: 307px;">
						<span class="product-image-wrapper" style="padding-bottom: 100%;">
							<img class="product-image-photo" src="../admin/<?=$row['product_image']?>" alt="<?=$row['product_name']?>">
						</span>
					</span>
				</a>
				<?php if($row['product_stocks'] == 0) {?>
					<span class="outofstock-label">SOLD OUT</span>
				<?php } ?>
				<div class="product-item-details">
					<strong class="product-item-name">
						<a href="details.php?view_product=<?=$row['product_id']?>&id=<?=$row['segment_id']?>"><?=$row['product_name']?></a>
					</strong>
					<p class="product-item-brand"><?=$row['product_brand']?></p>
					<div class="product-price-details">
						<span class="price">&#8369;<?=number_format($row['product_price'], 2)?></span>
					</div>
				</div>
			</div>
		<?php
	}
	return $row;
}

[ZooKeeper]

Add a group by clause to your query.

SELECT * FROM tblproduct LEFT JOIN tblproduct_extension ON tblproduct.product_id = tblproduct_extension.product_id WHERE tblproduct.segment_id='$id' GROUP BY tblproduct_extension.product_id

I would question the decision regarding a product having many stocks. I’ve seen that done before for a system that supports multiple store fronts. However, this doesn’t look like the case with your store. Why can a product have multiple stocks?

[zxcyeezus]

Each product has a product size. For example I add a size, stocks on Nike Lebron 15 Ghost where size is 8, stocks is 15, after that I will add a new one; size=9 and the stocks=20. That’s the purpose of the stocks.