0

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'

morfin
  • 3
  • 2

1 Answers1

0

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.

David
  • 208,112
  • 36
  • 198
  • 279
  • Can I define a new variable when copying a row? Can I take new information from the form and replace it in ColumnA? $ColumnA=$_POST['ColumnA']; – morfin Jul 10 '22 at 17:17
  • @morfin: It’s not clear what you mean by that, though it is clear that the pseudo-code you’re proposing is very SQL-injectable and should not be used. – David Jul 10 '22 at 17:20