dhad18 dhad18 - 4 months ago 16
MySQL Question

mysqli update query failing

I am trying to update an internal database on post to a third party platform. I have successfully updated the same database for another variable but when I add this variable in it all breaks. Here is the mysqli update statement:

@$mysqli->query("UPDATE `tableName` SET `columnID`=" . (int)$columnID . ',`columnID2`=' . (int)$columnID2 . ',`columnID3`=' . (int)$columnID3 . ',`columnID4`=' . $columnID4 . ' WHERE `id`=' . (int)$id . ' LIMIT 1');


for some reason it will work if I take out columnID4 but when I have columnID4 included in the statement it doesn't work.

I have done a var dump on $columnID4 and it returns the value I am expecting every time.

when I did

echo $mysqli->error;


I got a message saying "Array" unknown array in 'field set'.

Here is what my code looks like where I am setting the field.

if ($query->num_rows == 1) {
$columnID4 = $query->fetch_assoc();
} else {
$columnID4 = 0;
}

Answer

It's always difficult to debug SQL by staring at PHP code that builds the SQL string. Instead, look at the SQL after it has been built!

Tip: never use the @ error-suppression operator. It's better to fix errors, not suppress them.

I don't like the habit of PHP developers to use string concatenation. It makes the code hard to read, and it's easy to make mistakes. Here are two different ways you could make the code more robust:

Solution 1: Use sprintf() to interpolate the variables into your SQL string. This allows you to coerce the variables to integers or decimals by using %d and %f respectively.

$sql = sprintf("UPDATE `tableName` SET `columnID`=%d, `columnID2`=%d, 
  `columnID3`=%d, `columnID4`=%f WHERE `id`=%d LIMIT 1",
  $columnID, $columnID2, $columnID3, $columnID4, $id);
error_log("sql = $sql");
$mysqli->query($sql);

Solution 2: Use bound parameters with mysqli_stmt::bind_param(). This is a recommended practice when using SQL. The variables never touch your SQL code, there's no way to break the query syntax.

$sql = "UPDATE `tableName` SET `columnID`=?, `columnID2`=?, 
  `columnID3`=?, `columnID4`=? WHERE `id`=? LIMIT 1";
error_log("sql = $sql");
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("iiidi", $columnID, $columnID2, $columnID3, $columnID4, $id);
$stmt->execute();