copying data and keeping referencial integrity with mysql

[fivestringsurf]

Using:

mysql  5.1.4

php 5

I built an application in which users can choose a predefined menu consisting of various parent/child relationships.  So basically once choosen the data rows are copied from the "template" table and added to a "user" table. From there the user can alter the predefined values as they wish.  The problem is the unique ids in the "user" table are different so all the parent_id /child_id stuff is off in the "user" table. Is there a way to easily way to duplicate the data (with new ids) but keep the references correct with a single  mysql statement? Or do I have to do a dreaded mix of php/mysql adding the parents first getting their unique ids and then adding subsequent children on and on.?

 

Just to be clear

"template table"

id | parent_id | value

1    null              red

2    null              blue

3    null              purple

4    1                  gray

5    2                  light gray

6    4                  orange

 

what i need -> "user table" (ids are unique and auto incrementing)

id | parent_id | value

1000    null        red

1001  null          blue

1002    null        purple

1003  1000      gray

1004  1001      light gray

1005  1004      orange

 

Thanks for any suggestions

[The Little Guy]

Something like this:

 

INSERT INTO user_table (parent_id, value) SELECT parent_id, value FROM template_table order by id

[fivestringsurf]

First off, thanks for the reply...

your sample code is pretty much what i have already; the problem with that approach is everything transfers over to a new table but the parent/child relationships get messed up.  The parent_id stays the same as the old table when it needs to link up with the id of the new table.

;....?

the sample code leads to a table that looks something like:

 

id | parent_id | value

1000    null        red

1001  null          blue

1002    null        purple

1003  1            gray

1004  2            light gray

1005  4            orange

 

note the broken child/parent references

 

[The Little Guy]

What about this:

 

INSERT INTO user_table (parent_id, value) SELECT id, value FROM template_table order by id

[fivestringsurf]

I'll have to try that out tomorrow (it's past my bedtime right now );

but at first glance it appears that would simply move the id from the first table and fill it into the parent_id field, still not maintaining parent/child references between the same table.

Maybe I'm not being clear.  I need to maintain parent/child pairs within the same table.

Thank you.

 

 

[fivestringsurf]

I have come to the realization that accomplishing this is (i don't think) possible in a single mysql statement. I'm actually going to re-work my strategy. I will remove auto increment and allow the ids to be non-unique.  I have added a field (user_id) and that will help track data... via a unique id between user_id and id. So the parent/child ids can actually be an exact replica from the "template" table -- whew that's much easier.  I've always just used auto incrementing primary ids...but here I don't think that's the way to go.