PHP Prepare语句-在while循环内查询时出错 [英] PHP Prepare statement - getting errors having a query inside a while loop

问题描述

我正在尝试与查询一起执行查询.第一个循环显示所有客户数据，第二个循环显示该客户的订单.到目前为止我拥有的代码:

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

与此行相关:

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

在

与此行相关:

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

我不明白发生了什么.任何帮助将不胜感激.

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

推荐答案

EDIT 我的回答是错误的，我认为这些是基于PDO的语句，但是使用了mysqli. 但是fetch_all仍然可以帮助您

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);
}

->fetch_all()获取所有结果，因此查询已完成".这使您可以开始另一条语句. 希望它能起作用.

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

这篇关于PHP Prepare语句-在while循环内查询时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！