I’m working on a small website, that keep tracks of students information, When someone makes an update on one of the students I want to archive a copy of the previous record, I know that doing this makes a copy of the current data.
"INSERT INTO archives SELECT * FROM students WHERE id = $id"
But I want to include the date when the edit was made, so I tried this, but it produces the following error:
DBD::mysql::st execute failed: Column count doesn't match value count at row 1 "INSERT INTO archives SELECT * FROM students WHERE id = $id, archive_date = $new_date"
Trying the following query – same error produced
INSERT INTO archives (val, val1, val2, val3, new_date) SELECT (val, val1, val2, val3, now()) FROM students WHERE id = $id
Advertisement
Answer
You should list all the columns and then you can set the value:
insert into archives ( . . ., archive_date) -- col list here select . . ., now() -- or whatever function you are using for the current time from students where id = ?;
Remember to pass the id
as a parameter.
EDIT: (based on the edit to your question)
INSERT INTO archives (val, val1, val2, val3, new_date) SELECT val, val1, val2, val3, now() -- or whatever function you are using for the current time FROM students WHERE id = $id;