Update woes using pdo

im trying to run an update script


$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    


try {
 
$sql = "UPDATE `properties` SET
  `display` = 0
  WHERE `id` = ".$_GET['id'];
  
$stmt = $dbh->prepare($sql);


  
  $stmt->execute();


echo $sql;


//$stmt->debugDumpParamas();  
  # Affected Rows?
  echo "<p>Rows Updated:<strong>".$stmt->rowCount()."</strong></p>"; // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

which produces
UPDATE properties SET display = 0 WHERE id = 6[COLOR=#424242][FONT=Adobe Caslon Pro]Rows updated:0

The problem is that the query works in phpmyadmin, why does it not work using PHP?[/FONT][/COLOR]

Are the single quotes correct within the query? They don’t seem to be the same single quotes as the ones you use when you’re echoing any error condition. I am not sure whether that would make any difference.

Note that by just concatenating the $_GET onto the end of your SQL statement, you might be negating some of the helpful stuff in PDO - I think (but am not sure) you would be much better using bindParam(). Perhaps someone with more experience than me can comment.

You should never directly POST to query if possible. As DroopSnoot pointed out you are missing out on a great PDO feature in that you can BIND user input, which will quote and escape input as needed. The back tics for table names and fields are not needed but I understand they are added when testing/running a query in MyAdmin. Also wrapping the query in a condition making sure GET id isset is also a good idea.

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if(isset($_GET['id'])):
	try {

	$sql = "UPDATE `properties` SET
	  `display` = 0
	  WHERE `id` = :id";
	
	$stmt = $dbh->prepare($sql);
	$stmt->bindParam(":id", $_GET['id']);
	$stmt->execute();

	echo $sql;	
	
	//$stmt->debugDumpParamas();
	  # Affected Rows?
	  echo "<p>Rows Updated:<strong>".$stmt->rowCount()."</strong></p>"; // 1
	} catch(PDOException $e) {
	  echo 'Error: ' . $e->getMessage();
	}
endif;

ok, thanks…

When you run an update statement, you will only get an “affected rows > 0” if the value changes.

Example:


## ID is 6, Display is 1
[COLOR=#333333][FONT=Helvetica Neue]UPDATE `properties` SET `display` = 0 WHERE `id` = 6

## affected rows == 1

[/FONT][/COLOR]## ID is 6, Display is 0
[FONT=Helvetica Neue][COLOR=#333333]UPDATE `properties` SET `display` = 0 WHERE `id` = 6[/COLOR][/FONT]

[FONT=Helvetica Neue][COLOR=#333333]## affected rows == 0[/COLOR][/FONT]
[FONT=Helvetica Neue][COLOR=#333333]

[/COLOR][/FONT]

Unless you’re getting an error, that’s completely normal behavior as the DB won’t update a row if the values aren’t going to change. After all, there is no sense – for performance reasons – in doing a write/update if you’re not changing anything.