I want to know if there is any way that in a single query it does three things, as far as I know it is two things, and the third would be an external agent, but I still ask if someone knows and can share it with me, it turns out that I have a current_products table, all the data from this table is sent to another table which is finished_products, which the query for this would be:
$query = self::connect()->prepare("INSERT INTO `finished_products`
(room, name, lot, quantity_packed, pallet) SELECT room, name, lot, quantity_to_package, finished_pallets
FROM `current_products` WHERE `room`=:room");
$query->execute(["room"=>$room]);
Once you've inserted that data from the current_products table to the finished_products table, how could you remove the data from the current_products table where the room equals the room passed to it, something like this:
$query = self::connect()->prepare("INSERT INTO `finished_products`
(room, name, lot, quantity_packed, pallet) SELECT room, name, lot, quantity_to_package, finished_pallets
FROM `current_products` WHERE `room`=:room") DELETE * FROM `current_products` WHERE `room`=:room;
$query->execute(["room"=>$room]);
I've been thinking of a trigger that runs on the finished_products table which will delete the data from the current_products table but I don't get the syntax
Thank you :)
UPDATE
done with transaction:
$connection = self::connect();
$connection->beginTransaction();
try {
$query = $connection->prepare("INSERT INTO `finished_products`
(room, name, lot, quantity_packed, pallet, start_date, finish_date) SELECT room, name, lot, quantity_to_package, finished_pallets,
start_date FROM `current_products` WHERE `room`=:room");
$query->execute(["room"=>$room]);
$query = $connection->prepare("DELETE FROM `current_products` WHERE `room`=:room");
$query->execute(["room"=>$room]);
$result = $connection->commit();
}catch(PDOException $e) {
$connection->rollBack();
return $e->getMessage();
}
With your code, every SQL statement you execute does so so that the results are immediately saved to the database. First you insert a record in
finished_products
. Then you delete a record ofcurrent_products
(the workaround you give, with a trigger , is equivalent).However, what if the first statement fails? You should detect the failure so as not to run the second one. What if the first statement doesn't fail but the second does? You should catch the failure to undo the first one (making sure it is actually undone); and hope that nothing bad has happened while the database has been saving inconsistent data.
Instead of doing all that, in cases like these it is more convenient to execute the statements as a single transaction. The typical example of a transaction is a transfer of funds between two bank accounts. It would be a disaster if funds were added to the destination account without withdrawing them from the source account. The operation, from the logical point of view, is "transfer", not "add" and "remove". If the operation cannot be executed as "transfer", it should not be performed. And for that it is executed as a transaction:
Once the transaction is started, the statements are executed but the changes will not be saved until you do a
COMMIT
. If any statement fails, all changes will be rolled back until the start of the transaction. Also, log inBEGIN TRANSACTION
andCOMMIT
you can run aROLLBACK
at any time to leave everything as it was when the transaction started.This is the natural solution to your problem, assuming your database engine supports transactions. For example MySQL (with InnoDB) and PostgreSQL support transactions (MySQL with MyISAM does not).
When mounting
prepare
this, it asks you for astring
containing the SQL statement. Thisstring
can contain more than one statement, separated by semicolons.