Get quantity of certain product from database instead of session - Shopping Cart (PHP & MySQL)

Question

I have been trying to create a persistent MySQL-based shopping cart application starting from a session-based php shopping cart. The cart table has the structure userid | productid | quantity | postingdate. In the session-based implementation, the quantity info for a certain product was stored in $_SESSION['cart'][$row['id']]['quantity'];; I am facing trouble with retrieving the quantity info for a certain product from the cart table in the database because I can't seem to find how to extract the productId necessary for the $getquantity query.

$sql1 = "SELECT * FROM cart WHERE userid='$userId'"; //selects the user's cart
$query1=mysqli_query($con,$sql1);
if (!empty($query1)) {    //if the user's cart isn't empty
    while($row = mysqli_fetch_array($query1)){
        $query = mysqli_query($con,"SELECT * FROM products JOIN cart ON cart.productid=products.id 
ORDER BY cart.postingDate DESC"); //selects all the products in the current user's cart
    } 
    $totalprice=0;
    $totalqunty=0;
    if (!empty($query)) {
        while ($row = mysqli_fetch_array($query)) { //for each product
            //$quantity=$_SESSION['cart'][$row['id']]['quantity']; //SESSION IMPLEMENTATION
            $id = $row['id'];
            $quantity = mysqli_query($con,"SELECT quantity FROM cart WHERE userid='$userId' AND productid='$id'"); //MY IMPLEMENTATION, THE WRONG PART IS WITH $id
            $subtotal= $quantity*$row['productPrice']+$row['shippingCharge'];
            $totalprice += $subtotal;  
            $_SESSION['qnty']=$totalqunty+=$quantity; //SESSION IMPLEMENTATION
        }
    }
}

I've tried $row['id'] but it returns an array of all the productid from the records returned by $query and MySQl doesn't seem to accept it, while instead I need the $getquantity to run on each productid. Please don't mind how it's vulnerable to SQL injections as I will get to that in later stages.

question from:https://stackoverflow.com/questions/65944570/get-quantity-of-certain-product-from-database-instead-of-session-shopping-cart

Answer 1

Here's a working solution: I could have just selected the cart.quantity attribute (considering how the products and cart table were joined):

$sql1 = "SELECT * FROM cart WHERE userid='$userId'"; //selects the user's cart
$query1=mysqli_query($con,$sql1);
if (!empty($query1)) {    //if the user's cart isn't empty
    while($row = mysqli_fetch_array($query1)){
        $query = mysqli_query($con,"SELECT products.productImage1 , products.productName ,
                                                    products.id, cart.productId, cart.quantity as qty, products.productPrice,
                                                    products.shippingCharge  FROM products JOIN cart ON cart.productid=products.id 
ORDER BY cart.postingDate DESC"); //selects all the products in the current user's cart
    } 
    $totalprice=0;
    $totalqunty=0;
    if (!empty($query)) {
        while ($row = mysqli_fetch_array($query)) { //for each product
            $quantity=$row['qty'];
            $subtotal= $quantity*$row['productPrice']+$row['shippingCharge'];
            $totalprice += $subtotal;   
        }
    }
}