Can't use more than one STMT query for mysqli

Question

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:

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

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

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

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.

You forgot to parameterize `$usuario`. You should fix it as soon as possible. — Dharman, Jun 30 '20 at 22:08
Can you please not use CAPS and edit them? It makes it look like you're yelling at us. — Funk Forty Niner, Jun 30 '20 at 22:24
@Dharman what do you mean with I didn't parameterize $usuario? — adel azeroth, Jul 01 '20 at 12:01
You should not put PHP variables in SQL string. You should use `bind_param` to bind the data. See how I did it in the answer. — Dharman, Jul 01 '20 at 12:02
yeah, I added it there because it wasn't working properly like in the code you shared, but now its working, thanks very much — adel azeroth, Jul 01 '20 at 14:48

score 3 · Accepted Answer · answered Jun 30 '20 at 22:18

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.

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

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

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

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

The alternative you are offering me its to work with objects, isn't it? Im not quite familiarized to work that way, that's why I choosed to work with procedural instructions — adel azeroth, Jul 01 '20 at 12:04
Yes. It is OOP. It is much easier than procedural and less error-prone. I strongly recommend it. — Dharman, Jul 01 '20 at 12:04
@adelazeroth it's really simple. You just write `$mysqli->method()` instead of `mysqli_method($mysqi)` just a slightly different syntax. — Your Common Sense, Jul 01 '20 at 12:28
@adelazeroth Do you have error reporting switched on? What errors do you get? [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Jul 01 '20 at 14:43

Can't use more than one STMT query for mysqli

1 Answers1