How can I get the data if I delete it and insert it to another table?

Question

I have a little project which have item management and log history.

Item Management

Item Log

My problem is if I delete the data the last item name that I added is getting inserted in log history instead of the item name that I deleted.

How can I fetch the last item name that I deleted?

Here is my codes on delete button

    if(isset($_GET['delete'])) {
  $the_item_management_id = $_GET['delete'];
  $query = "DELETE FROM item_management WHERE item_management_id = {$the_item_management_id}";
  $delete_query = mysqli_query($connection, $query);

  $query1 = "INSERT INTO item_management_log (item_code_id, item_name, deleted_stock) VALUES ('$item_code_id', '$item_name', 'deleted $item_name from the stock')";
  mysqli_query($connection, $query1);
  header("Location:inventory_management.php");

}

I think you could change the order of the queries, executing $query1 first. Maybe working with transactions and rollback in case of failures. — jairhumberto, Jun 25 '21 at 03:04
You can use last inserted id concept Ref : https://www.w3schools.com/php/php_mysql_insert_lastid.asp And from that id you can fetch the last inserted data from new table. — S. Gandhi, Jun 25 '21 at 03:06
@jairhumberto I think this could work I need to fetch the data first then insert it and last is delete. — Gami gami, Jun 25 '21 at 03:10
@S.Gandhi could I also fetch the whole row if I use last insert function? — Gami gami, Jun 25 '21 at 03:12
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Jun 25 '21 at 09:56
Yes, by using last inserted id you can fetch that particular row data, after insertion you can run the select query by using last inserted id. — S. Gandhi, Jun 26 '21 at 14:37

jairhumberto · Answer 1 · 2021-06-28T04:07:36.427

I believe you could do this:

if (isset($_GET["delete"])) {
    mysqli_begin_transaction($connection); // Using transactions.

    // Execute log insertion first.
    $stm_log = mysqli_prepare($connection, "INSERT INTO item_management_log (item_code_id, item_name, deleted_stock) VALUES (?, ?, ?)");

    $log_message = "deleted $item_name from the stock";
    mysqli_stmt_bind_param($stm_log, "iss", $item_code_id, $item_name, $log_message);
    mysqli_stmt_execute($stm_log);

    // Remove the item later.
    $stm_delete = mysqli_prepare($connection, "DELETE FROM item_management WHERE item_management_id = ?");

    $the_item_management_id = $_GET["delete"];

    mysqli_stmt_bind_param($stm_delete, "i", $the_item_management_id);
    mysqli_stmt_execute($stm_delete);

    if (mysqli_stmt_affected_rows($stm_delete) > 0) {
        mysqli_commit($connection);
    } else {
        mysqli_rollback($connection); // Undo everything if nothing was deleted.
    }

    header("Location:inventory_management.php");
}

Why Transactions

You should use transactions because you probably want to log something that actually happened, and so you can abort (through rollback) recording the log if there is an error deleting the item. This way, you won't have an inaccurate log in the database.

Note

I also used mysqli_stmt_affected_rows which makes it possible to know the number of affected records and thus know if an item was really deleted or not.

Also, I believe you should use PDO instead of mysqli, which is a friendlier api. Or at least you should use the mysqli's object-oriented style, which will make your code cleaner and easier to understand.

How can I get the data if I delete it and insert it to another table?

1 Answers1