php - add quantity + price in separate tables

Question

Good.

I have the Products table + the Cart table

When I Add to Cart.. the function adds 1 record in the Cart table stating the User Name, Product ID and Quantity of products ordered.

Now I want to add the quantity of these products + the price.

function total_price($con) {

    if(isset($_SESSION['u_email'])) {

        $clientID = $_SESSION['u_id'];

        $total = 0;

        $getPriceTotal = "SELECT quantity, product_id FROM public_cart WHERE user_id='$clientID'";
        $run_total = mysqli_query($con, $getPriceTotal);   
        $savedItems = mysqli_fetch_assoc($run_total);

        $quantity = $savedItems['quantity'];
        $productID = $savedItems['product_id'];


        $productQuery = "SELECT product_price FROM public_products WHERE product_id='$productID'";
        $run_product = mysqli_query($con, $productQuery);
        $product_search_price = mysqli_fetch_assoc($run_product);

        while($product_search_price = mysqli_fetch_assoc($run_product)) {

            $product_price = $product_search_price['product_price'];

            $sum = $product_price * $quantity;
            $total += $sum;

        }

         echo number_format($total, 2, '.', '');

    } else {

        echo "0";
    } 

}

But from what I see... I’m not even getting it, because it appears different values or super higher...

The code is simply counting from a product. Even making a while.

Answer 1

Apparently his while is positioned incorrectly, whereas you have n records in the table public_cart but each record has only one product.

This way makes more sense:

function total_price($con) {

    if(isset($_SESSION['u_email'])) {

        $clientID = $_SESSION['u_id'];

        $total = 0;

        $getPriceTotal = "SELECT quantity, product_id FROM public_cart WHERE user_id='$clientID'";
        $run_total = mysqli_query($con, $getPriceTotal);   

        while ($savedItems = mysqli_fetch_assoc($run_total)) {

            $quantity = $savedItems['quantity'];
            $productID = $savedItems['product_id'];

            $productQuery = "SELECT product_price FROM public_products WHERE product_id='$productID'";
            $run_product = mysqli_query($con, $productQuery);
            $product_search_price = mysqli_fetch_assoc($run_product);

            $product_price = $product_search_price['product_price'];

            $sum = $product_price * $quantity;
            $total += $sum;

        }

        echo number_format($total, 2, '.', '');

    } else {

        echo "0";

    }
}