PDO: General error: 1366 Incorrect integer value

[AdRock]

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

 

[Ch0cu3r]

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