CAKEPHP 2.0 updateALL, json_encoded data

Question

I have another problem with one of my controller functions. I have written a function that updates some fields from my database. I am grabbing the content from a decoded json_file and after I grabbed the data I have encoding it again so I could store it in my database. I am getting some errors I do not understand.

My function

public function admin_update_rulings()
{
    $this->loadModel('Magicsets');
    $this->loadModel('Cards');
    $this->autoRender = false;
    $code = 'LEA';
    $cards = file_get_contents('http://mtgjson.com/json/' . $code . '-x.json');

    $decodedcards = json_decode($cards);
    $this->Card->query("SET CHARACTER SET utf8");


    foreach ($decodedcards->cards as $cards) {
        $mvid = $cards->multiverseid;
        $legal = json_encode($cards->legalities);
        $rulings = '';
        if (!empty($cards->rulings)) {
            $rulings = json_encode($cards->rulings);
        } else {
            $rulings = json_encode('leeg');
        }

        $this->Cards->updateAll(
            array('rulings' => $rulings),   //fields to update
            array('multiverseid' => $mvid)  //condition
        );
    }
}

The error message I get:

Database Error

Error: SQLSTATE[42000]: Syntax error or access violation: 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 '[{"date":"2008-08-01","text":"This card has been returned to its original functi' at line 1

SQL Query: UPDATE magicmm.cards AS Cards SET Cards.rulings = [{"date":"2008-08-01","text":"This card has been returned to its original functionality. If it is enchanting an artifact that's already a creature, it won't change its power and toughness."},{"date":"2008-08-01","text":"A noncreature permanent that turns into a creature can attack, and its {T} abilities can be activated, only if its controller has continuously controlled that permanent since the beginning of his or her most recent turn. It doesn't matter how long the permanent has been a creature."}] WHERE multiverseid = 96

Notice: If you want to customize this error message, create app/View/Errors/pdo_error.ctp

Any help on solving this issue would be greatly appreciated :-).

Answer 1

You need to wrap strings in quotation marks when using updateAll() in Cake. You can do this using the datasource's value() method:-

$db = $this->getDataSource();
$value = $db->value($rulings, 'string');
$this->Cards->updateAll(
    array('rulings' => $rulings),   //fields to update
    array('multiverseid' => $mvid)  //condition
);

You're probably better off using save() rather than updateAll() if you know the primary key when saving.

See the accepted answer to Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual for a fuller explanation.

Answer 2

Thanks for the advice everyone. I decided to look into the save method that both of you adviced me to use. It resolved the issue for me and everything is working now. I changed my update code to this.

        $updaterow = $this->Cards->find('first', array('fields' => array('id'), 'conditions' => array('multiverseid' => $mvid), 'limit' => 1));
        $id = $updaterow['Cards']['id'];

        $data = array('id' => $id, 'rulings' => $rulings, 'legalities' => $legal);

        $this->Cards->save($data);