Jump to content

PDO: General error: 1366 Incorrect integer value


AdRock
Go to solution Solved by Ch0cu3r,

Recommended Posts

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

 

Link to comment
Share on other sites

  • Solution

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 by Ch0cu3r
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.