How to Duplicate Records in the same MySQL Table Using the Inserted IDs

Question

I have 2 tables. The first table is tbl_items and the second table is tbl_items_extras.

I insert my Items into tbl_items and if in case this item has a single/multiple extras, they will be inserted in the table tbl_items_extras where tbl_items.id=tbl_items_extras.tbl_items_id.

Below is the screenshot of my two tables.

I was able to duplicate the records for tbl_items perfectly using the below query;

INSERT INTO `tbl_items` (`items_ref_id`, `rev`, `die_number`, `product_type_id`, `parts_id`, `complexity_id`)
          SELECT
            q.`items_ref_id`,
            q.`rev`+1,
            q.`die_number`,
            q.`product_type_id`,
            q.`parts_id`,
            q.`complexity_id`
          FROM `tbl_items` q WHERE `items_ref_id`='$refNum' AND `rev`='$maxRev'

But when I duplicate the tbl_items_extras records using the below query;

INSERT INTO `tbl_items_extras` (`tbl_items_id`, `extras_conditions_id`, `percentage`, `quantity`, `total_percentage`)
          SELECT
            q.`tbl_items_id`,
            q.`extras_conditions_id`,
            q.`percentage`,
            q.`quantity`,
            q.`total_percentage`
          FROM `tbl_items_extras` q WHERE `tbl_items_id` IN (SELECT `id` FROM `tbl_items` WHERE `items_ref_id`='$refNum' AND `rev`='$rev')

In this query, I didn't get what I need exactly. See the below screenshot.

What I need is to duplicate selected records in tbl_items_extras where the tbl_items_id has to be the SQL_INSERTED_ID of the duplicated records in tbl_items.

The result should be as per the below screenshot.

As you can see, the tbl_items.id was used accordingly in the tbl_items_extras.tbl_items_id.

I know I can use the LAST_INSERT_ID but this works only for a single record.

You should use parameters instead of putting PHP variables directly into your query. — Dharman, Dec 08 '19 at 10:50
You 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, Dec 08 '19 at 10:58
@Dharman, I'm already using these - `$refNum = mysqli_real_escape_string($connect, $_POST['refNum']); $rev = mysqli_real_escape_string($connect, $_POST['rev']);` — iAmBorgy, Dec 08 '19 at 11:24
Don't use these. They are not suitable for prevention of SQL injection and they only make your code messy and buggy. Use prepared statements with parameter binding, it will make your life much easier once you learn them. — Dharman, Dec 08 '19 at 11:34
Noted. Thanks @Dharman, I will learn from here. [link]https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection — iAmBorgy, Dec 08 '19 at 11:36
Not the worst tutorial, but they still have some questionable code in there. Better learn from here: https://phpdelusions.net/ — Dharman, Dec 08 '19 at 11:37

score 0 · Answer 1 · answered Dec 10 '19 at 09:17

I found an answer to my question.

I put my DUPLICATE QUERY inside a WHILE LOOP where I fetched the id of each row.

While tbl_items duplicates the record it will duplicate the tbl_items_extras using the fetched id and insert the $inserted_tbl_items_id in the tbl_items_extras.tbl_items_id.

    $stmt = $connect->prepare("SELECT * FROM `tbl_items` WHERE `items_ref_id`= ? AND `rev`= ?");
    $stmt->bind_param("ii", $refNum, $maxRev);
    $stmt->execute();
    $result = $stmt->get_result();

    // duplicate tbl_items
    if($result->num_rows === 0) exit('No rows');
    while($row = $result->fetch_assoc()) {
    $id = $row['id']; 

    // I put my tbl_items DUPLICATE QUERY (same as the above) 

    $inserted_tbl_items_id = $connect->insert_id; // get inserted id


    // and my tbl_items_extras DUPLICATE QUERY
    $sql = "INSERT INTO `tbl_items_extras` (`tbl_items_id`, `extras_conditions_id`, `percentage`, `quantity`, `total_percentage`)
      SELECT
        '$inserted_tbl_items_id',
        q.`extras_conditions_id`,
        q.`percentage`,
        q.`quantity`,
        q.`total_percentage`
      FROM `tbl_items_extras` q WHERE `tbl_items_id` IN (SELECT `id` FROM `tbl_items` WHERE `id`='$id')";
    $connect->query($sql);

    }

How to Duplicate Records in the same MySQL Table Using the Inserted IDs

1 Answers1