PDO Prepared Update Statement Not Updating DB

PHP
1

I have jquery posting some data to this php file, the dev tools show the post data correctly. My insert delete statements execute correctly but this prepared statement does not. What have I done incorrectly.

Thanks in advance, James


<?php

	$dbtype     = "mysql";
	$dbhost     = "localhost";
	$dbname     = "dbname";
	$dbuser     = "dbuser";
	$dbpass     = "dbpass";

try {
	
	// database connection
 	$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
	$conn -> exec('SET CHARACTER SET utf8');
		
} catch(PDOException $e) {
	echo 'There was a problem. Caught exception: ',  $e->getMessage(), "\
";
}

$date = date('Y-m-d H:i:s');

if (isset($_POST['id'])) {

     $sql = "UPDATE catalogues "
    .$sql = "SET title = :title,
            manufacturer = :manufacturer,
            modified = :modified,
            category_id = :category_id,
            subcategory_id = :subcategory_id "
    .$sql = "WHERE id = :id";

    $query = $conn->prepare($sql);
    $query->bindParam(":id",$_POST['id']);
    $query->bindParam(":title",$_POST['title']);
    $query->bindParam(":manufacturer",$_POST['manufacturer']);
    $query->bindParam(":category_id",$_POST['category_id']);
    $query->bindParam(":modified",$date);
    $query->bindParam(":subcategory_id",$_POST['subcategory_id']);

    if ($query->execute()){
      echo $_POST['title']." updated successfully.";
    } else {
      echo "Crap, something went wrong.";
    }
}
?>
2

And how about like this?

$sql = "UPDATE catalogues
SET title = :title,
manufacturer = :manufacturer,
modified = :modified,
category_id = :category_id,
subcategory_id = :subcategory_id
WHERE id = :id";
3

Well, for starters I’d move your execute call outside of the if conditions, you should be checking $query->rowCount() = 1 . rowCount lets you see the number of rows affected by the query and since you seem to only ever need one it’s an easy place to check for an issue with the response, you could even throw in an else to respond to it being greater than 1.

4

This part is wrong:


$sql = "UPDATE catalogues " 
    .$sql = "SET title = :title, 
            manufacturer = :manufacturer,             
            modified = :modified, 
            category_id = :category_id, 
            subcategory_id = :subcategory_id "             
    .$sql = "WHERE id = :id";

I’m not sure what it’ll do, but I’m sure it doesn’t do what you expect it does.
I think you meant


$sql = "UPDATE catalogues "
    . "SET title = :title, 
            manufacturer = :manufacturer,             
            modified = :modified, 
            category_id = :category_id, 
            subcategory_id = :subcategory_id "             
    . "WHERE id = :id";

Even better would be (my opinion)


$sql = "
UPDATE
   catalogues
SET
   title = :title
 , manufacturer = :manufacturer
 , modified = :modified
 , category_id = :category_id
 , subcategory_id = :subcategory_id
WHERE
   id = :id
";
5

Actually that looks like he’s trying to append the SQL to the query and mistook what the manner that’s done in was, instead replicating something that would be done inside a function, like echo, but didn’t realise echo is a function because it’s generally appended to non-parenthetically.


$sql = "UPDATE catalogues "
    .$sql = "SET title = :title,
            manufacturer = :manufacturer,
            modified = :modified,
            category_id = :category_id,
            subcategory_id = :subcategory_id "
    .$sql = "WHERE id = :id";

The way the code would read as written while executing would probably be something like this:
$sql = "UPDATE catalogues " “” =[error] "SET title = :title, manufacturer = :manufacturer, modified = :modified, category_id = :category_id, subcategory_id = :subcategory_id " . “” =[error] “WHERE id = :id”;

What it should be is this:


$sql = "UPDATE catalogues ";
$sql .= "SET title = :title,
            manufacturer = :manufacturer,
            modified = :modified,
            category_id = :category_id,
            subcategory_id = :subcategory_id ";
$sql .= "WHERE id = :id";

.= is the concatenation assignment operator in PHP, it essentially means append to end and it shorthand for $variable = $variable . “random string”; as . is the concatenation operator for strings. If you know javascript think of . as + and .= as Add to end of variable (string).

That all being said, you probably shouldn’t be attempting to concatenate the string unless it’s via conditionals or assigned variables. Since in this case the string in the variable $sql isn’t being modified by anything other than binds it should simply just be being assigned in one call to avoid issues like this (so like post #4 above).

6

I thought that too when I saw it, but I tested it and it actually still does concatenate the strings correctly (even though it’s not the usual way to do it).

7

Probably because it’s all one long string and the $sql variables are as of yet undefined while it’s executing so it’s being treated as . =

At least that’d be my guess since it’s completely lacking of line ends.