CODEWITHSUNDEEP
phpmysqlpdoinsert-intoon-duplicate-key
Merc
Merc

Reputation: 4570

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

I just tried to get an INSERT INTO ON DUPLICATE KEY mySQL query to get to work. Unfortunately with no luck. I read all the many posts on that matter here in stackoverflow, I had a look at this http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html, but still no luck...

I have a table like this:

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

My Id is a Unique value, but not Auto Incrementing. all other values are booleans describing some chosen options.

I wanted to write a function to either update the table if the id is already existing or write a new row if I have a new id.

I'm working with existing code with a database class.

$this->db = new PDO($host, $user, $password);
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);

(don't ask me about the setAttribute. That's just what was in the Code...) and later on:

$this->query = $this->db->prepare("INSERT INTO myTable (id, option1, option2, option3, option4) 
VALUES (83, 0, 1, 1, 1) ON DUPLICATE KEY UPDATE id = VALUES(id)");
$this->query->execute();

I know that I don't really have to prepare anything like binds. (In the end I want to do so, but I could not figure out how to get it done here.

Well I don't get any error Message from catching PDO Exceptions but I cannot get my database updated. Let's assume that all the stuff concerning connecting to the DB is ok (as I can select data).

I also tried notations like this:

$this->query = $this->db->prepare("INSERT INTO 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 INTO myTable (id, option1, option2, option3, option4) 
VALUES (83, 0, 1, 1, 1) ON DUPLICATE KEY UPDATE id = id + 1");

Which would all be the same, right?

Well, I would be very very glad if someone could help me out here. I must be doing something completely wrong...

Upvotes: 1

Views: 417

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You don't want on duplicate key update. This either inserts a new row. Or it updates the old row. You seem to want to insert a new row, but with a different id when there is a conflict.

I might suggest something like this:

INSERT INTO myTable(id, option1, option2, option3, option4) 
    select (case when max(id = 83) > 0 then max(id) + 1
                 else 83
            end), 0, 1, 1, 1
    from mytable;

This will put 83 into the table if it does not exist. It will put the maximum id + 1 in if 83 does exist.

I don't really recommend this. Better you should have an auto-incrementing id. I don't think the above logic is multi-user safe in all storage engines, but it might solve your problem.

Upvotes: 2

Related Questions