Solved

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

Posted on 2014-01-21
9
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39799857
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
ID: 39802485
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 110

Expert Comment

by:Ray Paseur
ID: 39802905
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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 

Author Comment

by:sabecs
ID: 39805414
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39806508
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 110

Expert Comment

by:Ray Paseur
ID: 39806558
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 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39806603
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
ID: 39807928
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 110

Expert Comment

by:Ray Paseur
ID: 39808081
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

728 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