Search code examples
phpsqlcopyrow

copy and insert row in same table with new primary key+php

my project: PHP

i have a table. i want copy one row into same table with diffrent primary key.

sql code: INSERT INTO messages SELECT * FROM messages WHERE id='$id'

when i click on submit show : Error: INSERT INTO messages SELECT * FROM messages WHERE id='12' Duplicate entry '12' for key 'PRIMARY'

Solution

  • Because you're trying to insert every field in that row, which includes the primary key. If you want to specify a subset of fields, specify a subset of fields:

    INSERT INTO messages (ColumnA, ColumnB, Etc)
SELECT ColumnA, ColumnB, Etc
FROM messages WHERE id='$id'

    That way you're not also inserting a value into id.

    (This is of course assuming that the database auto-generates the primary key. If it doesn't, you'd need to insert whatever value would be required for a primary key.)

    As an aside, the use of what appears to be a PHP variable (and explicit quotes) in your SQL code strongly implies that you are likely vulnerable to SQL injection. Right now is the best time to correct that.