How to use mysqli_stmt_affected_rows() with prepared statements?

Question

After changing this query to be user with binding parameters, It won't work anymore.

    $conectar2 = mysqli_connect(HOST, USER, PASS, DATABASE);

    $buscarUsuarioExistente = " SELECT userID, userEmail 
                                FROM usuarios 
                                WHERE userEmail =?
                              ";
                    $usuarioExiste = mysqli_prepare($conectar2,$buscarUsuarioExistente);
                    mysqli_stmt_bind_param($usuarioExiste, 's', $email);
                    mysqli_stmt_execute($usuarioExiste);
                    mysqli_stmt_close($usuarioExiste);

//SI EL MAIL QUE PONE EL USUARIO YA EXISTE EN BASE
    if (mysqli_stmt_affected_rows($usuarioExiste) != 0) {
            $usuario = mysqli_fetch_assoc($usuarioExiste);
            //como nos devuelve un array, extraemos el primer elemento como string. El array contiene sólo un elemento.
            $userID = array_shift($usuario);
            //si el usuario existe en base, no lo generamos, sino que le agregamos el curso que seleccionó y le asignamos el rol "noAutorizado" hasta no verificar el pago
                    $asignarRol = "INSERT INTO rolesUsuarios 
                                    (userID, nombreRol) 
                                   VALUES 
                                    (?, ?)
                                   ";
                    $noAutorizado = 'noAutorizado';             
                    $asignarRolenBase = mysqli_prepare($conectar2,$asignarRol);
                    mysqli_stmt_bind_param($asignarRolenBase, 'ss', $userID, $noAutorizado);
                    mysqli_stmt_execute($asignarRolenBase);

                    if ($asignarRolenBase) {
                        echo 'Estado "pendiente" del usuario generado.<br>';
                        } 
                        else { 
                            echo 'Error al asignar estado al usuario'.mysqli_error($conectar2).'<br>';
                        }
                    mysqli_stmt_close($asignarRolenBase);

    }

The problem seems to be with this: mysqli_stmt_affected_rows($usuarioExiste) != 0 (this is line 45)

Because I get this error:

Warning: mysqli_stmt_affected_rows(): Couldn't fetch mysqli_stmt in /home/public_html/inscripcionUsuario.php on line 45

UPDATE:

I've noticed that I do close it in the line before I call it (thanks Qirel), but moving further down that line, so I don't close it before, gives me this error:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, object given

So, with the update: $conectar2 = mysqli_connect(HOST, USER, PASS, DATABASE);

    $buscarUsuarioExistente = " SELECT userID, userEmail 
                                FROM usuarios 
                                WHERE userEmail =?
                              ";
                    $usuarioExiste = mysqli_prepare($conectar2,$buscarUsuarioExistente);
                    mysqli_stmt_bind_param($usuarioExiste, 's', $email);
                    mysqli_stmt_execute($usuarioExiste);

//SI EL MAIL QUE PONE EL USUARIO YA EXISTE EN BASE
    if (mysqli_stmt_affected_rows($usuarioExiste) != 0) {
            $usuario = mysqli_fetch_assoc($usuarioExiste);
            //como nos devuelve un array, extraemos el primer elemento como string. El array contiene sólo un elemento.
            $userID = array_shift($usuario);
            //si el usuario existe en base, no lo generamos, sino que le agregamos el curso que seleccionó y le asignamos el rol "noAutorizado" hasta no verificar el pago
                    $asignarRol = "INSERT INTO rolesUsuarios 
                                    (userID, nombreRol) 
                                   VALUES 
                                    (?, ?)
                                   ";
                    $noAutorizado = 'noAutorizado';             
                    $asignarRolenBase = mysqli_prepare($conectar2,$asignarRol);
                    mysqli_stmt_bind_param($asignarRolenBase, 'ss', $userID, $noAutorizado);
                    mysqli_stmt_execute($asignarRolenBase);

                    if ($asignarRolenBase) {
                        echo 'Estado "pendiente" del usuario generado.<br>';
                        } 
                        else { 
                            echo 'Error al asignar estado al usuario'.mysqli_error($conectar2).'<br>';
                        }
                    mysqli_stmt_close($asignarRolenBase);
                    mysqli_stmt_close($usuarioExiste);

    }

Update:

I've tried with mysqli_stmt_num_rows() (if (mysqli_stmt_num_rows($usuarioExiste) != 0))

Instead and I've got this error:

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given

It might be because you close it the line before. – Qirel Aug 10 '16 at 15:46 — Qirel, Aug 10 '16 at 15:46

score 0 · Answer 1 · edited May 23 '17 at 12:14

Your problem is here

mysqli_stmt_close($usuarioExiste);

You're closing the mysqli_stmt object which does

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle

As to the other question, you're missing a step

 $usuario = mysqli_fetch_assoc($usuarioExiste);

That won't work because you have a mysqli_stmt object, and mysqli_fetch_assoc expects a mysqli_result object. This gets a bit tricky because you might not have the MySQL Native Driver installed (that's a server config). If you do, you can fix it like this

$result = mysqli_stmt_get_result($usuarioExiste);
$usuario = mysqli_fetch_assoc($result);

If that function is undefined you'll have to use the much clunkier mysqli_stmt_bind_result and assign variables. The examples here require a lot of recoding on your part, but you can see some examples over at Example of how to use bind_result vs get_result

I know that the database exists and the credetials are ok, but this error now could be because it cannot connect? `Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean` I also get a mysql error `Commands out of sync; you can't run this command now` — Rosamunda, Aug 10 '16 at 16:46
I think you might need to ask a new question at this point. We're way beyond the original scope — Machavity, Aug 10 '16 at 16:58

How to use mysqli_stmt_affected_rows() with prepared statements?

UPDATE:

Update:

1 Answers1