2 Replies - 2800 Views - Last Post: 20 November 2012 - 09:58 PM

#1 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 926
  • View blog
  • Posts: 3,204
  • Joined: 19-January 10

INSERT ... ON DUPLICATE KEY UPDATE problems

Posted 19 November 2012 - 10:21 PM

It's pretty late at night so this is probably something simple... But I'm having some troubles with this query inside of this function:

/*
     * setPage(int $id, string $name, string $content)
     * Stores/updates $id with $name and $content
     * returns true on success, false on failure
     */
    public function setPage($id, $name, $content){
        $rand_filename = 'data/pages/staticPage_' . uniqid() . '.html';
        $write_result = file_put_contents($rand_filename, $content);
        if($write_result === false){
            return false;
        }
        $sql = "INSERT INTO pages(id, filename, name) VALUES('" . (int)$id . "', '" . $rand_filename . "', '" . $name . "')
                    ON DUPLICATE KEY
                        UPDATE pages SET name='" . $name . "', filename='" . $rand_filename . "' WHERE id='" . (int)$id . "'";
        echo $sql;
        $result = parent::get('db')->mysqli()->query($sql);
        if($result !== false){
            return true;
        }
    }



Example sql query produced:
INSERT INTO pages(id, filename, name) VALUES('3', 'data/pages/staticPage_50ab128a7b47e.html', 'FAQ') ON DUPLICATE KEY UPDATE pages SET name='FAQ', filename='data/pages/staticPage_50ab128a7b47e.html' WHERE id='3'


Error message from attempting query:
#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 'SET name='FAQ', filename='data/pages/staticPage_50ab128a7b47e.html' WHERE id='3'' at line 1


Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: INSERT ... ON DUPLICATE KEY UPDATE problems

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3481
  • View blog
  • Posts: 10,033
  • Joined: 08-June 10

Re: INSERT ... ON DUPLICATE KEY UPDATE problems

Posted 19 November 2012 - 11:47 PM

remove table name and SET from the UPDATE part. cf. MySQL Manual for comparison.

and remove the ' around the ID if that’s an INTEGER field (and if it is not, alter it to one)
Was This Post Helpful? 1
  • +
  • -

#3 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 926
  • View blog
  • Posts: 3,204
  • Joined: 19-January 10

Re: INSERT ... ON DUPLICATE KEY UPDATE problems

Posted 20 November 2012 - 09:58 PM

Ah, sweet. I also had to remove the WHERE part but now it's working :)

Much thanks!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1