Solved

MySQL - copy information from a remote users table to my local users table.

Posted on 2014-01-21
9
364 Views
Last Modified: 2014-01-24
Hi,
I need to copy information from a remote users table to my local users table.

I only want users who's email address does not match any records on my local table and also set the `user_groups_id` to 17 for users added?

Is their an easy way to do this and add it to my?


CREATE TABLE IF NOT EXISTS `users` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `email` varchar(90) DEFAULT NULL,
  `contact` varchar(40) DEFAULT NULL,
  `First_Name` varchar(120) DEFAULT NULL,
  `Last_Name` varchar(120) DEFAULT NULL,
  `Phone` varchar(30) DEFAULT NULL,
  `Fax` varchar(40) DEFAULT NULL,
  `comments` text,
  `send` char(3) DEFAULT 'N',
  `bounced` tinyint(3) DEFAULT '0',
  `user_type` enum('C','S','R','N') DEFAULT 'C',
  `user_groups_id` tinyint(3) DEFAULT '2',
  `Business_Name` varchar(150) DEFAULT NULL,
  `ACN_ABN` varchar(50) DEFAULT NULL,
  `Registered_Address_1` varchar(150) DEFAULT NULL,
  `Registered_Address_2` varchar(150) DEFAULT NULL,
  `Registered_Suburb_Town` varchar(80) DEFAULT NULL,
  `Registered_State_Province` varchar(80) DEFAULT NULL,
  `Registered_Country` varchar(80) DEFAULT NULL,
  `Registered_Zip_Post_Code` varchar(12) DEFAULT NULL,
  `Trading_Address_1` varchar(150) DEFAULT NULL,
  `Trading_Address_2` varchar(150) DEFAULT NULL,
  `Trading_Suburb_Town` varchar(80) DEFAULT NULL,
  `Trading_State_Province` varchar(80) DEFAULT NULL,
  `Trading_Country` varchar(80) DEFAULT NULL,
  `Trading_Zip_Post_Code` varchar(12) DEFAULT NULL,
  `Shipping_Address_1` varchar(150) DEFAULT NULL,
  `Shipping_Address_2` varchar(150) DEFAULT NULL,
  `Shipping_Suburb_Town` varchar(80) DEFAULT NULL,
  `Shipping_State_Province` varchar(80) DEFAULT NULL,
  `Shipping_Country` varchar(80) DEFAULT NULL,
  `Shipping_Zip_Post_Code` varchar(12) DEFAULT NULL,
  `security_question` tinyint(4) DEFAULT NULL,
  `security_answer` varchar(120) DEFAULT NULL,
  `random_key` varchar(16) DEFAULT NULL,
  `Activate` enum('Y','N') DEFAULT 'N',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=5093 ;

should I use a loop like below?

$nz_conn_data = mysql_pconnect($nz_hostname, $nz_db_user_name, $nz_db_password) or die(mysql_error());

mysql_select_db($nz_db_name, $nz_conn_data);
$list_nz_users_table = "SELECT * FROM `users`";
$list_nz_users = mysql_query($list_nz_users_table, $nz_conn_data) or die(mysql_error());
$row_nz_users = mysql_fetch_assoc($list_nz_users);

$local_conn_data = mysql_pconnect($local_hostname, $local_db_user_name, $local_db_password) or die(mysql_error());

mysql_select_db($local_db_name, $local_conn_data);
$list_local_users_table = "SELECT * FROM `users`";
$list_local_users = mysql_query($list_local_users_table, $local_conn_data) or die(mysql_error());
$row_local_users = mysql_fetch_assoc($list_local_users);
	
do{
	
//check here if users enmail exist, if not found add user and alter `user_groups_id` to 17

}while ($row_nz_users = mysql_fetch_assoc($list_nz_users));

Open in new window

0
Comment
Question by:sabecs
  • 6
  • 3
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Step One is probably "avoid MySQL" because PHP is doing away with MySQL support.  This article explains why and what you must do to keep your scripts running.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

To the question of application design, can you get to both tables and are they on the same server?  Are they in different data bases?  

SQL queries identify columns in a hierarchy like this:

SELECT databaseName.tableName.columnName FROM...

Because the data base is often selected separately we don't often see it in the query, but it's easy to put it in there.  This is especially helpful when you have to write queries that run across two data bases at the same time.
0
 

Author Comment

by:sabecs
Comment Utility
Thanks Ray for the advice, I have a lot of reading to do and code to update.
But, I need a quick fix for now...

I basically have tables of the same name and same database name but on different websites/servers. I need to copy the info from website 2, website 3, website 4 etc to website 1 database but want to just change one field "user_groups_id" along the way.

Website 1 will have table info from website 2 and set user_groups_id = 2;  
Website 1 will have table info from website 3 and set user_groups_id = 3;
I hope this makes sense?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
If the tables are on different servers, under different URLs you will have to copy the data between servers.  You can use phpMyAdmin to create an export copy of a data base or of just a table.  You can use a PHP script to read and modify the contents of the export file, or you can create "shadow copies" of the tables on website 1, under different names, then you can alter the tables to change the user_groups_id so that it reflects the web site of origin,

It's a process that will take a little while.  If you wanted to automate it (advantage: more timely copies), you would have to write your own data base export program (an API) for website 2, website 3, etc.  Then a script running on website 1 could call the export API to receive the data.  As each table is received, the script on website 1 would set the user_groups_id column and INSERT the rows into a new table representing the merged data from the other sites.  

Since this process will expose your data to a GET request, you will want to have some kind of sturdy handshake between the servers.  Choose a good API key and keep it a secret.  You can also check HTTP_REFERER.  These are relatively modest security measures, and the best security will be to disable the scripts after they have been used.

You may also want to make an md5() digest of the data you're transmitting.  It will help you know whether it all got to server 1 intact.

Some information on APIs is available here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12239-Introduction-to-Application-Programming-Interfaces.html
0
 

Author Comment

by:sabecs
Comment Utility
Thanks again Ray but I can't I just do something like this?

	$nz_conn_data = mysql_pconnect($nz_hostname, $nz_db_user_name, $nz_db_password) or die(mysql_error());

	mysql_select_db($nz_db_name, $nz_conn_data);
	$list_nz_users_table = "SELECT * FROM `users`";
	$list_nz_users = mysql_query($list_nz_users_table, $nz_conn_data) or die(mysql_error());
	$nz_users = mysql_fetch_assoc($list_nz_users);

	$local_conn_data = mysql_pconnect($local_hostname, $local_db_user_name, $local_db_password) or die(mysql_error());

	mysql_select_db($local_db_name, $local_conn_data);
	$list_local_users_table = "SELECT email FROM `users`";
	$list_local_users = mysql_query($list_local_users_table, $local_conn_data) or die(mysql_error());
	$local_users = mysql_fetch_assoc($list_local_users);
	
	
	//loop through remote nz table
	do{
	
		$nz_email = $row_nz_users['email;
		$nz_username = $row_nz_users['username];
		$nz_password = $row_nz_users['password'];
	        $nz_user_groups_id = '17';
		...
		
	    
	    //loop through local table
	    do{
	
		//check here if nz users email exist in local table, if not found add user
		if (in_array($nz_email , $local_users , FALSE)){
		
		//if remote users email not located in local table then insert new user 
		mysql_select_db($local_db_name, $local_conn_data);
		$add_users = "INSERT INTO users (email, username, password, user_group_id,...) VALUES ($nz_email, $nz_username, $nz_password, '17'...)";
		$add = mysql_query($add_users, $local_conn_data) or die(mysql_error());
		}
			
		
		}


	    }while ($row_local_users = mysql_fetch_assoc($list_local_users);)


	}while ($row_nz_users = mysql_fetch_assoc($list_nz_users));

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
That strategy appears mostly workable if the data bases are available on the same server.  That was not clear to me earlier.

I think I would avoid the use of pconnect() and just choose mysql_connect().

You probably want to check the loops and iterators carefully.  The code looks like it might lose a row here and there.  I'll see if I can show you an alternative script in a little while.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Well, I looked at it and there are a lot of data-dependencies in the design.  Important questions:

1. How much data is involved?  When you SELECT * FROM `users` how many rows do you get and how big is each row?  Can you be confident that you will not run out of memory if you load all of the rows into memory at the same time?

2. Are the email addresses unique?  I don't mean necessarily marked UNIQUE as an index.  I'm just looking to see if we can use the email address in a WHERE clause and expect to match only one row.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
Depending on the answers to the questions, I might use something like this.

<?php 
error_reporting(E_ALL);

// CONNECT TO A DATABASE SERVER
$nz_conn = mysql_connect($nz_hostname, $nz_db_user_name, $nz_db_password) or trigger_error(mysql_error(), E_USER_ERROR);

// SELECT THE DATA BASE
mysql_select_db($nz_db_name, $nz_conn);

// RUN QUERY AND GET RESULTS SET INTO AN ARRAY OF OBJECTS INDEXED BY EMAIL ADDRESS
$nz_sql = "SELECT * FROM `users`";
$nz_res = mysql_query($nz_sql) or trigger_error(mysql_error(), E_USER_ERROR);
while ($nz_row = mysql_fetch_object($nz_res)) $nz_rows[$nz_row->email] = $nz_row;

// CONNECT TO ANOTHER DATABASE SERVER
$lc_conn = mysql_connect($local_hostname, $local_db_user_name, $local_db_password) or trigger_error(mysql_error(), E_USER_ERROR);

// SELECT A DIFFERENT DATA BASE
mysql_select_db($local_db_name, $lc_conn);

// RUN QUERY AND GET ANOTHER ARRAY OF OBJECTS, SIMILARLY INDEXED
$lc_sql = "SELECT email FROM `users`";
$lc_res = mysql_query($lc_sql) or trigger_error(mysql_error(), E_USER_ERROR);
while ($lc_row = mysql_fetch_object($lc_res)) $lc_rows[$lc_row->email] = $lc_row;

// REMOVE THE OBJECTS FROM THE "NZ" ARRAY IF THEY ARE PRESENT IN THE LOCAL ARRAY
$diffs = array_diff_keys($nz_rows, $lc_rows);

// ADD THE REMANING DATA TO THE LOCAL TABLE
foreach ($diffs as $nz_row)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $email    = mysql_real_escape_string($nz_row->email);
    $username = mysql_real_escape_string($nz_row->username);
    $password = mysql_real_escape_string($nz_row->password);
        
    // SHOULD THIS BE HARDWIRED OR COPIED FROM THE OTHER DATA BASE TABLE???
    $user_groups_id = '17';

    // CREATE THE QUERY STRING
    $up_sql = "INSERT INTO users (email, username, password, user_group_id) VALUES ('$email', '$username', '$password', '17')";
    $up_res = mysql_query($up_sql) or trigger_error(mysql_error(), E_USER_ERROR);
}

Open in new window

HTH, ~Ray
0
 

Author Closing Comment

by:sabecs
Comment Utility
Thanks Ray, that is fantastic...very much appreciated.

Only problem I have is that I receive an error "Call to undefined function array_diff_keys" which I will create a work around..

Cheers,

Andrew
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
That's the problem with not having a test plan!

http://php.net/manual/en/function.array-diff-key.php

Function name spelled wrong on my part!

Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now