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?

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.

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