Can't see why this prepared statement UPDATE fails

Beginners - Learning PHP
#1

I have been converting the MySQL statements in a few files to use prepared statements using MySQLi.

I have one file where an UPDATE statement fails, even though other INSERT and SELECT statements in the same file work correctly:

 if ($type == "inv") {
     // add details of email to waiting list record
     $comment = "Invitation to " . $class . " emailed on " . date("d/m/Y");
     error_log("About to update record " . $wl_id . " with: " . $comment);
     $sql = "UPDATE `waitlist` SET wl_changes = CONCAT(wl_changes,?) where  wl_ID = ?";
     $stmt = $conn->prepare($sql);
     if (
             $stmt &&
             $stmt->bind_param('si', $comment, $wl_id) &&
             $stmt->execute()
     )
         if ($stmt->error) {
             error_log("FAILURE!!! " . $stmt->error);
         } else {
             error_log("Updated {$stmt->affected_rows} rows");
         }
 
     $stmt->close();
 } else {
     error_log($type . " Therefore no update");
 }

The PHP log returns:

[13-May-2020 16:20:31 UTC] About to update record 21 with: Invitation to Sat 10am emailed on 13/05/2020

[13-May-2020 16:20:31 UTC] Updated 0 rows

I know that the record with wl_ID of 21 exists because earlier in the file I use it to select details from the record.

Can anyone tell me why it isn’t updating record 21?

Thanks

#2

What does var_dump($wl_id); show?

Next, if you use exceptions for database statement errors and in most cases let php catch and handle the exception, you can eliminate all the error handling logic you have now. Your code only has to deal with error free execution, since execution transfers to the nearest exception handling (php in this case) upon an error.

Lastly, if you switch to the much simpler and more consistent PDO extension, you will have less code to write or to convert.

#3

Sorry have been out for my daily “lockdown” exercise :smiley:

The var_dump shows $wl_id as a 2 character string.

If I use a SELECT statement immediately before the UPDATE statement to check if it works ok using $wl as the bound parameter then that works ok :

$sql =“SELECT surname FROM waitlist WHERE wl_ID = ?”
$stmt->bind_param(‘i’, $wl_id)

With regards to PDO, I fully understand where you are coming from, but I am trying to get to grips with, what are to me, new technologies PHP and JavaScript/JQuery, so am taking “baby steps”…Also I have inherited a project that has been more than 50% converted to this style so have decided to stick with it for the remainder of this project.

#4

Just found out what the problem, probably, is.

The field wl_changes was a NULL. I went into the database and edited it to a string value, and then the update worked ok. Now just need to detect if the field contains a NULL and if so just update field with value rather than trying to concatenate a string to NULL

#5

The simplest would be to set it to an empty string when the row is inserted and to update any existing null to be an empty string.

Actually, concatenating values into a field to keep a record of changes went out of style a long time ago. You should instead have a wl_status table that you insert a new row into with any - who, what, when, where, and why information about each change in status, related back to the row in the waitlist table through the wl_ID value.

Here’s what the PDO version, also using exceptions for errors, of the posted code would be -

if ($type == "inv") {
	// add details of email to waiting list record
	$comment = "Invitation to " . $class . " emailed on " . date("d/m/Y");

	error_log("About to update record " . $wl_id . " with: " . $comment);

	$sql = "UPDATE `waitlist` SET wl_changes = CONCAT(wl_changes,?) where  wl_ID = ?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([$comment, $wl_id]);

	error_log("Updated {$stmt->rowCount()} rows");

} else {
	error_log($type . " Therefore no update");
}
#6

Thanks for your help

I used "…SET wl_changes = CONCAT(IFNULL(wl_changes,’’),?) " and that seems to have sorted it.
I can’t change the Table structure at the moment because it is also used by an existing Delphi application that will not be phased out yet awhile.

I have some related work to start in the future and will use that as a way of starting PDO.

Again thanks for your input

Sponsor our Newsletter | Privacy Policy | Terms of Service