Copy data from one table to another with mysqli

Question

I have some records in the db if I customer delete a record, I use that record id to copy the data from the that table and insert it into an archive table

$sqlarch = "INSERT customers_archive SELECT * FROM customers_record WHERE UCI = '8233379' "

Answer 1

Given only the names of two tables in your database and a possibly misunderstood question in keeping with the comment made regarding a trigger perhaps the following might be of use.

To replicate the customers_record table such that it is suitable to act as an archived version of the customers_record table you can simply create a clone using:

create table `customers_archive` like `customers_record`

and then create a simple trigger that will populate the new table with a copy of whatever record is deleted using:

CREATE TRIGGER `trCustomers_Archive` BEFORE DELETE ON `customers_record` FOR EACH ROW BEGIN
    insert into `customers_archive` select * from `customers_record` where `UCI`=old.`UCI`;
END

You can extend this same logic to act as a log for any action performed on a particular table if you add additional columns to record the type of sql action being performed and also possibly the user which can be useful for audit purposes.