4

Working on an existing webapp (PHP/MySQL) I came to this point: I have 2 tables storing names and types id. The relation between them being N<---->N I have another table in between. (see picture)

Tables names and columns have been simplified, but it's pretty much it

I have a last table called "category" which is unpictured. What I have to do is: duplicate contracts and customers having id_category=1 to contracts and customers with same values, having id_category=3. As well as duplicating the relations between them.

So far I've managed to duplicate all contracts and customers apart, 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 being Auto-Incremented, it goes fine).

But about copying the relations, I can't figure out HOW to take the problem.

Additional info:

  • Using temporary tables is fine, but I can't modify the existing structure of tables.
  • Contracts may have from 0 to N customers. Customers may have from 0 to N contracts.
  • I can use either strict MySQL, or with PHP.
tshepang
  • 10,772
  • 21
  • 84
  • 127
monsieur_h
  • 1,320
  • 1
  • 9
  • 20

1 Answers1

0

You say you can't change the table structure. If you could, I would likely recommend against the denormalization (duplication of rows) of these tables depending upon what you are doing.

I'm also a bit confused because you say you have to duplicate the rows and relations with id_category = 1 to id_category = 3, but then your sample queries have id_category = 2.

This answer should apply regardless, though. 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();
Explosion Pills
  • 176,581
  • 46
  • 285
  • 363
  • Thanks for answering.About the categories: it is a typo. My method has to complete with any given parameter anyway. (i.e. duplicate category 2 to 3, or 1->4...). About the SQL: isn't the `$stmtRelation->execute($con_id, $cus_id);` going to link every customers to every contract? What about customer having 0 contract? The will get one with your solution. – monsieur_h Sep 18 '12 at 13:52
  • @caffein That `execute` will only link the last customer and contract that you inserted. If it is "0" or the query fails to insert for some reason, you can always skip the relation check. Is that a possibility? – Explosion Pills Sep 18 '12 at 13:55
  • How does it works : `$stmtCustomers-execute()` will insert *the whole set* of duplicated customers, while `->lastInsertId()` will only return one of them. I don't understand. – monsieur_h Sep 18 '12 at 14:05