#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    78
    Rep Power
    11

    Question How can I copy a parent and all child/grandchild entries at once?


    Hi,

    Is there an efficient way to copy a parent entry and all related (child, grandchild, great grandchild, etc.) entries at the same time?

    MySQL Version: 5.0.51

    I have a series of related InnoDB tables with Foreign Keys.
    Code:
    +------------------+
    | tblParent           |
    +------------------+
    | ParentId (PK)    |
    | ParentName      |
    | <other fields>   |
    +------------------+
    
    +------------------+
    | tblChild             |
    +------------------+
    | ChildId (PK)      |
    | <some fields>   |
    | ParentId (FK)    |
    +------------------+
    
    +----------------------+
    | tblGrandChild         |
    +----------------------+
    | GranChildId (PK)    |
    | <some fields>       |
    | ChildId (FK)           |
    +----------------------+
    
    PK = Primary key (auto increment)
    FK = Foregin key
    If I were to do this programatically with nested PHP loops I would have to:

    - Record the existing parent's PK (old.parentid)
    - Insert a copy of the parent entry
    - Record the new parent entry PK (new.parentid)
    - Select all child entries having the FK old.parentid
    - Insert copies of those children replacing the FK with new.parentid
    - For each child select all grandchild entries having the FK old.childid
    - Insert copies of those grandchildren replacing the FK with new.childid
    - ...and so on...

    This seems like a very cumbersome method for a task that feels like it would be very common.

    I use the ON DELETE CASCADE functionality which is extremely useful for deleting entries (deleting the parent deletes all related child, grandchild, etc. entries automatically).

    Is there a native MySQL solution similar to the ON DELETE CASCADE that will allow me to copy all related fileds simutaneously using foreign keys? Or am I stuck with complex PHP loops?

    Thanks in advance for any advice you can provide.

    Cheers,

    Andrew
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,238
    Rep Power
    4279
    Originally Posted by awaddington
    Or am I stuck with complex PHP loops?
    possibly

    an alternative is to do a join of all three tables, but then there's the issue of...

    ... where are you copying these rows to?

    i personally don't like the idea of three separate tables

    have a look at this --Categories and Subcategories

    they are usually all stored in the same table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    78
    Rep Power
    11
    Hi Rudy,

    Thanks for the response and link.

    The parent/child entries have different content. The relationships also aren't quite as linear as say nested menu content (Cars->Blue Cars->Blue Cars with Silver trim)

    Consider two parent tables:

    1) Users
    2) Projects

    Users and Projects have a many to many relationship - A project has many users and a user can have many projects (linked with the use of a table project_users containing user_id and project_id)

    A [Project] also has several [Dimensions] (child table) which in turn have [Attributes] (grand child)

    A [Project] also has several [Categories] (child table) which in turn have several [Questions] (grandchild) which in turn have several [Responses] (great grandchild).

    A [User] assigns scores for [Attributes] as they relate to [Responses].

    For example: a category might be "Demographics" having a question "Gender distribution" having a response "50% male"

    A dimension might be "Car colour preferences" and an attribute might be "Blue".

    The score is the relevance of blue car colour as it relates to males (high, neutral, low, etc.).

    I don't think this works cleanly in a single table. Another point I have to consider...

    Code:
    <Poor planning NOOB excuse>
      I've already built the entire system using multiple tables
      and can't afford to redo it all to switch to a single table
      even if it is the technically correct approach.
    </Poor planning NOOB excuse>
    The copy will be made into the same respective tables. A User may start a new project that is very similar to an existing project. Rather than start from scratch they can copy the project and make changes where needed.

    I guess I'll focus on the PHP approach...but your answer has been VERY helpful because it means I can stop searching for a feature that doesn't exist.

    Thanks again.

    Cheers

    Andrew
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,238
    Rep Power
    4279
    Originally Posted by awaddington
    I don't think this works cleanly in a single table.
    you're darned right it doesn't

    thanks for the explanation

    i don't know of any good way to copy a complete structure like that automatically

    so you'd have to write a script consisting of one INSERT statement for each target table (to be copied into) where that INSERT statement uses a SELECT which retrieves data from the table to be copied, and each SELECT would be different, and might involve multiple JOINs depending on which table it is, since the relationship has to be traced from the single row in the project table for the project being copied

    did that make sense?

    you should write and test these INSERT statements outside of php before assembling them into an executable script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    78
    Rep Power
    11

    Thumbs up


    Originally Posted by r937
    did that make sense?
    Absoluteley.

    Many thanks.

IMN logo majestic logo threadwatch logo seochat tools logo