在另一个while循环内的while循环内执行mysqli准备好的语句 [英] Executing mysqli prepared statment within while loop that's within another while loop

问题描述

我正在努力实现以下目标:

I am trying to achieve the following:

User 1:
     - Alert 1 Email
     - Alert 2 Email
User 2:
     - Alert 1 Email
     - Alert 2 Email

我试图在另一个运行 mysqli 准备好的语句的 while 循环中使用一个 while 循环来完成此操作，但我无法让它工作.

I'm trying to accomplish this with a while loop inside another while loop that is running a mysqli prepared statement, but I cannot get it to work.

代码:

$stmtAdd = $conn->prepare("INSERT INTO Data (row1, row2, row3, row4) VALUES ('".$row1."', '".$row2."', '".$row3."', '".$row4."')");
$stmtAdd->execute();
$stmtAdd->close();

$stmtUsers = $conn->prepare("SELECT username, setting1 FROM Users");
$stmtUsers->store_result();
if ($stmtUsers->execute() === FALSE) {
    die("Could not execute prepared statement");
} else {
    $stmtUsers->bind_result($user, $setting1);
    while ($stmtUsers->fetch()) {
        /* Check if each user has setting 1 disabled */
        if ($setting1 == '0'){
            /* Check if any alerts exist for each user */
            $stmtUsersAlerts = $conn->prepare("SELECT name, filter, email FROM Alerts WHERE user='".$user."' AND type='1'");
            $stmtUsersAlerts->store_result();
            $stmtUsersAlerts->bind_result($name, $filter, $email);
            while ($stmtUsersAlerts->fetch()) {
                /* Send email */
            }
            $stmtUsersAlerts->close();
        }
    }
    $stmtUsers->close();
}

推荐答案

stmt->store_result() 不能在 stmt->execute() 之前运行.

stmt->store_result() can not run before stmt->execute().

$stmtUsers = $conn->prepare("SELECT username, setting1 FROM Users");
if ($stmtUsers->execute() === FALSE) {
    die("Could not execute prepared statement");
} else {
    $stmtUsers->store_result();                // After execute()
    $stmtUsers->bind_result($user, $setting1);
    while ($stmtUsers->fetch()) {
        /* Check if each user has setting 1 disabled */
        if ($setting1 == '0'){
            /* Check if any alerts exist for each user */
            $stmtUsersAlerts = $conn->prepare("SELECT name, filter, email FROM Alerts WHERE user='".$user."' AND type='1'");
            $stmtUsersAlerts->execute();        // This line was missing
            $stmtUsersAlerts->store_result();
            $stmtUsersAlerts->bind_result($name, $filter, $email);
            while ($stmtUsersAlerts->fetch()) {
                /* Send email */
            }
            $stmtUsersAlerts->close();
        }
    }
    $stmtUsers->close();
}

这篇关于在另一个while循环内的while循环内执行mysqli准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！