Ahhh, a section I didn't include - what if the values are already in the new database's secondary table with different ID #'s, you'd have to check by the VALUE in each array and find out which ID's are different and assign those accordingly - that's a few steps which should be done prior to creating the new array of values not in the new database.
On Wed, May 19, 2010 at 6:26 PM, William Attwood <[email protected]> wrote: > 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] > -- 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
