SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using PHP to copy tables from one Mysql DB to another

    Hey Sitepoint!

    I'm trying to write a script that will copy the contents of my development domain over to the live site, or vice versa. Part of this script will also need to copy Mysql tables from one database to another. I have tried doing this manually with PHP (by copying all of the contents of the tables into a PHP array but it runs out of memory very quickly), so I was trying to find a better way of doing this and came across this: http://www.edmondscommerce.co.uk/mys...se-to-another/

    Code:
    DROP TABLE IF EXISTS `backup_db.backup_table`;
    CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`
    This seems really easy, however I don't quite understand how this command can run as PHP will only be logged into one database at a time and the dev database, and the live database have different user/passwords.

    Should I just run the SQL command using the system() function?

  2. #2
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,191
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    All you need to do is use the standard MySQL functions that PHP has built in, see the below examples:

    MySQL
    PHP Code:
    // Create a new MySQL database connection
    if (!$con mysql_connect('localhost'$username$password)) {
        die(
    'An error occurred while connecting to the MySQL server!<br><br>' mysql_error());
    }

    if (!
    mysql_select_db($database)) {
        die(
    'An error occurred while connecting to the database!<br><br>' mysql_error());
    }

    // Create an array of MySQL queries to run
    $sql = array(
        
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
        
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
    );

    // Run the MySQL queries
    if (sizeof($sql) > 0) {
        foreach (
    $sql as $query) {
            if (!
    mysql_query($query)) {
                die(
    'A MySQL error has occurred!<br><br>' mysql_error());
            }
        }
    }

    mysql_close($con); 
    MySQLi
    PHP Code:
    // Create a new MySQL database connection
    if (!$con = new mysqli('localhost'$username$password$database)) {
        die(
    'An error occurred while connecting to the MySQL server!<br><br>' $con->connect_error);
    }

    // Create an array of MySQL queries to run
    $sql = array(
        
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
        
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
    );

    // Run the MySQL queries
    if (sizeof($sql) > 0) {
        foreach (
    $sql as $query) {
            if (!
    $con->query($query)) {
                die(
    'A MySQL error has occurred!<br><br>' $con->error);
            }
        }
    }

    $con->close(); 

  3. #3
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris.upjohn View Post
    All you need to do is use the standard MySQL functions that PHP has built in, see the below examples:

    MySQL
    PHP Code:
    // Create a new MySQL database connection
    if (!$con mysql_connect('localhost'$username$password)) {
        die(
    'An error occurred while connecting to the MySQL server!<br><br>' mysql_error());
    }

    if (!
    mysql_select_db($database)) {
        die(
    'An error occurred while connecting to the database!<br><br>' mysql_error());
    }

    // Create an array of MySQL queries to run
    $sql = array(
        
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
        
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
    );

    // Run the MySQL queries
    if (sizeof($sql) > 0) {
        foreach (
    $sql as $query) {
            if (!
    mysql_query($query)) {
                die(
    'A MySQL error has occurred!<br><br>' mysql_error());
            }
        }
    }

    mysql_close($con); 
    MySQLi
    PHP Code:
    // Create a new MySQL database connection
    if (!$con = new mysqli('localhost'$username$password$database)) {
        die(
    'An error occurred while connecting to the MySQL server!<br><br>' $con->connect_error);
    }

    // Create an array of MySQL queries to run
    $sql = array(
        
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
        
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
    );

    // Run the MySQL queries
    if (sizeof($sql) > 0) {
        foreach (
    $sql as $query) {
            if (!
    $con->query($query)) {
                die(
    'A MySQL error has occurred!<br><br>' $con->error);
            }
        }
    }

    $con->close(); 
    I only see you logging into one Database... How will this connect to two different databases?

  4. #4
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,191
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    The article you linked to assumes that both databases have access via the same username and password, because of this the above example assumes that as well. The only other way to transfer the database values from one db to another would be to manually pull down all the data and store it in a buffer until your ready with the second database, of course this will cause a huge problem with the memory limit but it would be the only other way i can think of to transfer the data.

  5. #5
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris.upjohn View Post
    The article you linked to assumes that both databases have access via the same username and password, because of this the above example assumes that as well. The only other way to transfer the database values from one db to another would be to manually pull down all the data and store it in a buffer until your ready with the second database, of course this will cause a huge problem with the memory limit but it would be the only other way i can think of to transfer the data.
    Yes, that is what I figured from the beginning, but I thought I'd ask and see if there was another way. Thank you for clarifying.

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,023
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Are both the source and destination database on the same server? Assuming that the structure of the tables is the same in both databases you could after having got a list of the tables, loop through the tables, fdor each table using a "select insert" query to copy the data across
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Are both the source and destination database on the same server? Assuming that the structure of the tables is the same in both databases you could after having got a list of the tables, loop through the tables, fdor each table using a "select insert" query to copy the data across
    Yes, both source and destination databases are on the same Mysql Server, but they required different usernames and logins. Could you give an example of how I might pull off what you suggested? I'd really appreciate that.

  8. #8
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,023
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wh33t View Post
    Yes, both source and destination databases are on the same Mysql Server, but they required different usernames and logins. Could you give an example of how I might pull off what you suggested? I'd really appreciate that.
    Does one of the to users have rights to do SELECT queries on the tables in the source database and INSERT queries in the destination database? If so have PHP use that user to copy the data across, but running the query against the destination database.

    Thread moved to the Databases forum
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  9. #9
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi wh335,

    One way is to create two different database instances; one using database A's credentials and one using database B's Credentials.

    So here is how to get both connections using PDO:

    PHP Code:
    <?php
    class DBOne {
       static 
    $db ;
       private 
    $dbh ;
       private function 
    PDO_DBConnect(){
         
    $db_type 'mysql'
         
    $db_name 'testdb';
         
    $user 'db_user1' ;    $password '' ;
         
    $host 'localhost' 
        try {
            
    $dsn "$db_type:host=$host;dbname=$db_name"
            
    $this->dbh = new PDO $dsn$user$password); 
            
    $this->dbh->setAttribute(PDO::ATTR_PERSISTENTtrue);
            
    $this->dbh->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
         } catch ( 
    PDOException $e ) {
            print 
    "Error!: " $e->getMessage () . "\n" ;      die () ; 
         }  
       }
       public static function 
    getInstance ( ) {
         if (! isset ( 
    PDO_DBConnect::$db )) {
            
    PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
         }
         return 
    PDO_DBConnect::$db->dbh;
      }
    }
    class 
    DBTWO {
       static 
    $db ;
       private 
    $dbh ;
       private function 
    PDO_DBConnect(){
         
    $db_type 'mysql'
         
    $db_name 'testdb2';
         
    $user 'db_user2' ;    $password '' ;
         
    $host 'localhost' 
        try {
            
    $dsn "$db_type:host=$host;dbname=$db_name"
            
    $this->dbh = new PDO $dsn$user$password); 
            
    $this->dbh->setAttribute(PDO::ATTR_PERSISTENTtrue);
            
    $this->dbh->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
         } catch ( 
    PDOException $e ) {
            print 
    "Error!: " $e->getMessage () . "\n" ;      die () ; 
         }  
       }
       public static function 
    getInstance ( ) {
         if (! isset ( 
    PDO_DBConnect::$db )) {
            
    PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
         }
         return 
    PDO_DBConnect::$db->dbh;
      }
    }
    You would set each connection by doing this:
    PHP Code:
    $o_DbA DBOne::getInstance(); //Get Database A
    $o_DbB DBTwo::getInstance(); // Get Database B
    $sql 'SELECT...';
    $stmt $o_DBA->prepare($sql);
    $stmt->execute();
    $db_A_results $stmt->fetch(PDO::FETCH_ASSOC);
    foreach(
    $db_A_results as $results){
      foreach(
    $result as $key => $value){
        
    $stmt $o_DbB ->prepare("INSERT INTO sometable (name, value) VALUES (:name, :value)");
        
    $stmt->bindParam(':name'$key);
        
    $stmt->bindParam(':value'$value);
        
    $stmt->execute();
      }

    Now you can use both connections to SELECT, UPDATE, INSERT AND DELETE data from the same application.

    A different idea, hope this helps;

    Steve
    ictus==""

  10. #10
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Hi wh335,

    One way is to create two different database instances; one using database A's credentials and one using database B's Credentials.

    So here is how to get both connections using PDO:

    PHP Code:
    <?php
    class DBOne {
       static 
    $db ;
       private 
    $dbh ;
       private function 
    PDO_DBConnect(){
         
    $db_type 'mysql'
         
    $db_name 'testdb';
         
    $user 'db_user1' ;    $password '' ;
         
    $host 'localhost' 
        try {
            
    $dsn "$db_type:host=$host;dbname=$db_name"
            
    $this->dbh = new PDO $dsn$user$password); 
            
    $this->dbh->setAttribute(PDO::ATTR_PERSISTENTtrue);
            
    $this->dbh->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
         } catch ( 
    PDOException $e ) {
            print 
    "Error!: " $e->getMessage () . "\n" ;      die () ; 
         }  
       }
       public static function 
    getInstance ( ) {
         if (! isset ( 
    PDO_DBConnect::$db )) {
            
    PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
         }
         return 
    PDO_DBConnect::$db->dbh;
      }
    }
    class 
    DBTWO {
       static 
    $db ;
       private 
    $dbh ;
       private function 
    PDO_DBConnect(){
         
    $db_type 'mysql'
         
    $db_name 'testdb2';
         
    $user 'db_user2' ;    $password '' ;
         
    $host 'localhost' 
        try {
            
    $dsn "$db_type:host=$host;dbname=$db_name"
            
    $this->dbh = new PDO $dsn$user$password); 
            
    $this->dbh->setAttribute(PDO::ATTR_PERSISTENTtrue);
            
    $this->dbh->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
         } catch ( 
    PDOException $e ) {
            print 
    "Error!: " $e->getMessage () . "\n" ;      die () ; 
         }  
       }
       public static function 
    getInstance ( ) {
         if (! isset ( 
    PDO_DBConnect::$db )) {
            
    PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
         }
         return 
    PDO_DBConnect::$db->dbh;
      }
    }
    You would set each connection by doing this:
    PHP Code:
    $o_DbA DBOne::getInstance(); //Get Database A
    $o_DbB DBTwo::getInstance(); // Get Database B
    $sql 'SELECT...';
    $stmt $o_DBA->prepare($sql);
    $stmt->execute();
    $db_A_results $stmt->fetch(PDO::FETCH_ASSOC);
    foreach(
    $db_A_results as $results){
      foreach(
    $result as $key => $value){
        
    $stmt $o_DbB ->prepare("INSERT INTO sometable (name, value) VALUES (:name, :value)");
        
    $stmt->bindParam(':name'$key);
        
    $stmt->bindParam(':value'$value);
        
    $stmt->execute();
      }

    Now you can use both connections to SELECT, UPDATE, INSERT AND DELETE data from the same application.

    A different idea, hope this helps;

    Steve
    That's an interesting way of looking at the problem. Unfortunately that code is a bit beyond my understanding.

    I've managed to up the memory limit and time limit for the script to execute and I think I can do it by switching connections (kind of like your set up) between the databases. I'm just curious if there is an easy PHP Sql function to do this.

    Normally I would do something like "select * from some table" and I would use while loop like

    Code:
    $query = 'select * from some_table";
    $result = mysql_query($query);
    while($row = mysql_fetch_assoc($result))
    {
      $sql_table_data[] = $row;
    }
    Then, once I have that array with the table data I would do something like

    Code:
    connect_to_other_database();
    
    foreach ($sql_table_data as $row)
    {
      $query = 'insert into some_table set SOME_COLUMN_NAME = "' . $row['SOME_COLUMN_NAME'] . ', SOME_COLUMN_NAME2 = "' . $row['SOME_COLUMN_NAME2'] . '";
    
    mysql_query($query);
    }
    But I'm thinking there is probably a way I can select a row of data and insert it with out having to specify each column name on the insert. Would you happen to know if this is possible?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    But I'm thinking there is probably a way I can select a row of data and insert it with out having to specify each column name on the insert. Would you happen to know if this is possible?
    yes, it is

    provide a VALUES value for every column, and abandon the SET version of the INSERT statement

    this has the added advantage that you can also supply values for multiple rows at a time, say in batches of 50 or 100 rows, to improve efficiency over calling the target database one row at a time

    which raises the question... have you thought of dumping the table from the source database, and executing the load into the target as a command?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, it is

    provide a VALUES value for every column, and abandon the SET version of the INSERT statement

    this has the added advantage that you can also supply values for multiple rows at a time, say in batches of 50 or 100 rows, to improve efficiency over calling the target database one row at a time

    which raises the question... have you thought of dumping the table from the source database, and executing the load into the target as a command?
    Would you mind writing me a small piece of sample code to demonstrate that? That really sounds like something I ought to know by now lol.

    I tried to do "select * INTO OUTFILE" and it tells me I don't have permission. That is probably because I only have access to PHPMYADMIN and I'm not sure how to give a database user login account access to the physical disk to write files. I would normally just do a full dump of the database and pave over the development database but I don't want to wipe the whole thing out, I just want a select 10/30~ tables. So... here I am.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    INSERT
      INTO targettable
    VALUES
     (  9, 'asdf', 'this is a row' )
    ,( 37, 'qwer', 'this is another' )
    ,( 42, 'zxcv', 'this also' )
    , ...
    as for dumping, i didn't mean INTO OUTFILE, i meant mysqldump command
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    INSERT
      INTO targettable
    VALUES
     (  9, 'asdf', 'this is a row' )
    ,( 37, 'qwer', 'this is another' )
    ,( 42, 'zxcv', 'this also' )
    , ...
    as for dumping, i didn't mean INTO OUTFILE, i meant mysqldump command
    Thank you very much for the example code. I think I get it now. How many rows can I insert into one SQL insert like that? You mentioned 50-100 but is there an actual limit I should respect?

    As for mysqldump, I presumed it wasn't possible to just dump just the tables I wanted. I also didn't think it was possible to dump the tables into an array that I would be able to iterate through.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    I also didn't think it was possible to dump the tables into an array that I would be able to iterate through.
    i was thinking that you would dump to a text file, then just load the text file into the target database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i was thinking that you would dump to a text file, then just load the text file into the target database
    I could do that, but I was trying to make it so I could perform this database update simply by clicking a button on a password protected web page.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    I could do that, but I was trying to make it so I could perform this database update simply by clicking a button on a password protected web page.
    what is the difference between clicking a button to execute a php routine that copies one table to the other a few rows at a time, versus clicking a button to execute a php routine that runs a dump and upload?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what is the difference between clicking a button to execute a php routine that copies one table to the other a few rows at a time, versus clicking a button to execute a php routine that runs a dump and upload?
    Mostly my limited knowledge of Linux. When I try to do things from the command line (which I presume is what your talking about) quite often I run into permission errors because the user account that runs PHP on the VPS I use doesn't have access to various directories and such. But if it's possible to do all of this just through PHP then I'm game! At any rate, I think I got it figured out now with your examples from above, so thank you


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •