[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Use PHP to export mysql table rows from dbA to dbB - separate user/pass for each db

Posted on 2006-05-04
14
Medium Priority
?
204 Views
Last Modified: 2013-12-12
I'm trying to get info from one table into another. Each table is from a seperate mySQL database, each having its own user/pass.

INSERT INTO table_A(id_A,email_A) SELECT id_B,email_B FROM table_B WHERE activated='1'

Is there a way to accomplish this from within PHP? I can't shut down the server or use phpMyAdmin. It has to be from within my current script.

I would prefer not writing a file to the HD if that's possible.
0
Comment
Question by:tgavin
  • 6
  • 5
  • 3
14 Comments
 
LVL 9

Expert Comment

by:LinuxNubb
ID: 16605915
you'll just have to make 2 connection identifiers like:

$hostname = "127.0.0.1";

$dbusername1 = "someuser";
$password1 = "somepass";
$dbname1 = "somedb";

$dbusername2 = "someotheruser";
$password2 = "someotherpass";
$dbname2 = "someotherdb";

$connect1 = mysql_connect($hostname, $dbusername1, $password1) or die ("Unable to connect to server.");
$select_db1 = mysql_select_db($dbname1) or die ("Unable to open database.");

$connect2 = mysql_connect($hostname, $dbusername2, $password2) or die ("Unable to connect to server.");
$select_db2 = mysql_select_db($dbname2) or die ("Unable to open database.");

And then select your data, referencing your first connection identifier
$query1 = "SELET * from tablename..."
mysql_query($query1, $connect1);

Grab your data and build your query:
Then insert with 2nd connection:
$query2 = "INSERT INTO table....";
mysql_query($query2, $connect2);


0
 

Author Comment

by:tgavin
ID: 16607996
I've already gone through this previously and can't figure out where to store the result from $query1.
That's why my original query was selecting and inserting in one go. It works great for two tables within the same db, but won't work with different u/p.

See, my problem is not knowing how or where to store the data from the query and then switching connections and inserting.

In your example, where and how is the data from $query1 stored?
0
 
LVL 9

Assisted Solution

by:LinuxNubb
LinuxNubb earned 200 total points
ID: 16608803
$hostname = "127.0.0.1";

$dbusername1 = "someuser";
$password1 = "somepass";
$dbname1 = "somedb";

$dbusername2 = "someotheruser";
$password2 = "someotherpass";
$dbname2 = "someotherdb";

$connect1 = mysql_connect($hostname, $dbusername1, $password1) or die ("Unable to connect to server.");
$select_db1 = mysql_select_db($dbname1) or die ("Unable to open database.");

$connect2 = mysql_connect($hostname, $dbusername2, $password2) or die ("Unable to connect to server.");
$select_db2 = mysql_select_db($dbname2) or die ("Unable to open database.");

And then select your data, referencing your first connection identifier
$query1 = "SELET * from tablename..."
mysql_query($query1, $connect1);

// loop through result set
while ($row = mysql_fetch_assoc($query1)) {
     $col1 = $row['col1'];  /// or whatever your column name is
     $col2 = $row['col2'];  /// and on and on
   
     $query2 = "INSERT INTO table (col1,col2) VALUES ('$col1','$col2');  /// or whatever fields you are inserting
     mysql_query($query2, $connect2);

     unset ($col1, $col2);  // unset variables to ensure data integrity

}
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:tgavin
ID: 16608844
Wouldn't that mean a SELECT and INSERT for every record? if I have 100,000 records that would be way too many queries.
0
 
LVL 9

Expert Comment

by:LinuxNubb
ID: 16608934
INSERT INTO `db2`.`table2` SELECT * FROM `db1`.`table1` ;
0
 

Author Comment

by:tgavin
ID: 16609143
That's basically the same as my original post.
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16610119
>>Wouldn't that mean a SELECT and INSERT for every record? if I have 100,000 records that would be way too many queries.

No - that would mean 1 SELECT and 100,000 inserts, I've done about 50000 record inserts from a PHP script at one time with no issues.

It is worth noting that you can INSERT more the one row with one INSERT statement: http://dev.mysql.com/doc/refman/4.1/en/insert-speed.html
You cannot INSERT all the rows in one statement because you would exceed the MAX_PACKET_SIZE in MySQL, but maybe you could do 20 at a time.  Let me know if it works:

<?php
$hostname = "127.0.0.1";

$dbusername1 = "someuser";
$password1 = "somepass";
$dbname1 = "somedb";

$dbusername2 = "someotheruser";
$password2 = "someotherpass";
$dbname2 = "someotherdb";

$connect1 = mysql_connect($hostname, $dbusername1, $password1) or die ("Unable to connect to server.");
$select_db1 = mysql_select_db($dbname1) or die ("Unable to open database.");

$connect2 = mysql_connect($hostname, $dbusername2, $password2) or die ("Unable to connect to server.");
$select_db2 = mysql_select_db($dbname2) or die ("Unable to open database.");

//And then select your data, referencing your first connection identifier
$query1 = "SELET * from tablename..."
$res = mysql_query($query1, $connect1);
$num_of_inserts = 20; //set number of simultaneous INSERTS
$numrows = mysql_num_rows($res);
$numiter = ceil($numrows / $num_of_inserts);
$i = 0;
// loop through result set
while ($i < $numiter) {
       $i++;
       $a = 0;
       unset($row, $temp, $key, $value, $dataArray, $data);
       $fieldstring = "";
       while($a < $num_of_inserts){
            if($row = mysql_fetch_assoc($query1)){ //make sure there's still data left
                  foreach($row as $key => $value){
                        $temp[$key][] = $value; //store the data vertically in the array
                  }
            }
            $a++;
       }
       foreach($temp as $key => $dataArray){
            $fieldstring .= "(";
            foreach($dataArray as $data){
                  $fieldstring .= "'" . $data . "',";
            }
            rtrim($fieldstring,","); //remove last comma
            $fieldstring .= "),";
       }
       rtrim($fieldstring,","); // remove last comma
       $query2 = "INSERT INTO table VALUES ".$fieldstring;  /// or whatever fields you are inserting
       mysql_query($query2, $connect2) or die("Error with query: <hr>$query2<hr>Details: ".mysql_error());
       $affected = mysql_affected_rows();
       echo "Query Iteration $i: updated $affected row(s), ".($numiter - $i)." remaining<br>";
       unset($col1, $col2);  // unset variables to ensure data integrity
}
?>

I haven't checked this script but if it is ok it will be a work of art!  You can change how many rows it does at a time - check the comments.
0
 

Author Comment

by:tgavin
ID: 16615199
Okay, it's REAL close! After some tweaking it's now inserting into the db, with one problem; some of the email addresses are replaced by id numbers.

If I echo $insert I receive the following:
INSERT INTO send_queue (queue_id, queue_email) VALUES ('1','3'),('gernblanston@aol.com','fredflintstone@aol.com')

So I figured I'd try removing the id numbers from the SELECT query and the table and then trying again, and I get a 'column count doesn't match row count' error (Whaaa? it's only 1 field!)

$sub_conn = mysql_pconnect($sub_hostname, $sub_username, $sub_password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($sub_database, $sub_conn);

$conn = mysql_pconnect($hostname, $db_username, $db_password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database, $conn);

// select your data, referencing your first connection identifier
$select = "SELECT ".SUB_ID.",".SUB_EMAIL." FROM ".SUB_TABLE." WHERE ".SUB_ACTIVATED." = '1'";
$res = mysql_query($select, $sub_conn);
$num_of_inserts = 2; // set number of simultaneous inserts
$numrows = mysql_num_rows($res);
$numiter = ceil($numrows / $num_of_inserts);
$i = 0;
while($i < $numiter){
      $i++;
      $a = 0;
      unset($row, $temp, $key, $value, $dataArray, $data);
      $fieldstring = '';
      while($a < $num_of_inserts){
            if($row = mysql_fetch_assoc($res)){ // make sure there's still data left
                  foreach($row as $key => $value){
                        $temp[$key][] = $value; // store the data vertically in the array
                  }
            }
            $a++;
      }
      
      foreach($temp as $key => $dataArray){
            $fieldstring .= '(';
            foreach($dataArray as $data){
            $fieldstring .= "'" . $data . "',";
      }
      
            rtrim($fieldstring, ','); // remove last comma
            $fieldstring = rtrim($fieldstring, ',');
            $fieldstring .= '),';
      }

      $insert = "INSERT INTO send_queue (queue_id, queue_email) VALUES " . rtrim($fieldstring, ',');
      mysql_query($insert, $conn) or die("Error with query: <hr>$insert<hr>Details: " . mysql_error());
      $affected = mysql_affected_rows();
      echo "Query Iteration $i: updated $affected row(s), ".($numiter - $i)." remaining<br>";
      unset($col1, $col2); // unset variables to ensure data integrity
}
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16615334
There must be something strange about the data in the rows where the email is being replaced.  Can you figure out which rows are doing this?  Maybe we need to do addslashes($value) to the data before we re-insert it or something.
0
 

Author Comment

by:tgavin
ID: 16615994
The data is fine. I truncated the table and inserted 5 new (minimal) records with phpMyAdmin just to be sure. I've also turned on magic_quotes just in case (they were off before).

So that you can see everything, I've exported the data from the SELECT table

CREATE TABLE `users` (
  `usr_id` int(11) NOT NULL auto_increment,
  `usr_fname` varchar(30) NOT NULL default '',
  `usr_lname` varchar(30) NOT NULL default '',
  `usr_email` varchar(35) NOT NULL default '',
  `usr_title` varchar(35) default NULL,
  `usr_hospital` varchar(35) default NULL,
  `usr_phone` varchar(12) default NULL,
  `usr_fax` varchar(12) default NULL,
  `usr_address` varchar(60) default NULL,
  `usr_city` varchar(35) default NULL,
  `usr_state` tinyint(4) default '0',
  `usr_zip` varchar(10) default NULL,
  `usr_added_by` varchar(30) NOT NULL default '',
  `usr_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `usr_activated` enum('0','1') NOT NULL default '0',
  PRIMARY KEY  (`usr_id`)
) TYPE=MyISAM;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1, '', '', 'test1@mac.com', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '', '0000-00-00 00:00:00', '1');
INSERT INTO `users` VALUES (2, '', '', 'test2@mac.com', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '', '0000-00-00 00:00:00', '1');
INSERT INTO `users` VALUES (3, '', '', 'test3@mac.com', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '', '0000-00-00 00:00:00', '1');
INSERT INTO `users` VALUES (4, '', '', 'test4@mac.com', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '', '0000-00-00 00:00:00', '1');
INSERT INTO `users` VALUES (5, '', '', 'test5@mac.com', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '', '0000-00-00 00:00:00', '1');

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

Now here's what I get after running the script

CREATE TABLE `send_queue` (
  `queue_id` int(11) NOT NULL auto_increment,
  `queue_email` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`queue_id`)
) TYPE=MyISAM COMMENT='Contains the subscribers that will receive email';

--
-- Dumping data for table `send_queue`
--

INSERT INTO `send_queue` VALUES (1, '2');
INSERT INTO `send_queue` VALUES (2, 'test2@mac.com');
INSERT INTO `send_queue` VALUES (3, '4');
INSERT INTO `send_queue` VALUES (4, 'test4@mac.com');
0
 
LVL 13

Accepted Solution

by:
kamermans earned 1800 total points
ID: 16616150
I had a major misunderstanding of the syntax for multiple row inserts and have corrected it - this is some cool code!:

<?php
$debug = false;

$hostname1 = "127.0.0.1";
$hostname2 = "127.0.0.1";

$dbusername1 = "zipuser";
$password1 = "zippass";
$dbname1 = "teraadm_terazip";

$dbusername2 = "zipuser";
$password2 = "zippass";
$dbname2 = "teraadm_terazip";

$connect1 = mysql_connect($hostname1, $dbusername1, $password1) or die ("Unable to connect to server.");
$select_db1 = mysql_select_db($dbname1) or die ("Unable to open database.");

$connect2 = mysql_connect($hostname2, $dbusername2, $password2) or die ("Unable to connect to server.");
$select_db2 = mysql_select_db($dbname2) or die ("Unable to open database.");

//And then select your data, referencing your first connection identifier
$query1 = "SELECT * FROM zipcodes WHERE 1";
$res = mysql_query($query1, $connect1) or die(mysql_error());
$num_of_inserts = 20; //set number of simultaneous INSERTS
$numrows = mysql_num_rows($res);
$numiter = ceil($numrows / $num_of_inserts);
$i = 0;
$totalaffected = 0;
$start = microtime_float();
// loop through result set
while ($i < $numiter) {
       $i++;
       $a = 0;
       unset($row, $temp, $key, $value, $dataArray, $data);
       $fieldstring = "";
       while($a < $num_of_inserts){
            if($row = @mysql_fetch_assoc($res)){ //make sure there's still data left
                  $fieldstring .= "(";
                  foreach($row as $key => $value){
                        $fieldstring .= "'" . addslashes($value) . "',";                                                
                  }
                  $fieldstring = rtrim($fieldstring,","); //remove last comma
                  $fieldstring .= "),";
            }
            $a++;
       }
       $fieldstring = rtrim($fieldstring,","); // remove last comma
       $query2 = "INSERT INTO newTable VALUES ".$fieldstring;  /// or whatever fields you are inserting
       mysql_query($query2, $connect2) or die("Error with query: <hr>$query2<hr>Details: ".mysql_error());
       $affected = mysql_affected_rows();
       $totalaffected += $affected;
       if($debug){echo "Query Iteration $i: updated $affected row(s), ".($numiter - $i)." remaining<br>";}
       unset($col1, $col2);  // unset variables to ensure data integrity
}
$end = microtime_float();
$diff = $end - $start;
$difftime = $diff;
echo "<hr>Records from DB 1: $numrows<br>Records INSERTed to DB 2: $totalaffected<br>Num. of Simultaneous INSERTS: $num_of_inserts<br>Total Time: $difftime Sec";

function microtime_float()
{
   list($usec, $sec) = explode(" ", microtime());
   return ((float)$usec + (float)$sec);
}
?>

---------------------------------------------------------------------------
Results on my ZIP code DB:

Records from DB 1: 43191
Records INSERTed to DB 2: 43191
Num. of Simultaneous INSERTS: 1
Total Time: 7.31878399849 Sec

Records from DB 1: 43191
Records INSERTed to DB 2: 43191
Num. or Simultaneous INSERTS: 20
Total Time: 2.40108299255 Sec

Records from DB 1: 43191
Records INSERTed to DB 2: 43191
Num. of Simultaneous INSERTS: 100
Total Time: 1.9807779789 Sec

Records from DB 1: 43191
Records INSERTed to DB 2: 43191
Num. of Simultaneous INSERTS: 1000
Total Time: 2.31355595589 Sec
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16616240
Note: The source and destination tables must have the same structure (same column names, datatypes, etc...).
0
 

Author Comment

by:tgavin
ID: 16616820
That works like a charm!
I gave LinuxNubb 50 points as an assist, since he steered it in the right direction.

Thank you very much for your help!
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16616941
No problem!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
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 count occurrences of each item in an array.
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 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