Although the loop only finds the last iteration

Question

So, for school I am trying to create an online store (this is a project), and for the basket I use a database where I get information based on the user and print it. HOWEVER, my while loop only finds the last element, not the other two. Now I am sure that the old data will be overwritten so that it will not be printed, but I do not know how to fix it.

this is what i got so far:

<?php
if(isset($_SESSION['userid'])){

    $db = new PDO('mysql:host=localhost;dbname=ismsite', 'root', 'e-scooter');
    $result = $db->prepare("SELECT * FROM cart where user_id=:userid ORDER BY cart_id DESC LIMIT 3 ");
    $result->bindParam(':userid', $_SESSION['userid']);
    $result->execute();
    $info = array();

    while($row = $result->fetch(PDO::FETCH_ASSOC)){

        $pid = $row['product_id'];
        $quantity = $row['quantity'];

        $result = $db->prepare("SELECT * FROM Products WHERE productID=:pid");
        $result->bindParam(':pid', $pid);
        $result->execute();

        while($row = $result->fetch(PDO::FETCH_ASSOC)){
            echo $row['naam'];
        }

    }
}
else{

    echo "Je moet inloggen om te kunnen winkelen.";

}
?>

Can anyone help me on this?

Answer 1

You must use SQL JOIN .

Try it like this:

 $db = new PDO('mysql:host=localhost;dbname=ismsite', 'root', 'e-scooter');
             $sql = "SELECT cart.*,Products.* FROM cart 
                    LEFT JOIN Products
                    ON cart.product_id = Products.productID
                    where cart.user_id=:userid ORDER BY cart.cart_id DESC LIMIT 3"; 
                $result = $db->prepare($sql);
                $result->bindParam(':userid', $_SESSION['userid']);
                $result->execute();                   

                while($row = $result->fetch(PDO::FETCH_ASSOC)){
                   echo $row['naam'];
                }

Answer 2

$result,$row. $row $result. , While .

     while($row = $result->fetch(PDO::FETCH_ASSOC)){
                                    $pid = $row['product_id'];
                                    $quantity = $row['quantity'];

                                    $result1 = $db->prepare("SELECT * FROM Products WHERE productID=:pid");
                                    $result1->bindParam(':pid', $pid);
                                    $result1->execute();

                                    while($row1 = $result1->fetch(PDO::FETCH_ASSOC)){
                                        echo $row1['naam'];
                                    }
}

Answer 3

You have a conflict with your variable names. Try the following:

if(isset($_SESSION['userid'])){
    $db = new PDO('mysql:host=localhost;dbname=ismsite', 'root', 'e-scooter');
    $cart = $db->prepare("SELECT * FROM cart where user_id=:userid ORDER BY cart_id DESC LIMIT 3 ");
    $cart->bindParam(':userid', $_SESSION['userid']);
    $cart->execute();
    $info = array();

    while($row = $cart->fetch(PDO::FETCH_ASSOC)){
        $pid = $row['product_id'];
        $quantity = $row['quantity'];
        $products = $db->prepare("SELECT * FROM Products WHERE productID=:pid");
        $products->bindParam(':pid', $pid);
        $products->execute();

        while($product = $products->fetch(PDO::FETCH_ASSOC)){
              echo $product['naam'];
        }
}

Answer 4

Use a for loop instead of a while loop

for($i = 0, $c = count($result->fetch(PDO::FETCH_ASSOC)); $i < $c; $i++){
    //Do stuff here
}