html - PHP Prepare statement - getting errors having a query inside a while loop

Question

I'm attempting to execute a query withing a query. The first loop displays all customer data, the second loop displays the orders for that customer. The code I have so far:

$stmt = $conn->prepare("SELECT * FROM Customers 
                                  WHERE travel_Date >= ?
                                  AND   travel_Date <= ?
                                  ".$searchOption."
                                  LIMIT ? 
                                  OFFSET ?");
        $todayDateFrom = $todayDate." 00:00:00";
        $todayDateTo = $todayDate." 23:59:59";
        $stmt->bind_param("ssii", $todayDateFrom, $todayDateTo, $limit, $offset);
        $stmt->execute();
        /* bind variables to prepared statement */
        $stmt->bind_result($customer_ID, $name, $etc);

while ($stmt->fetch()) {
    $stmt_Order = $conn->prepare("SELECT * FROM Orders 
                                  WHERE customer_ID= ?");
    $stmt_Order->bind_param("i", $customer_ID);
    $stmt_Order->execute();
    $stmt_Order->bind_result($order_ID, $order_Name);
}

The first loop worked fine for me, when I added the second query, I get the following errors:

All data must be fetched before a new statement prepare takes place in

which relates to this line:

$stmt_Order = $conn->prepare("SELECT * FROM Orders 
                              WHERE customer_ID= ?");

Call to a member function bind_param() on a non-object in

Which relates to this line:

$stmt_Order->bind_param("i", $cust_Customer_ID);

I'm not understanding what's happening. Any help would be appreciated.

Answer 1

EDIT My answer is wrong, I thought those are PDO based statements, but mysqli is used. But still fetch_all should help:

http://php.net/manual/en/mysqli-result.fetch-all.php

---

Can't try it, but this should work:

$stmt = $conn->prepare("SELECT * FROM Customers 
                              WHERE travel_Date >= ?
                              AND   travel_Date <= ?
                              ".$searchOption."
                              LIMIT ? 
                              OFFSET ?");
$todayDateFrom = $todayDate." 00:00:00";
$todayDateTo = $todayDate." 23:59:59";
$stmt->bind_param("ssii", $todayDateFrom, $todayDateTo, $limit, $offset);
$stmt->execute();
/* bind variables to prepared statement */
$stmt->bind_result($customer_ID, $name, $etc);

foreach ($stmt->fetch_all() as $customer) {
    $stmt_Order = $conn->prepare("SELECT * FROM Orders 
                              WHERE customer_ID= ?");
    $stmt_Order->bind_param("i", $customer_ID);
    $stmt_Order->execute();
    $stmt_Order->bind_result($order_ID, $order_Name);
}

The ->fetch_all() fetches all results, so the query is "finished". This allows you to start another statement. Hope it works.