PDO execute() - update query executes but nothing happens
Posted: Fri May 29, 2009 11:47 am
Hello everyone
I do not know if I'm stupid, or crazy. I have a function that executes a select query to a db and gets some parameter. Then I do some operations with files where I get some new value. Then I run an update query on the same registry to insert a new parameter.
The thing is that both queries throw no error, the code after the second one executes well but the table stays without the update, keeping the initial value...
Here is the code. Just to make it more understandable: it's a image files indexation resort, and the parameter I update is the primary image property that one of the images in the directory can hold. So if some file was "primary" and its index changes from for ex. 5 to 2 - I have to update the DB so the property would appear correctly in the web forms.
I've done all this thousands of times. There are dozens of similar queries and methods altering the DB in this project, all run with no problems. The method resortImages() logging and testing showed that all files are resorted well, the $prevPriImg and $postPriImg change their values accordingly, and by the moment I run updateDBParameter() to apply the update the $postPriImg and the rest of parameters have the correct value. The only problem in all this - the table never gets updated. So the problem is in the updateDBParameter() method, maybe some PDO connection problems, I do not know... The most unexplainable thing is that the $this->log->lwrite("updated"); line executes, and the algorithm never enters catch part.
Also, if I intentionally put some error into the update query the PDOException writes the related error as it should. And when I run the function in standalone (just using some simple php) it works perfectly too.
This is the line where the divine magic happens I suppose: $stmt->execute(array($value,$keyValue));
Searching in the Web I've found this: http://bugs.mysql.com/bug.php?id=36406. Though this is not entirely my case...
I am pretty desperate with this case already after spending more than a day trying to find the answer. I've posted this already in every php development forum I know with no result. And I definitely do not want to change/separate the methods.
Ex. of common usage of the method: updateDBParameter("usher_stats","idUsher","4a1dcda8ec831","idPrimaryImg","9");
Related fields: idUsher - char(16), idPrimaryImg - tinyint(1)
MySQL 5.0.45, PHP 5.2.5
Thanks in advance
I do not know if I'm stupid, or crazy. I have a function that executes a select query to a db and gets some parameter. Then I do some operations with files where I get some new value. Then I run an update query on the same registry to insert a new parameter.
The thing is that both queries throw no error, the code after the second one executes well but the table stays without the update, keeping the initial value...
Here is the code. Just to make it more understandable: it's a image files indexation resort, and the parameter I update is the primary image property that one of the images in the directory can hold. So if some file was "primary" and its index changes from for ex. 5 to 2 - I have to update the DB so the property would appear correctly in the web forms.
Code: Select all
public function getDBParameter($dbTable,$key,$keyValue,$field){
$param = NULL;
try{
$this->openDbPDO($this->p);
$stmt = $this->conn->prepare("SELECT ".$field." FROM ".$dbTable." WHERE ".$key." = ?");
$stmt->execute(array($keyValue));
if($row = $stmt->fetch()){
$param = $row[$field];
}
$this->conn = null;
}catch(PDOException $e){
$this->log->lwrite($e->getMessage());
}
return $param;
}
public function updateDBParameter($dbTable,$key,$keyValue,$field,$value){
try{
$this->openDbPDO($this->p);
$stmt = $this->conn->prepare("UPDATE ".$dbTable." SET ".$field." = ? WHERE ".$key." = ?");
$stmt->execute(array($value,$keyValue));
$this->conn = null;
$this->log->lwrite("updated"); //this line executes
}catch(PDOException $e){
$this->log->lwrite($e->getMessage()); //the function never enters here
}
}
public function resortImages($usherId){
$lib = new Repository();
$images = $lib->listUsherImages($usherId); //returns array of pairs of remaining images
$prevPriImg = NULL;
$postPriImg = NULL;
if(count($images)>0){
//getting previous primary image index
$prevPriImg = $this->getDBParameter("usher_stats","idUsher",$usherId,"idPrimaryImg");
//resorting images...
$idxs = array();
for($i=0; $i<count($images); $i=$i+2){
$idxs[$i/2] = substr($images[$i],14,1); //the 15th char in file names is the index
}
if($handle = opendir("images/data/usher/".$usherId."/")){
$k = 0;
while(false !== ($file = readdir($handle))){
if($k>1){
for($i=0; $i<count($idxs); $i++){
if(strcmp($idxs[$i],substr($file,14,1))==0){
rename("images/data/usher/".$usherId."/".$file,
"images/data/usher/".$usherId."/".substr_replace($file,"$i",14,1));
if(strcmp($prevPriImg,substr($file,14,1))==0){
$postPriImg = $i; //getting new index for primary image
$this->log->lwrite("CHANGING PriImg: ".$prevPriImg." ---> ".$postPriImg);
//the line above gives the right values
}
}
}
}
$k++;
}
closedir($handle);
}
//setting updated primary image index
$this->updateDBParameter("usher_stats","idUsher",$usherId,"idPrimaryImg",$postPriImg);
}
return count($images)/2;
}Also, if I intentionally put some error into the update query the PDOException writes the related error as it should. And when I run the function in standalone (just using some simple php) it works perfectly too.
This is the line where the divine magic happens I suppose: $stmt->execute(array($value,$keyValue));
Searching in the Web I've found this: http://bugs.mysql.com/bug.php?id=36406. Though this is not entirely my case...
I am pretty desperate with this case already after spending more than a day trying to find the answer. I've posted this already in every php development forum I know with no result. And I definitely do not want to change/separate the methods.
Ex. of common usage of the method: updateDBParameter("usher_stats","idUsher","4a1dcda8ec831","idPrimaryImg","9");
Related fields: idUsher - char(16), idPrimaryImg - tinyint(1)
MySQL 5.0.45, PHP 5.2.5
Thanks in advance