PDO execute() - update query executes but nothing happens

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Xeel
Forum Newbie
Posts: 6
Joined: Fri May 29, 2009 11:31 am

PDO execute() - update query executes but nothing happens

Post 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:
Last edited by Benjamin on Fri May 29, 2009 12:21 pm, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Xeel
Forum Newbie
Posts: 6
Joined: Fri May 29, 2009 11:31 am

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

Post 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.
Xeel
Forum Newbie
Posts: 6
Joined: Fri May 29, 2009 11:31 am

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

Post 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());
    }
}
Xeel
Forum Newbie
Posts: 6
Joined: Fri May 29, 2009 11:31 am

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

Post by Xeel »

I was stupid after all... :oops: topic closed :mrgreen:
....
:drunk:
Post Reply