0

I have to following code to move a particular row to another table.

$id = $_GET['id'];
$table = $_GET['name'];
$trashtable = $_GET['name'] . "_trash";

$movetotable = $conn->prepare("INSERT INTO `$table` SELECT * FROM `$trashtable` WHERE id = :id");
$movetotable->bindParam(':id', $id, PDO::PARAM_STR);
$movetotable->execute();

If the primary key of this particular row already exists it throws ofcourse a duplicate entry error.

I would like to add the row with the duplicate primary key as a new row with a new id.

Is this possible? Hope to hear something soon!

joostdelange
  • 113
  • 11
  • Use `INSERT ... ON DUPLICATE KEY UPDATE` https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html – Funk Forty Niner Apr 17 '17 at 19:40
  • I tried doing that @Fred-ii- But I got a syntax error – joostdelange Apr 17 '17 at 19:42
  • Your code is vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection) since you're using [unvalidated table names](https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement). – Alex Howansky Apr 17 '17 at 19:42
  • Try `LAST_INSERT_ID()` then @JoostdeLange sounds like that's what it's about. what was the exact error? – Funk Forty Niner Apr 17 '17 at 19:43
  • This is what I tried to add 1 to the duplicate id: **$movetotable = $conn->prepare("INSERT INTO `$websitedirectory` SELECT * FROM `$trashtable` WHERE id = :id ON DUPLICATE KEY UPDATE id = id + 1");** @Fred-ii- – joostdelange Apr 17 '17 at 19:49
  • @JoostdeLange if the `id` column for that column is AI, you can't use `+1`. You may have to update your question to show what the schemas are for those tables and representative (example) values. – Funk Forty Niner Apr 17 '17 at 19:54

0 Answers0