mysqli不能使用多个STMT查询 [英] Can't use more than one STMT query for mysqli

问题描述

我正在尝试生成用户创建页面，并进行验证以确定该用户是否已经存在，为此，我使用下一个代码:

I'm trying to generate an users creation page and I make a validation to determine if the user already exists, for that I use the next code:

在这里，我将用户及其密码插入到USERS表中

here i insert the user and his password to the table USERS

mysqli_stmt_bind_param($guardar_usuario, 'ss', $usuario,$encriptar);
mysqli_stmt_execute($guardar_usuario);

在这里，我重复使用$ user变量来恢复创建的新id用户

here i reuse the variable $user to recover the new id user created

$consulta = "SELECT id_usuario FROM usuario WHERE usuario = '$usuario' ORDER BY fecha_registro DESC LIMIT 1";
$ultimo_registro = mysqli_prepare($connect,$consulta);
mysqli_stmt_execute($ultimo_registro);
mysqli_stmt_bind_result($ultimo_registro, $id_usuario_creado);
mysqli_stmt_fetch($ultimo_registro);

我将多余的数据插入到名为PERSONAS的表中

I insert the extra data to a table named PERSONAS

$guardar_persona = mysqli_prepare($connect, "INSERT INTO persona (id_usuario, nombre, email) VALUES (?, ?, ?)");        
mysqli_stmt_bind_param($guardar_persona, 'iss',  $ultimo_registro, $nombre_usuario, $email_usuario);            
mysqli_stmt_execute($guardar_persona);

我的问题是第二个查询无法将数据插入到表PERSONAS中，但是如果我删除了第一个查询，即将插入到表USERS中，则该查询可以完美地工作，所以我不知道是否有规则可以做到这一点吗?

My problem is that the second query, the data insert to the table PERSONAS doesn't work, but if I remove the first query which makes the insert to the table USERS, this query works perfectly, so I dont know if is there a rule to make this work?

很抱歉，如果这是一个相当新手的问题，它刚刚开始以这种方式工作，以试图防止尽可能多的数据插入.

Sorry if its a quite newbie question, just started to work this way to try to prevent as much as possible data inyection.

推荐答案

在执行准备好的语句时，mysqli默认使用无缓冲查询.这是一个非常令人困惑的行为，但是这意味着结果不会自动发送到PHP. MySQL服务器将结果存储在服务器上，并等待PHP检索它们.只要还有更多要提取的结果，您就无法执行其他查询.

When executing prepared statements mysqli uses unbuffered queries by default. This is a very confusing behaviour, but it means that the results are not sent automatically to PHP. MySQL server stores the results on the server and waits for PHP to retrieve them. As long as there is still more results to be fetched, you can't execute another query.

要解决此问题，您需要致电mysqli_stmt_store_result()或获取所有结果.

To fix this you need to call mysqli_stmt_store_result() or you need to get all results.

在您的情况下，简单的选择是添加store_result并完成操作.

In your case the simple option would be to add store_result and be done with it.

$consulta = "SELECT id_usuario FROM usuario WHERE usuario = ? ORDER BY fecha_registro DESC LIMIT 1";
$ultimo_registro = mysqli_prepare($connect,$consulta);
mysqli_stmt_bind_param($ultimo_registro, 's', $usuario);
mysqli_stmt_execute($ultimo_registro);
mysqli_stmt_store_result($ultimo_registro);
mysqli_stmt_bind_result($ultimo_registro, $id_usuario_creado);
mysqli_stmt_fetch($ultimo_registro);

但是，如果您停止直接使用mysqli函数，对您来说会容易得多.即使是一个简单的包装函数也可以帮助您避免所有此类mysqli混乱.

However, it would be much easier for you if you stop using mysqli functions directly. Even a simple wrapper function can help you avoid all this mysqli mess.

function safeQuery(mysqli $db, string $sql, array $params = []): ?array {
    $stmt = $db->prepare($sql);
    if ($params) {
        $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    }
    $stmt->execute();
    if ($result = $stmt->get_result()) {
        return $result->fetch_all(MYSQLI_BOTH);
    }
    return null;
}

然后，您可以将整个准备好的语句抽象为仅一行代码:

Then you can abstract your whole prepared statement to just one line of code:

$consulta = "SELECT id_usuario FROM usuario WHERE usuario = ? ORDER BY fecha_registro DESC LIMIT 1";
$result = safeQuery($connect, $consulta, [$usuario]);
if ($result) {
    $id_usuario_creado = $result[0]['id_usuario'];
}

您甚至可以编写第二个函数，该函数将从准备好的语句中仅获取单个值.

You can even write a second function which will fetch only a single value from the prepared statement.

function fetchSingle(mysqli $db, string $sql, array $params = []) {
    $stmt = $db->prepare($sql);
    if ($params) {
        $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    }
    $stmt->execute();
    if ($result = $stmt->get_result()) {
        return $result->fetch_row()[0];
    }
    return null;
}

当您只想获取单个值时，可以与LIMIT 1一起使用.

This can be used with LIMIT 1 when you only want to fetch a single value.

$consulta = "SELECT id_usuario FROM usuario WHERE usuario = ? ORDER BY fecha_registro DESC LIMIT 1";
$id_usuario_creado = fetchSingle($connect, $consulta, [$usuario]);

这篇关于mysqli不能使用多个STMT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！