• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[MYSQL] Update data error

#1
[eluser]Clooner[/eluser]
Code:
$this->db->set('read' , 1);
$this->db->where('id', $id);
$this->db->update($this->table);
Is the code I use to generate my update query. This would come out looking like this.
Code:
UPDATE news SET read = 1 WHERE `id` = '9'
This always worked until this morning. Right now I get a database error saying the query is incorrect.

I can change the code to
Code:
$this->db->set('`read`' , 1);
$this->db->where('id', $id);
$this->db->update($this->table);
This would come out looking like this.
Code:
UPDATE news SET `read` = 1 WHERE `id` = '9'
And this works. What did I do to my mysql to no longer accept the first query? Or is this a CI thing?

#2
[eluser]TheFuzzy0ne[/eluser]
Please could you post the exact error MySQL gives you?

#3
[eluser]Clooner[/eluser]
[quote author="TheFuzzy0ne" date="1238170222"]Please could you post the exact error MySQL gives you?[/quote]

It is a simple error to fix but I just find it strange. This is the report I get from CI

Code:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read = 1 WHERE `id` = '10'' at line 1

UPDATE news SET read = 1 WHERE `id` = '10'

#4
[eluser]TheFuzzy0ne[/eluser]
What version of CodeIgniter are you using? The identifiers should be protected automatically.

Have you changed anything, by either adding a custom library, or editing the core files?

Here's what the $this->db->set() method looks like in 1.7.1:
Code:
/**
* The "set" function.  Allows key/value pairs to be set for inserting or updating
*
* @access    public
* @param    mixed
* @param    string
* @param    boolean
* @return    object
*/
function set($key, $value = '', $escape = TRUE)
{
    $key = $this->_object_to_array($key);

    if ( ! is_array($key))
    {
        $key = array($key => $value);
    }    
        foreach ($key as $k => $v)
    {
        if ($escape === FALSE)
        {
            $this->ar_set[$this->_protect_identifiers($k)] = $v;
        }
        else
        {
            $this->ar_set[$this->_protect_identifiers($k)] = $this->escape($v);
        }
    }
    
    return $this;
}

#5
[eluser]Clooner[/eluser]
I've found the fault. It is a mysql thing because the name of the column is "read" which is a reserved sql statement or word. But what is the reason for CI not escaping the column names?

#6
[eluser]TheFuzzy0ne[/eluser]
What version of CodeIgniter are you using?


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.