• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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

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
tgavin
Asked:
tgavin
  • 6
  • 5
  • 3
2 Solutions
 
LinuxNubbCommented:
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
 
tgavinAuthor Commented:
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
 
LinuxNubbCommented:
$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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
tgavinAuthor Commented:
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
 
LinuxNubbCommented:
INSERT INTO `db2`.`table2` SELECT * FROM `db1`.`table1` ;
0
 
tgavinAuthor Commented:
That's basically the same as my original post.
0
 
kamermansCommented:
>>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
 
tgavinAuthor Commented:
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
 
kamermansCommented:
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
 
tgavinAuthor Commented:
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
 
kamermansCommented:
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
 
kamermansCommented:
Note: The source and destination tables must have the same structure (same column names, datatypes, etc...).
0
 
tgavinAuthor Commented:
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
 
kamermansCommented:
No problem!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now