Search code examples
mysqlsqlinsertcascadelastinsertid

Copy and mutiple insert into (mysql)?

My problem is very simple to explain but I don't success to find the right answer to it !

I have 3 tables in mysql, called tA, tB, tC. I have one entry A in tA, 2 entries B in tB and 3 entries C in tC with a hierarchical architecture like this: 

            A
            |
     ----------------
     |              |
     B1             B2
     |              |
 --------           |
 |      |           |
 C1     C2          C3

As you can imagine, I have B entries are linked to A entries with A id, and C entries are linked to B entries with B id.

I just want to copy these data to :

            A'
            |
     ----------------
     |              |
     B1'            B2'
     |              |
 --------           |
 |      |           |
 C1'    C2'         C3'

So at the beginning, I start by creating A' and copying the B entries linked to A'. No worries.

But after, I did a request with a left join to copy C entries to C'. It nearly works... the only problem is that, by this way, my C' entries are linked to B id and not to B' id !!!!

I try some other stuffs but I don't know how to do that. It seems so simple. Maybe I'm tired... Maybe I have to copy B entries one by one, and copy all C entries of the current B entry before to go to the next.

But is there not a smarter way to do that ? With insert_id, cascade, trigger, foreign key ???

I'm not a sql specialist and I hope somebody here will have a nice solution.

Thanks by advance.

Bastien

Solution

  • Alain's method is a possible way to handle this purely in SQL, but requires some table modifications, basically storing the original id that an object was copied from.

    So you said you are using php, and I wish I could give you more relevant examples, but I've been away from php too long. But you should be able to do the following.

    1. Get A from the database, store A id in a variable in php (suggest $oldAID)
    2. Copy A properties and store back to the db as A'
    3. Get Last Insert ID and store as variable (suggest $newAID)
    4. Get all B records from DB where parent = $oldAID
    5. Loop through the b records, store the current records id as $oldBID
    6. Copy B1 propertied and store back to DB as B1' with a parent id $newAID
    7. Get last insertID ans store as $newBID
    8. Get all C records from DB where parent ID = $oldBID
    9. Make Copies and store back to DB with parent ID = $newBID
    10. Itterate through your loop started in step 5

    By using PHP to keep tabs on your last insert ID's you can make the copies you need and keep them accurate. Downside to this, there is a lot of back and forth between php and MySQL so in a really large set, that would go several levels deep, a process like this can take a few seconds. But if your normal sets are as small and shallow as your example, it shouldn't be too bad.