AdRock Posted November 3, 2013 Share Posted November 3, 2013 I have a database class that uses PDO and it all works until i need to do an update where I increment the value What I have is a table and one of the columns is called 'counter' and I need to update this table every time a page is visited This is the function that handles all the PDO and creates the query /** * update * @param string $table A name of table to insert into * @param string $data An associative array * @param string $where the WHERE query part */ public function update($table, $data, $where) { ksort($data); $fieldDetails = NULL; foreach($data as $key=> $value) { $fieldDetails .= "`$key`=:$key,"; } $fieldDetails = rtrim($fieldDetails, ','); $sth = $this->prepare("UPDATE $table SET $fieldDetails WHERE $where"); foreach ($data as $key => $value) { $sth->bindValue(":$key", $value); } $sth->execute(); } and this is how I call the function by passing parametres to the function $board = ucwords(str_replace('-',' ',$board)); $sql = "SELECT boardid from boards WHERE boardname = :board"; $rows = $this->db->select($sql, array(':board' => $board)); $postData = array( 'counter' => 'counter+1', ); $this->db->update('topics', $postData, "`topicid` = {$topic} AND `boardid` = {$rows[0]['boardid']}"); It fails the update with this error message PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'counter+1' for column 'counter' at row 1' in C:\www\mvc\libs\Database.php:143 The counter column is of type INT Quote Link to comment Share on other sites More sharing options...
Solution Ch0cu3r Posted November 3, 2013 Solution Share Posted November 3, 2013 (edited) Your code is trying to update the counter column's value to the string value of 'counter+1' not increment it's value by 1, this is because you are binding its value to the query. If you need to increment the columns value you don't bind it. I setup the $postData array like $postData = array( 'counter' => array( // column name as the key 'value' => 'counter+1', // the columns value 'bindValue' => false // do we bind it? ), ); Then change the Update function to public function update($table, $queryData, $where) { ksort($data); $fieldDetails = NULL; foreach($queryData as $column => $data) { // do we bind the value? Set the column to the value or add the placeholder if(isset($data['bindValue']) && $data['bindValue'] === false) { $fieldDetails .= "`$column`={$data['value']},"; unset($queryData[$column]); // remove it from array so the value doesn't bind } else $fieldDetails .= "`$column`=:$column,"; } $fieldDetails = rtrim($fieldDetails, ','); $sth = $this->prepare("UPDATE $table SET $fieldDetails WHERE $where"); foreach ($queryData as $key => $value) { $sth->bindValue(":$key", $value); } $sth->execute(); } EDIT: Added updated function. I posted my reply too early Edited November 3, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.