Page 1 of 1

PDO execute() - update query executes but nothing happens

Posted: Fri May 29, 2009 11:47 am
by Xeel
Hello everyone :wink:

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;
}
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 :roll:

Re: PDO execute() - update query executes but nothing happens

Posted: Fri May 29, 2009 12:34 pm
by pickle
My guess is that maybe the query is running fine, but it's not what you expect - maybe the key's wrong or something.

Normally I'd say output the query & run it manually, but with prepared statements that's not possible. Is there some way via PDO to get the actual parsed query? Is that stored in the MySQL query log?

Re: PDO execute() - update query executes but nothing happens

Posted: Fri May 29, 2009 12:46 pm
by Xeel
Thanks, astions, for the code change, was looking for code coloring myself =)

2 pickle: All the parameters are fine. I output the query as it goes in the log and everything is fine. As I said, if I run it in SQL console, or the method solo it works. If I take the same Id and other stuff without making the first query and file renaming the query works as well. I am sure it's some kind of PDO bug.

Re: PDO execute() - update query executes but nothing happens

Posted: Fri May 29, 2009 1:18 pm
by Xeel
Maybe it's relevant. Here's the connection initialization:

Code: Select all

private function openDbPDO($p){
    try{
        $this->conn = new PDO('mysql:host='.$p->dbhost.';dbname='.$p->dbname, $p->dbuser, $p->dbpassword);
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }catch(PDOException $e){
        $this->log->lwrite($e->getMessage());
    }
}

Re: PDO execute() - update query executes but nothing happens

Posted: Fri May 29, 2009 5:12 pm
by Xeel
I was stupid after all... :oops: topic closed :mrgreen:
....
:drunk: