MYSQL: Why this update query won't work?

Question

I'm trying this sql query, and it won't work:

<form name="modificarUsuario" action="" method="POST">
<h2>Modificación de Datos Personales</h2>
Username: <input type="text" name="username" />  
Nombre Completo: <input type="text" name="nombre" />  
Email: <input type="text" name="email" />  
Clave: <input type="text" name="pass" />  
<input type="submit" class="button" name="modificarDatos" value="Modificar datos">
<br>
</form>

$usuario = $_POST['username'];
$nombre = $_POST['nombre'];
$email = $_POST['email'];
$clave = $_POST['pass'];
$uid = 1;
$consulta = mysqli_prepare($conectar,
                "UPDATE usuario 
                 SET username = ?, pass = ?, name = ?, email = ?) 
                 WHERE uid = '".$uid."'
                ");
if ($consulta) {
    mysqli_stmt_bind_param( $consulta, 'ssss', $usuario, $clave, $nombre, $email);
    mysqli_stmt_execute($consulta);
    echo 'Se guardaron los cambios';
}
else {echo 'Hubo un error! El cambio no se guardó!';}
mysqli_stmt_close($consulta); //here's line 52

The error is:

PHP Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in ...

The database has an "usuario" table, with an "uid" field, which is an integer. All fields are correctly spelled and in the right order: uid, username, pass, name, email

I've read this post and this other post, and they both have interesting information about what could have been wrong. But I've checked and everything seems ok.

Why are you using bound parameters for `username`, `pass`, `name` and `email`, but string substitution for `uid`? — Matt Gibson, Jan 12 '15 at 22:36
I've harcoded it. There's only one user in the database, the admin. Thought it was easier to test that the code worked as expected. — Rosamunda, Jan 12 '15 at 22:37
And can you show us a small, complete example that demonstrates the problem? I can't quite see from the code you've posted how you'd have an error preparing the statement—as seems to be happening—but not get your error message out. (Also: it may help if you output `$mysqli->error` if the prepare fails.) Leaving things out of your code, like the php tags, may not be helping us understand the whole situation. It's often a hint that other important things are missing. — Matt Gibson, Jan 12 '15 at 22:43
@Rosamunda You really should bind *all* parameters just to be on the safe side. This is how you create nasty bugs by mistake: Step one is always innocent enough. — tadman, Jan 12 '15 at 22:49

score 4 · Accepted Answer · answered Jan 12 '15 at 22:47

4

You've got a spurious close paren character in your SQL text (the string containing the UPDATE statement). And that's a MySQL syntax violation.

, email = ?) 
           ^

If the mysqli_prepare fails (i.e. returns FALSE), you should retrieve the error message with mysqli_error($connectar), that would let you see the syntax error.

 else {
    echo 'Hubo un error! El cambio no se guardó!';
    echo mysqli_error($conectar);
 }

The mysqli_stmt_close only works if there's a valid statement handle, so that call should be moved up into the "then" block, so it only gets executed when the prepare returned a statement handle.

answered Jan 12 '15 at 22:47

spencer7593

106,611
15
112
140

You were correct! The issue was that ) that didn't belonged! THANKS!! – Rosamunda Jan 13 '15 at 00:06
2

@Rosamunda: At the risk of being redundant, I want to re-emphasize... use **`mysqli_error`** to retrieve the MySQL error. That error would have indicated a MySQL syntax problem, narrowing down where the actual problem was. – spencer7593 Jan 13 '15 at 00:28

Félix Adriyel Gagnon-Grenier · Answer 2 · 2015-01-12T22:49:14.590

0

from the manual:

mysqli_prepare() returns a statement object or FALSE if an error occurred.

When your prepare statement fails, it will return a boolean, meaning that $consulta will not be a statement but a boolean. booleans can't be closed, and mysqli_stmt_close wants a statement. So if you pass your failed prepared statement to mysqli_stmt_close, you get the error you posted.

so put your close only if it succeeded

if ($consulta) {
    mysqli_stmt_bind_param( $consulta, 'ssss', $usuario, $clave, $nombre, $email);
    mysqli_stmt_execute($consulta);
    echo 'Se guardaron los cambios';
    mysqli_stmt_close($consulta);
}
else {echo 'Hubo un error! El cambio no se guardó!';}

edited Jan 12 '15 at 22:49

answered Jan 12 '15 at 22:38

Félix Adriyel Gagnon-Grenier

8,422
10
52
65

Yes, I know that it fails, but why? The same statement I've tested it in other place and it worked just fine. I thought that it could be because some silly *grammar* error in my code... – Rosamunda Jan 12 '15 at 22:39
`$conectar` is not set in the code you posted. That could be the issue. – Arjan Jan 12 '15 at 22:40
the error in your question is related to the `mysql_stmt_close`, not the prepare. if the prepare fails, that is another question @Rosamunda – Félix Adriyel Gagnon-Grenier Jan 12 '15 at 22:41
$conectar is included, in a different file, used in several other places (that's why I know that it works). If I put mysqli_stmt_close() only when it succeeds, tehre's no error, but it won't update either. – Rosamunda Jan 12 '15 at 22:43
@Rosamunda then why show the error of the `mysql_stmt_close` if it's about the `mysqli_prepare` that you want to ask? – Félix Adriyel Gagnon-Grenier Jan 12 '15 at 22:44
@FélixGagnon-Grenier that's only partly correct, `mysql_stmt_close` fails because the prepare fails (and there's no check to see if the prepare was successful), so I would say a proper solution not only adds error handling but also fixes the prepare issue. – Arjan Jan 12 '15 at 22:46
@Arjan yes, the `mysql_stmt_close` fails because the prepare fails, that's exactly what I'm saying... – Félix Adriyel Gagnon-Grenier Jan 12 '15 at 22:48

MYSQL: Why this update query won't work?

2 Answers2