Duplicate tables related to N: M (including relationships)

Question

While working on an existing webapp (PHP / MySQL), I came to this: I have 2 tables that store id names and types. The relationship between them: N <----> N I have another table between them. (see picture)

I have a final table called a "category" that has no image. I have to do the following: duplicate contracts and clients having id_category = 1 for contracts and clients with the same values, having id_category = 3. And also duplication of relations between them .

So far, I have managed to duplicate all contracts and clients separately, using:

INSERT INTO contract (t_name, id_category) SELECT t_name,'3' WHERE id_category=2 INSERT INTO customer (t_name, id_category) SELECT t_name,'3' WHERE id_category=2 

(The "id" field is automatically added, it is excellent).

But about copying relationships, I cannot understand HOW to solve this problem.

Additional Information:

• Using temporary tables is fine, but I cannot change the existing structure of tables.
• Contracts can have from 0 to N clients. Customers can have from 0 to N contracts.
• I can use either strict MySQL or PHP.

Answer 1

You say that you cannot change the structure of the table. If you could, I would recommend against the denormalization (duplication of rows) of these tables depending on what you are doing.

I'm also a little confused because you are saying that you need to duplicate strings and relationships from id_category = 1 to id_category = 3 , but then your query samples have id_category = 2 .

However, this answer should be applied independently. I would use PHP and PDO.

 $pdo = new PDO('mysql:host=?', $user, $passwd); $stmtCustomers = $pdo->prepare("INSERT INTO customer (t_name, id_category) VALUES (t_name, ?) WHERE id_category = ?"); $stmtContracts = $pdo->prepare("INSERT INTO contract (t_name, id_category) VALUES (t_name, ?) WHERE id_category = ?"); $stmtRelation = $pdo->prepare("INSERT INTO customer_has_contract VALUES (?, ?)"); //Perform in a loop if needed $pdo->beginTransaction(); $stmtCustomers->execute($target_cat_id, $origin_cat_id); $cus_id = $pdo->lastInsertId(); $stmtContracts->execute($target_cat_id, $origin_cat_id); $con_id = $pdo->lastInsertId(); $stmtRelation->execute($con_id, $cus_id); $pdo->commit();