Merging Databases without losing data

[Guest]

Here's the short version: installed osc (2.2) on a server, and later learned the server was not secure. Reinstalled osc on the secure server, working great. But it took several days to reinstall and tweak all the files to get the new cart working just right, and some new customers and orders came through on the old cart. How can I pull the customer and order information from the old cart into the new one and merge them without overwriting or losing the data in the new cart? Is this possible?

[Guest]

Same question...can this be done?

 

Maybe use the MySQL function "merge" to create a new table??

 

Something like:

 

STEP (1): change the customer table name to customer1

STEP (2): import the extra customer table and name it customer2

STEP (3): run the following query in PhpMyAdmin ->

 

CREATE TABLE `customers` (

`customers_id` int(11) NOT NULL auto_increment,

`customers_gender` char(1) NOT NULL default '',

`customers_firstname` varchar(32) NOT NULL default '',

`customers_lastname` varchar(32) NOT NULL default '',

`customers_dob` datetime NOT NULL default '0000-00-00 00:00:00',

`customers_email_address` varchar(96) NOT NULL default '',

`customers_default_address_id` int(5) NOT NULL default '1',

`customers_telephone` varchar(32) NOT NULL default '',

`customers_fax` varchar(32) default NULL,

`customers_password` varchar(40) NOT NULL default '',

`customers_newsletter` char(1) default NULL,

PRIMARY KEY (`customers_id`)

) TYPE=MERGE UNION=(customers1, customers2) INSERT METHOD=LAST;

 

What do the gurus think?

[Guest]

I tried it and the info does not query correctly....back to the drawing board.

[Guest]

Try this:

 

Create a PHP page with the following code -->

<style type="text/css">
<!--
body {
font: 12px Verdana, Arial, Helvetica, sans-serif;
background: #000000;
color: #FFFFFF;
}

a {
font: bold 12px Verdana, Arial, Helvetica, sans-serif;
color: #FFFFFF;
}

a:hover {
font: bold 12px Verdana, Arial, Helvetica, sans-serif;
color: #FF0000;
}
-->
</style>
<?
/********************************************************************************
******************
Created by R. Easland
This script will import all the customer records from one table into another.
- TABLES AFFECTED: customers, address_book

INSTRUCTIONS:
(1) Data dump the following tables on the target database for backup purposes:
- address_book
- customers

(2) Data dump the same tables from the source database.

(3) Open the <SOURCE> SQL file data dump for customers and change all instances of 'customers' to 'customers_store' using find/replace function.
- Replace the CREATE TABLE instructions with the following and save the file:
 CREATE TABLE `customers_source` (
 `customers_id` int(11) NOT NULL auto_increment,
   `customers_gender` char(1) NOT NULL default '',
   `customers_firstname` varchar(32) NOT NULL default '',
   `customers_lastname` varchar(32) NOT NULL default '',
  	 `customers_dob` datetime NOT NULL default '0000-00-00 00:00:00',
 `customers_email_address` varchar(96) NOT NULL default '',
 `customers_default_address_id` int(5) NOT NULL default '1',
 `customers_telephone` varchar(32) NOT NULL default '',
 `customers_fax` varchar(32) default NULL,
 `customers_password` varchar(40) NOT NULL default '',
 `customers_newsletter` char(1) default NULL,
 PRIMARY KEY  (`customers_id`)
 ) TYPE=MyISAM;
 
(4) Open the <SOURCE> SQL data dump for address_book and change all instances of 'address_book' to 'address_book_store' using the find/replace function.
- Replace the CREATE TABLE instruction with the following and save the file:
 CREATE TABLE `address_book_source` (
   `customers_id` int(11) NOT NULL default '0',
   `address_book_id` int(11) NOT NULL default '1',
   `entry_gender` char(1) NOT NULL default '',
   `entry_company` varchar(32) default NULL,
   `entry_firstname` varchar(32) NOT NULL default '',
   `entry_lastname` varchar(32) NOT NULL default '',
   `entry_street_address` varchar(64) NOT NULL default '',
   `entry_suburb` varchar(32) default NULL,
   `entry_postcode` varchar(10) NOT NULL default '',
   `entry_city` varchar(32) NOT NULL default '',
   `entry_state` varchar(32) default NULL,
   `entry_country_id` int(11) NOT NULL default '0',
   `entry_zone_id` int(11) NOT NULL default '0',
   PRIMARY KEY  (`address_book_id`,`customers_id`)
 ) TYPE=MyISAM;

(5) Add the tables using the SQL upload file function (in the <TARGET DATABASE>).
- After this step you should see the following tables:
 --> address_book
 --> address_book_source
 --> customers
 --> customers_source

(6) Change the parameters below to match the TARGET database and upload the file.

(7) Choose a few customers at random from the SOURCE store (using the admin interface) and record their data.
- These will be used to verify a good merge.  Simply edit their account info and print the page.

(8) Call the file in your browser and verify the output.

(9) If the output is acceptable call the merge functionality (click the link).

(10) Verfiy the data merge by comparing the data with the control customers.
- Using the TARGET interface search for the control customers and compare the data from STEP #7.
********************************************************************************
*******************/

/*******************************************
*               PARAMETERS                 *
*******************************************/
//Database settings
$dbasename = '';
$dbuser = '';
$dbpass = '';
//address_book table settings
$target_address_book = 'address_book';
$source_address_book = 'address_book_source';
//customers table settings
$target_customers = 'customers';
$source_customers = 'customers_source';
/*******************************************
*             END PARAMETERS               *
*******************************************/

//DO NOT EDIT BELOW THIS POINT!!!
//*******************************

//Connection info
$connection = mysql_connect("localhost", $dbuser, $dbpass);
$db = @mysql_select_db($dbasename, $connection);

//Select all records from the customer source table
$store_sql="select * from $source_customers order by customers_id";
$store_result=mysql_query($store_sql, $connection);
//Output the merge data link
echo "<center><a href=".$PHP_SELF."?&action=merge>Click here to merge data</a></center><br><hr>";
//Output each customer source record
while ($data = mysql_fetch_array($store_result))
{
//Address book data
$address_book = mysql_query("select * from $source_address_book where customers_id = '$data[customers_id]' order by customers_id"); 
$address_count = mysql_num_rows($address_book);
//Print the source record to screen
echo "<strong>SOURCE RECORD: $data[customers_id]. $data[customers_lastname], $data[customers_firstname] - $data[customers_gender]</strong><br>";
echo "This customer has $address_count source address book entries.<br><br>";
//Select all the matching records from the target table
$mstore_sql ="select * from $target_customers where customers_lastname = '$data[customers_lastname]' and customers_firstname = '$data[customers_firstname]'";
$mstore_result=mysql_query($mstore_sql, $connection);  
//Count the number of  records that match
$count = mysql_num_rows($mstore_result);
//If there are records that match
if ($count != 0){
echo "<blockquote>$count Target Duplicate Records";
   
echo "<ul>";
 //Output each matching record
 while ($row = mysql_fetch_array($mstore_result))  
 {
 echo "<li>".$row[customers_id].". ".$row[customers_lastname].", ".$row[customers_firstname]." - ".$row[customers_gender]."</li>";
 }
echo "</ul></blockquote><hr>";
}
//If there are no matching records
else {
//If the merge command was passed then merge the customer and address_book tables
if ($HTTP_GET_VARS['action'] == 'merge')
 {
 mysql_query("insert into $target_customers (customers_id, customers_gender, customers_firstname, customers_lastname, customers_dob, customers_email_address, customers_default_address_id, customers_telephone, customers_fax, customers_password, customers_newsletter) values ('', '$data[customers_gender]', '$data[customers_firstname]', '$data[customers_lastname]', '$data[customers_dob]', '$data[customers_email_address]', '$data[customers_default_address_id]', '$data[customers_telephone]', '$data[customers_fax]', '$data[customers_password]', '$data[customers_newsletter]')");
 //Get the new customer_id and use that to populate the address book entries
 $new_id = mysql_insert_id();
 //Insert each address book entry associated with the account 
 while ($address_row = mysql_fetch_array($address_book))
 {
 mysql_query("insert into $target_address_book (customers_id, address_book_id, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('$new_id', '$address_row[address_book_id]', '$address_row[entry_gender]', '$address_row[entry_company]', '$address_row[entry_firstname]', '$address_row[entry_lastname]', '$address_row[entry_street_address]', '$address_row[entry_suburb]', '$address_row[entry_postcode]', '$address_row[entry_city]', '$address_row[entry_state]', '$address_row[entry_country_id]', '$address_row[entry_zone_id]')");
 }
 //Output the records merged message
 echo "<blockquote><strong><font color=\"#FF0000\">(Records Merged)</font></strong></blockquote><hr>";
 
 }
 //If the merge command was not passed then just output "No Matching Records"
 else {
	 echo "<blockquote><strong><font color=\"#FF0000\">No Matching Records</font></strong></blockquote><hr>";
	 }
}
}
//Output the merge data link	
echo "<center><a href=".$PHP_SELF."?&action=merge>Click here to merge data</a></center>";	
?>

I've tested it on a test server as well as the production site with no errors (1200+ customers in each table). Hope this helps anyone that needs it...

[Guest]

Just a note to say thank you for putting that together for us!

[d-woo]

Hi Rose,

 

Were you all able to get the order information merged as well?

 

If so, how did you do that?

 

Thanks!

 

:unsure:

[Guest]

It shouldn't be that hard to extend the script to also include the order info.

 

Just add control structures to also query the order tables and insert them into the target tables.

[Guest]

how many orders are you talking? less than a handful or more? if not many, may be easier to key them in, on the new store, you may end up with conflicting customer id's, etc

[gottaloveit]

If you have shell access it might be just as easy to set up master/slave mysql server and then you just make the slave the master when you switch to the new server.

[d-woo]

We're talking about 5,000 orders. Yes we have shell access.

 

------------------------------------------------

 

Initially we had one store on one server.

 

We then got a new server (faster) and copied the store db onto the new server. The store on the old server was never turned off, so older customers that had bookmarked this store continued purchasing.

 

Yep...2 stores on 2 different servers, 2 databases (with some common information). The data in each db is identical from Jan 2003 through Aug 2003. In Sept 2003, when the new store went live, the orders then split.

You will have the same order number in each db which corresponds to 2 different orders.

 

The old store and db have been disabled (Nov 2003)

 

We now want to merge the data from the old server onto the new.

 

We are using MySQL 4.0.15 and are looking into the feasiblity of using the UNION command in combination with a LEFT OUTER JOIN.

 

Of course we are using a snapshot/copy of the live store on the new server along with a copy of the old store.

 

My thinking is that the orders from the old store can be added to the new store with new order_id numbers. The order numbers aren't as important to us as "what" the customers ordered (history).

[onnig]

Hi all,

 

When I try to merge the customer and address_book data it does not work. I click on the link to merge the data and nothing happens. In your instructions you say to change all instances of 'customers' to 'customers_store' but when I do that it says that the table 'customers_store' does not exist. This is not working for me, please help.

[Guest]

Send me your first page or two of the data dumps so I can see your structure. Also, send me the script you are using for the merge.

[onnig]

Hi sorry I haven't gotten back to you. Here are the data dumps:

 

INSERT INTO address_book VALUES (2, 2, 'm', 'TSA Pros', 'XXXX', 'XXXXXX', '1111 E. Anywhere St. #5', '', '99999', 'Glen', '', 223, 12);

 

INSERT INTO customers_source VALUES (2, 'm', 'XXXX', 'XXXXXX', '1974-02-01 00:00:00', 'o@t.com', 2, '555-555-5555', '555-555-5555', 'fq83g9f0a04771d605324dacd9877741:bc', '1', '1000.0000', '0');

 

I only have one record I'm testing. This is a new site. Here is the scripts I'm using, same one on the thread.

 

<style type="text/css">
<!--
body {
font: 12px Verdana, Arial, Helvetica, sans-serif;
background: #000000;
color: #FFFFFF;
}

a {
font: bold 12px Verdana, Arial, Helvetica, sans-serif;
color: #FFFFFF;
}

a:hover {
font: bold 12px Verdana, Arial, Helvetica, sans-serif;
color: #FF0000;
}
-->
</style>
<?
/********************************************************************************
******************
Created by: R. Easland (easlandr@bdcnutrition.com)
This script will import all the customer records from one table into another.
- TABLES AFFECTED: customers, address_book

INSTRUCTIONS:
(1) Data dump the following tables on the target database for backup purposes:
- address_book
- customers

(2) Data dump the same tables from the source database.

(3) Open the <SOURCE> SQL file data dump for customers and change all instances of 'customers' to 'customers_store' using find/replace function.
- Replace the CREATE TABLE instructions with the following and save the file:
 CREATE TABLE `customers_source` (
 `customers_id` int(11) NOT NULL auto_increment,
   `customers_gender` char(1) NOT NULL default '',
   `customers_firstname` varchar(32) NOT NULL default '',
   `customers_lastname` varchar(32) NOT NULL default '',
  	 `customers_dob` datetime NOT NULL default '0000-00-00 00:00:00',
 `customers_email_address` varchar(96) NOT NULL default '',
 `customers_default_address_id` int(5) NOT NULL default '1',
 `customers_telephone` varchar(32) NOT NULL default '',
 `customers_fax` varchar(32) default NULL,
 `customers_password` varchar(40) NOT NULL default '',
 `customers_newsletter` char(1) default NULL,
 PRIMARY KEY  (`customers_id`)
 ) TYPE=MyISAM;
 
(4) Open the <SOURCE> SQL data dump for address_book and change all instances of 'address_book' to 'address_book_store' using the find/replace function.
- Replace the CREATE TABLE instruction with the following and save the file:
 CREATE TABLE `address_book_source` (
   `customers_id` int(11) NOT NULL default '0',
   `address_book_id` int(11) NOT NULL default '1',
   `entry_gender` char(1) NOT NULL default '',
   `entry_company` varchar(32) default NULL,
   `entry_firstname` varchar(32) NOT NULL default '',
   `entry_lastname` varchar(32) NOT NULL default '',
   `entry_street_address` varchar(64) NOT NULL default '',
   `entry_suburb` varchar(32) default NULL,
   `entry_postcode` varchar(10) NOT NULL default '',
   `entry_city` varchar(32) NOT NULL default '',
   `entry_state` varchar(32) default NULL,
   `entry_country_id` int(11) NOT NULL default '0',
   `entry_zone_id` int(11) NOT NULL default '0',
   PRIMARY KEY  (`address_book_id`,`customers_id`)
 ) TYPE=MyISAM;

(5) Add the tables using the SQL upload file function (in the <TARGET DATABASE>).
- After this step you should see the following tables:
 --> address_book
 --> address_book_source
 --> customers
 --> customers_source

(6) Change the parameters below to match the TARGET database and upload the file.

(7) Choose a few customers at random from the SOURCE store (using the admin interface) and record their data.
- These will be used to verify a good merge.  Simply edit their account info and print the page.

(8) Call the file in your browser and verify the output.

(9) If the output is acceptable call the merge functionality (click the link).

(10) Verfiy the data merge by comparing the data with the control customers.
- Using the TARGET interface search for the control customers and compare the data from STEP #7.
********************************************************************************
*******************/

/*******************************************
*               PARAMETERS                 *
*******************************************/
//Database settings
$dbasename = '';
$dbuser = '';
$dbpass = '';
//address_book table settings
$target_address_book = 'address_book';
$source_address_book = 'address_book_source';
//customers table settings
$target_customers = 'customers';
$source_customers = 'customers_source';
/*******************************************
*             END PARAMETERS               *
*******************************************/

//DO NOT EDIT BELOW THIS POINT!!!
//*******************************

//Connection info
$connection = mysql_connect("mysql05.powweb.com", $dbuser, $dbpass);
$db = @mysql_select_db($dbasename, $connection);

//Select all records from the customer source table
$store_sql="select * from $source_customers order by customers_id";
$store_result=mysql_query($store_sql, $connection);
//Output the merge data link
echo "<center><a href=".$PHP_SELF."?&action=merge>Click here to merge data</a></center><br><hr>";
//Output each customer source record
while ($data = mysql_fetch_array($store_result))
{
//Address book data
$address_book = mysql_query("select * from $source_address_book where customers_id = '$data[customers_id]' order by customers_id"); 
$address_count = mysql_num_rows($address_book);
//Print the source record to screen
echo "<strong>SOURCE RECORD: $data[customers_id]. $data[customers_lastname], $data[customers_firstname] - $data[customers_gender]</strong><br>";
echo "This customer has $address_count source address book entries.<br><br>";
//Select all the matching records from the target table
$mstore_sql ="select * from $target_customers where customers_lastname = '$data[customers_lastname]' and customers_firstname = '$data[customers_firstname]'";
$mstore_result=mysql_query($mstore_sql, $connection);  
//Count the number of  records that match
$count = mysql_num_rows($mstore_result);
//If there are records that match
if ($count != 0){
echo "<blockquote>$count Target Duplicate Records";
   
echo "<ul>";
 //Output each matching record
 while ($row = mysql_fetch_array($mstore_result))  
 {
 echo "<li>".$row[customers_id].". ".$row[customers_lastname].", ".$row[customers_firstname]." - ".$row[customers_gender]."</li>";
 }
echo "</ul></blockquote><hr>";
}
//If there are no matching records
else {
//If the merge command was passed then merge the customer and address_book tables
if ($HTTP_GET_VARS['action'] == 'merge')
 {
 mysql_query("insert into $target_customers (customers_id, customers_gender, customers_firstname, customers_lastname, customers_dob, customers_email_address, customers_default_address_id, customers_telephone, customers_fax, customers_password, customers_newsletter) values ('', '$data[customers_gender]', '$data[customers_firstname]', '$data[customers_lastname]', '$data[customers_dob]', '$data[customers_email_address]', '$data[customers_default_address_id]', '$data[customers_telephone]', '$data[customers_fax]', '$data[customers_password]', '$data[customers_newsletter]')");
 //Get the new customer_id and use that to populate the address book entries
 $new_id = mysql_insert_id();
 //Insert each address book entry associated with the account 
 while ($address_row = mysql_fetch_array($address_book))
 {
 mysql_query("insert into $target_address_book (customers_id, address_book_id, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('$new_id', '$address_row[address_book_id]', '$address_row[entry_gender]', '$address_row[entry_company]', '$address_row[entry_firstname]', '$address_row[entry_lastname]', '$address_row[entry_street_address]', '$address_row[entry_suburb]', '$address_row[entry_postcode]', '$address_row[entry_city]', '$address_row[entry_state]', '$address_row[entry_country_id]', '$address_row[entry_zone_id]')");
 }
 //Output the records merged message
 echo "<blockquote><strong><font color=\"#FF0000\">(Records Merged)</font></strong></blockquote><hr>";
 
 }
 //If the merge command was not passed then just output "No Matching Records"
 else {
	 echo "<blockquote><strong><font color=\"#FF0000\">No Matching Records</font></strong></blockquote><hr>";
	 }
}
}
//Output the merge data link	
echo "<center><a href=".$PHP_SELF."?&action=merge>Click here to merge data</a></center>";	
?>

 

Thanks of your help

[Guest]

if you have them in parralel db's either export the tables needed from the old one and rename the table name before you import it to the new one. Or write an import script that can select from multiple db's to pull info from one and place it into the other. But for the import part of the script have it select * from the old table(s) and then search the new one in a while or for loop function. while it is looping compare records, and if the script can not find a matching record have it insert the data into the right tables with out the orders_id or do the tep_db_perform(); If its the orders your importing remember you have to import the orders table, the orders_history_status and the orders_products, and orders_total tables all at the same time so you would do a tep_db_insert_id(); to get the orders_id for the other tables.

[Guest]

Try the code below.

 

I've cleaned it up just a bit and added the convenience of pulling from two databases (instead of the SQL dump method). I originally created the script for a client in about 2 hours and it's obviously a one time use type of script...so I never really coded the original for convenience.

 

Revisions:

(1) Easier to use.

(2) Added customers_info to the tables being imported.

(3) Records that need to be merged are highlighted for easy identification.

(4) Links added for either View Records or Merge Records

(5) Header bar outputs the source and target database names.

 

To Do List:

(1) Complete orders import.

(2) Implement a selectable menu system to define databases and tables.

 

If anyone wants to help code the contribution it may be completed in a more expeditious manner than if I have to do it myself.

<?
/********************************************************************************
******************

 merge.php,v 1.1 2004/01/21

 Created by: R. Easland (easlandr@bdcnutrition.com)

This script was written to import the records from one database into another.  It only imports the records
from customers, customers_info, and address_book.  At this time, THE SCRIPT DOES NOT IMPORT THE ORDER DATA!

REQUIREMENTS:
(1) Databases are on the same server.
(2) User has access to both databases. i.e. - User account must be valid for both databases (username and password).
- Simply add the SAME user to both databases via domain control panel.

INSTRUCTIONS:
(1) Change the parameters below to database settings.

(2) Call the script in your browswer and verify data.
- If there are no matching records then that record will be imported.

(3) Click the merge link to merge the records.

(4) Verify the merge in the AdminCP.
********************************************************************************
*******************/

/*******************************************
*               PARAMETERS                 *
*******************************************/
//User and password settings.
$CFG_dbase = array(
  	 "dbuser" => '',
  	 "dbpass" => ''
  	 );

//SOURCE database name
$CFG_source = array("dbasename" => '');

//TARGET database name
$CFG_target = array ("dbasename" => '');

/*******************************************
*             END PARAMETERS               *
*******************************************/

//DO NOT EDIT BELOW THIS POINT!!!
//*******************************
?>
<style type="text/css">
<!--
body {
font: 12px Verdana, Arial, Helvetica, sans-serif;
background: #000000;
color: #FFFFFF;
}

td {
font: 12px Verdana, Arial, Helvetica, sans-serif;
background: #000000;
color: #FFFFFF;
}

.alt {
font: 12px Verdana, Arial, Helvetica, sans-serif;
background: #666666;
color: #FFFFFF;
}

a {
font: bold 12px Verdana, Arial, Helvetica, sans-serif;
color: #FFFFFF;
}

a:hover {
font: bold 12px Verdana, Arial, Helvetica, sans-serif;
color: #FF0000;
}
-->
</style>
<table width="100%"  border="1" bordercolor="#FF0000" align="center" cellpadding="0" cellspacing="0">
 <tr> 
   <td class="alt"><div align="center">
Target database -> <? echo $CFG_target['dbasename'] ?><br>
Source database -> <? echo $CFG_source['dbasename'] ?>
</div></td>
 </tr>
</table>
<br>
<center><a href="<? echo $PHP_SELF ?>">View Data Comparison</a> | <a href="<? echo $PHP_SELF ?>"?&action=merge>Merge Tables</a></center><br>
<?
$connection = mysql_connect("localhost", $CFG_dbase['dbuser'], $CFG_dbase['dbpass']); //Connection info

mysql_select_db($CFG_source['dbasename'], $connection); //Select the SOURCE database
$source_result=mysql_query("SELECT * from customers ORDER BY customers_id", $connection); //Select ALL records from the SOURCE database

while ($data = mysql_fetch_array($source_result)) //Loop through the records
{
mysql_select_db($CFG_target['dbasename'], $connection); //Change to TARGET database
$target_result = mysql_query("select * from customers where customers_lastname = '$data[customers_lastname]' and customers_firstname = '$data[customers_firstname]'", $connection); //Query target database for matching records  
$count = mysql_num_rows($target_result); //Count the number of  records that match
if ($count == 0){echo "<table width=\"100%\" border=\"1\"><tr><td class=\"alt\" bordercolor=\"#FFOOOO\">";} else {echo "<table width=\"100%\"><tr><td bordercolor=\"#CCCCCC\">";}

//Address book data
mysql_select_db($CFG_source['dbasename'], $connection); //Change to SOURCE database
$address_book = mysql_query("select * from address_book where customers_id = '$data[customers_id]' order by customers_id", $connection); //Select ALL records from the address_book table that match ID's 
$address_count = mysql_num_rows($address_book); //Count the $address_book records  
$customers_info = mysql_query("select * from customers_info where customers_info_id = '$data[customers_id]' order by customers_info_id", $connection); //Select ALL records from the customer_info table that match ID's 
$customers_info_count = mysql_num_rows($customers_info); //Count the $customer_info records  
   
//Print the source record to screen
echo "<br><strong>SOURCE RECORD: $data[customers_id]. $data[customers_lastname], $data[customers_firstname] - $data[customers_gender]</strong><br>";
echo "This customer has $address_count source address book entries.<br>"; //Output the number of address book entries
echo "This customer has $customers_info_count source customers_info entries.<br><br>"; //Output the number of customers_info entries

if ($count !== 0){ //If there are records that match
echo "<blockquote>$count Target Duplicate Records";    
echo "<ul>"; //Start a list
 while ($row = mysql_fetch_array($target_result))//Output each matching record  
 {
 echo "<li>".$row['customers_id'].". ".$row['customers_lastname'].", ".$row['customers_firstname']." - ".$row['customers_gender']."</li>";
 }
echo "</ul></blockquote>";
}  

else { //If there are no matching records
if ($HTTP_GET_VARS['action'] == 'merge') //If the merge command was passed then merge the tables
 {
 mysql_select_db($CFG_target['dbasename'], $connection); //Change to TARGET database
 mysql_query("insert into customers (customers_id, customers_gender, customers_firstname, customers_lastname, customers_dob, customers_email_address, customers_default_address_id, customers_telephone, customers_fax, customers_password, customers_newsletter) values ('', '$data[customers_gender]', '$data[customers_firstname]', '$data[customers_lastname]', '$data[customers_dob]', '$data[customers_email_address]', '$data[customers_default_address_id]', '$data[customers_telephone]', '$data[customers_fax]', '$data[customers_password]', '$data[customers_newsletter]')"); //Insert the record
 $new_id = mysql_insert_id(); //Get the new customer_id and use that to populate the address book entries
 while ($address_row = mysql_fetch_array($address_book)) //Insert each address book entry associated with the account
 {
 mysql_query("insert into address_book (customers_id, address_book_id, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('$new_id', '$address_row[address_book_id]', '$address_row[entry_gender]', '$address_row[entry_company]', '$address_row[entry_firstname]', '$address_row[entry_lastname]', '$address_row[entry_street_address]', '$address_row[entry_suburb]', '$address_row[entry_postcode]', '$address_row[entry_city]', '$address_row[entry_state]', '$address_row[entry_country_id]', '$address_row[entry_zone_id]')");
 }
 while ($customers_info_row = mysql_fetch_array($customers_info))//Insert each entry associated with the account
 { 
 mysql_query("insert into customers_info (customers_info_id, customers_info_date_of_last_logon, customers_info_number_of_logons, customers_info_date_account_created, customers_info_date_account_last_modified, global_product_notifications) values ('$new_id', '$customers_info_row[customers_info_date_of_last_logon]', '$customers_info_row[customers_info_number_of_logons]', '$customers_info_row[customers_info_date_account_created]', '$customers_info_row[customers_info_date_account_last_modified]', '$customers_info_row[global_product_notifications]')");
 }
 //Output the records merged message
 echo "<blockquote><strong><font color=\"#FF0000\">(Records Merged)</font></strong></blockquote>";
 
 }
 //If the merge command was not passed then just output "No Matching Records"
 else {
	 echo "<blockquote><strong><font color=\"#FF0000\">No Matching Records - Needs to be Merged</font></strong></blockquote>";
	 }
}
echo "</td></tr></table>";
}
?>
<center><a href="<? echo $PHP_SELF ?>">View Data Comparison</a> | <a href="<? echo $PHP_SELF ?>"?&action=merge>Merge Tables</a></center><br>

[onnig]

Thanks to both for the replies. I would like to use this new script although my databases are on two different servers with two different sets of usernames and password and the database names are different. Is it too much to ask if anybody and code this into this script?

[Guest]

I know this is not pretty but will get the job done: every query you do just call it by function like $thing = query_new("select * from it");

 

 

but heres the functions, you could choose to mysql_close(); the functions too but this is raw and not clean

 

function query_old($query){

 

$link = mysql_connect("mysql_old_host", "mysql_login", "mysql_password")

or die ("Could not connect to MySQL");

 

mysql_select_db ("my_database")

or die ("Could not select database");

mysql_query($query, $link);

}

 

function query_new($query){

 

$link2 = mysql_connect("mysql_new_host", "mysql_login", "mysql_password")

or die ("Could not connect to MySQL");

 

mysql_select_db ("my_database")

or die ("Could not select database");

mysql_query($query, $link2);

[Guest]

sorry, just relized I left a } out of that last function. heres it with the } and I cleaned it uop a bit

 

function query_old($query){

$link = mysql_connect("mysql_old_host", "mysql_login", "mysql_password");

mysql_select_db ("my_database");

mysql_query($query, $link);

}

 

function query_new($query){

$link2 = mysql_connect("mysql_new_host", "mysql_login", "mysql_password");

mysql_select_db ("my_database");

mysql_query($query, $link2);

}

[Guest]

Maybe create a database class?

[onnig]

Hi,

 

I'm using the script from my original post. The customer data is going through but when a customer tries to buy something on the other shopping cart their address does not exist on their account, they need to fill it in on the new shopping cart. It seems as though the address book does not go through.