Jonathan--
Let's outline the scenario, you have two databases, four total tables
(concept applies for more):
- Database1
- Table1-1
- Table2-1
- Database2
- Table1-2
- Table2-2
Your process is as follows (PHP Script):
1. SELECT * FROM Database1.Table2-1 into ARRAY1 (id=>val)
2. SELECT * FROM Database2.Table2-2 into ARRAY2(id=>val)
3. INSERT INTO ARRAY3 (ALL FROM ARRAY2 NOT IN ARRAY1)
1. FOR EACH ELEMENT PUT INTO ARRAY3, ASSIGN ARRAY1[id][newid] = newid
4. SELECT * FROM Database1.Table1-1 into ARRAY4
1. FOR EACH ELEMENT IN ARRAY4 (data to merge into Database2) GET
ARRAY1.referenceid (Table2-1)
2. SET ARRAY4.referenceid = ARRAY2[id][newid] (Table2-1->Table2-2)
5. INSERT INTO Database2.Table2-2 ARRAY3 ELEMENTS
6. INSERT INTO Database2.Table1-2 ARRAY1 ELEMENTS
This way you get all new values and new ID's for them in Step3, you keep the
old ID and new ID in Step3.1, you then get all data from your Database1
table with those referential links, you update those according to the newid,
and then insert all new data into your second database.
Did I miss anything? I'm in a hurry, I hope that's easy to understand. I
could have named the Arrays better.
-Will
On Wed, May 19, 2010 at 5:53 PM, Jonathan Duncan <
[email protected]> wrote:
> UPHPU,
>
> Here is a database conundrum. I have two databases with identical tables
> (same structure different data). They have been on different sites though
> collecting data from different groups of people. I now need to merge that
> data. There is one table that relies on the 'id' key of another table. So
> the id's well need to be changed, and the other table updated to match. Any
> ideas?
>
> I think I like this way the best:
> http://www.everymanhosting.com/forum/about22.html
>
> However, this will not address the required cascading update.
>
> This has some good information:
> http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/
>
> But also not a final solution.
>
> I am thinking I may just write a PHP script that will select from
> db1.table1, insert it into db2.table1 and then check if there is an
> associated row in db1.table2 and if so insert that in db2.table 2 with new
> 'id' key from last insert on db2.table1.
>
> Thoughts?
>
> Thanks,
> Jonathan (aka SunSparc)
>
>
>
>
> _______________________________________________
>
> UPHPU mailing list
> [email protected]
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
>
--
Take care,
William Attwood
Idea Extraordinaire
[email protected]
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
