Jump to content

I want to send data from my local server database table to different database with the same table


mahenda

Recommended Posts

the second database found on the cloud

 

i try to get JSON data but how to insert and update  them to another online database with the same table

my php script to return json data 

<?php        

 include_once('db.php');

        $users = array();

        $users_data = $db -> prepare('SELECT id, username FROM  users');

        $users_data -> execute();

        while($fetched = $users_data->fetch()) {

            $users[$fetchedt['id']] = array (

                'id' => $fetched['id'],

                'username' => $fetched['name']

            );

        }

        echo json_encode($leaders);

 

 

 

 

i get

 

{"1":{"id":1,"username":"jeremia"},"2":{"id":2,"username":"Ernest"}}

 

Edited by mahenda
Link to comment
Share on other sites

You don't need JSON data (unless you actually want to store JSON data). 

Use two database connections - one to the local database, another to the cloud one - then loop through the data from the local one and insert it into the cloud one:

$insertDB -> prepare('insert into cloud_users values ( :id, :username )'); 

$readDB -> prepare('select id, username from users order by 1'); 
$readDB -> execute(); 
while ( $row = $readDB -> fetch() )
{
   $insertDB -> execute( [ 'id' => $row['id'], 'username' => $row['username'] ] ); 
}

Regards, 
   Phill  W.

 

  • Thanks 1
Link to comment
Share on other sites

This would be the fastest way

  • $local     = PDO connection to database on local db server
  • $remote = PDO connection to database on remote server
//
// GET DATA FROM LOCAL SERVER
//
$res = $local->query("SELECT user_id, user_username FROM users");
$users = [];
foreach ($res as $row) {
    $users[] = vsprintf("(%d, '%s')", $row);
}

//
// WRITE DATA TO REMOTE SERVER
// 1,000 RECORDS AT A TIME
//
$chunks = array_chunk($users, 1000);
$count = 0;
foreach ($chunks as $data) {
    $count += $remote->exec("INSERT INTO users (id, username) VALUES " . join(',', $data)); 
}

echo "$count records transferred";

 

  • Like 1
Link to comment
Share on other sites

On 3/24/2021 at 12:22 PM, Phi11W said:

You don't need JSON data (unless you actually want to store JSON data). 

Use two database connections - one to the local database, another to the cloud one - then loop through the data from the local one and insert it into the cloud one:


$insertDB -> prepare('insert into cloud_users values ( :id, :username )'); 

$readDB -> prepare('select id, username from users order by 1'); 
$readDB -> execute(); 
while ( $row = $readDB -> fetch() )
{
   $insertDB -> execute( [ 'id' => $row['id'], 'username' => $row['username'] ] ); 
}

Regards, 
   Phill  W.

 

 

On 3/24/2021 at 12:27 PM, Barand said:

This would be the fastest way

  • $local     = PDO connection to database on local db server
  • $remote = PDO connection to database on remote server

//
// GET DATA FROM LOCAL SERVER
//
$res = $local->query("SELECT user_id, user_username FROM users");
$users = [];
foreach ($res as $row) {
    $users[] = vsprintf("(%d, '%s')", $row);
}

//
// WRITE DATA TO REMOTE SERVER
// 1,000 RECORDS AT A TIME
//
$chunks = array_chunk($users, 1000);
$count = 0;
foreach ($chunks as $data) {
    $count += $remote->exec("INSERT INTO users (id, username) VALUES " . join(',', $data)); 
}

echo "$count records transferred";

 

thank you so much guys

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.