数据源在第二个while循环中不可用

Question

I have the following php code:

$user_id = $user["id_user_key"];
$stmt = $db->prepare("CALL spGetUserProducts(?)");
$stmt->bind_param('i', $user_id);
$stmt->execute();
$result = $stmt->get_result();

$data = array();

while($row = $result->fetch_assoc()) {
    $row_array = array();
    $row_array["id"] = $row["id"];
    $row_array["pname"] = $row["pname"];
    $row_array["picon"] = $row["picon"];
    $row_array["menuItems"] = array();
    $product = $row["id"];
    //loop
    $result_opt = $db->query("CALL spGetUserProductViews($user_id, $product)");
    while ($opt_fet = $result_opt->fetch_assoc()) {
        $row_array["menuItems"][] = array(
            "id" => $opt_fet["id"],
            "vname" => $opt_fet["vname"],
            "isheader" => $opt_fet["isheader"]
        );
    }
    array_push($data, $row_array);
}

$stmt->close();

echo json_encode($data);

The first loop can get a hold of $db, in other words: the first prepared statement is being excecuted and gives me results. The second one:

$result_opt = $db->query("CALL spGetUserProductViews($user_id, $product)");

gives me false. When I try this statement outside the loop, it does work.

Any thoughts ont his?

Answer 1

I found out that mysqli can't handle two simultaneous queries because mysqli uses unbuffered queries by default. Now, I could have dived into this (for example make use of $stmt->store-result()), but I also realized that I would like to keep the load on my database to a minimum.

My solution:

$data = array();
$user_id = $user["id_user_key"];

//menus -> products
$stmt = $db->prepare("CALL spGetUserProducts(?)");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();

$menus = array();
while($row = $result->fetch_assoc()) {
    $menus[] = $row;
}

$stmt->close();

//items -> views
$stmt = $db->prepare("CALL spGetUserProductViews(?)");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();

$items = array();
while($row = $result->fetch_assoc()) {
    $items[] = $row;
}

$stmt->close();

//generate object
//loop menus
foreach($menus as $m){
    $row_array = array();
    $row_array["id"] = $m["id"];
    $row_array["pname"] = $m["pname"];
    $row_array["picon"] = $m["picon"];
    $row_array["menuItems"] = array();
    //loop items
    foreach($items as $i) {
        if($m["id"] == $i["id_product"]) {
            $row_array["menuItems"][] = array(
                "id" => $i["id"],
                "vname" => $i["vname"],
                "isheader" => $i["isheader"]
            );
        }
    }
    array_push($data, $row_array);
}

echo json_encode($data);

So now I first generate arrays out of the two objects. then I do a foreach over the menus and then over the items. When the $menu["id"] equals $items["id_product"] then the array with items for that specific menu is being generated.

EDIT

After the data has been pulled from the database I first have to do a check wether or not the array contains data:

if(!empty($menus) && !empty($items)) {
    foreach ($menus as $m) {
        $row_array = array();
        $row_array["id"] = $m["id"];
        $row_array["pname"] = $m["pname"];
        $row_array["picon"] = $m["picon"];
        $row_array["menuItems"] = array();
        //loop items
        foreach ($items as $i) {
            if ($m["id"] == $i["id_product"]) {
                $row_array["menuItems"][] = array(
                    "id" => $i["id"],
                    "vname" => $i["vname"],
                    "isheader" => $i["isheader"]
                );
            }
        }
        array_push($data, $row_array);
    }
}