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.