Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    30

    Unanswered: transferring data between mySql databases.

    Hi All

    I need to get a php page to grab a chunk of records from a table in one database (on a remote server) then write them into a table on the database on the local server before processing them.

    the connections are not a probelm, nor is getting the data from the remote server but i want to find a way to write the data in one hit, rather than parsing through the array as it could contain 100'000 records.

    anyone got any ideas. even if the connections and getting the data need to be re-written. thanks

    PHP Code:
    $recordMax 1000;

    //---------- local database settings ----------//
    $host 'xxx';
    $user 'xxx';
    $password 'xxx';

    //---------- remote database settings ----------//
    $rmHost 'xxx';
    $rmUser 'xxx';
    $rmPassword 'xxx';

    //---------- get 1000 records to send ----------//

        
    $conRem mysql_connect($rmHost,$rmUser,$rmPassword);
        
    $remoteRecords mysql_db_query("master","SELECT * FROM table1 LIMIT 0, $recordMax",$conRem) or die (mysql_error());
        
    mysql_close($conRem);
        
    $conLocal mysql_connect($host,$user,$password) or die (mysql_error());
        
    //insert write data method here
    mysql_close($conLocal);

    print 
    "done!";
    ?> 

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    instead of storing 100,000 records in memory (using an array), write/export them to an intermediate text file (csv or xml) first?

    but then are we assuming that we cannot (or it is not appropriate to) have two connections open at the same time, one for the remote server and one for the local server... then do a loop insert?

  3. #3
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The quickest way (from a processing speed standpoint and using minimal code), would be to have your script use - SELECT ... INTO OUTFILE - to save the remote data into a CSV file on your server, then use - LOAD DATA LOCAL INFILE - to put the data into the database.
    Last edited by dbmab; 07-16-06 at 12:44.

  4. #4
    Join Date
    Mar 2004
    Location
    UK
    Posts
    30
    thanks for the replies. i'll have a play with the code and see how i get on.

Posting Permissions

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