PHP Prepare statement - getting errors having a qu

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

I think u can replace this code

   while ($stmt->fetch()) {

and instead u can write

   while ($row = $stmt->fetch()) {

Answer 2

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.

Answer 3

If you talk to MySQL, you can only ask one question (query) at a time. And you must wait for the full response, before you can ask the next one. In your case, the first query returns more than one response - and the second query is run, before all rows have been collected.

In PHP, you have functions for buffered and unbuffered SQL requests ($stmt->store_result() in your case). Buffering means: The whole response is collected from MySQL and buffered by PHP. Check the PHP manual for details on Buffered and Unbuffered queries

So, whenever you need a query within a query, you have to buffer the outer one. When using mysqli_stmt, check mysqli_stmt::store_result(). There is also the option to collect all rows into an array, first, and the go through this array to do the secondary queries (see answer from theiNaD).

Note: Buffering the results may require a lot of memory, when working with large data sets. The perfect SQL solution is a LEFT JOIN. This also will save you from running hunderts of queries, which is quite slow.

$stmt = $conn->prepare("SELECT * FROM Customers c
                              LEFT JOIN Orders o ON (o.customer_ID = c.id)
                              WHERE travel_Date >= ?
                              AND   travel_Date <= ?
                              ".$searchOption."
                              LIMIT ? 
                              OFFSET ?");

BurninLeo