php - mySQL INSERT INTO ON DUPLICATE KEY - no idea whats not working -

i tried insert on duplicate key mysql query work. unfortunately no luck. read many posts on matter here in stackoverflow, had @ http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html, still no luck...

i have table this:

------------------------------------------------- | id | option1 | option 2 | option 3 | option 4 | ------------------------------------------------- | 83 |    0     |    1     |     0    |    0    | 

my id unique value, not auto incrementing. other values booleans describing chosen options.

i wanted write function either update table if id existing or write new row if have new id.

i'm working existing code database class.

$this->db = new pdo($host, $user, $password); $this->db->setattribute(pdo::attr_errmode, pdo::errmode_silent); 

(don't ask me setattribute. that's in code...) , later on:

$this->query = $this->db->prepare("insert mytable (id, option1, option2, option3, option4)  values (83, 0, 1, 1, 1) on duplicate key update id = values(id)"); $this->query->execute(); 

i know don't have prepare binds. (in end want so, not figure out how done here.

well don't error message catching pdo exceptions cannot database updated. let's assume stuff concerning connecting db ok (as can select data).

i tried notations this:

$this->query = $this->db->prepare("insert mytable (id, option1, option2, option3, option4)  values (83, 0, 1, 1, 1) on duplicate key update id = 83"); 

or

$this->query = $this->db->prepare("insert mytable (id, option1, option2, option3, option4)  values (83, 0, 1, 1, 1) on duplicate key update id = id + 1"); 

which same, right?

well, very glad if me out here. must doing wrong...

you don't want on duplicate key update. either inserts new row. or updates old row. seem want insert new row, different id when there conflict.

i might suggest this:

insert mytable(id, option1, option2, option3, option4)      select (case when max(id = 83) > 0 max(id) + 1                  else 83             end), 0, 1, 1, 1     mytable; 

this put 83 table if not exist. put maximum id + 1 in if 83 exist.

i don't really recommend this. better should have auto-incrementing id. don't think above logic multi-user safe in storage engines, might solve problem.