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

[mahenda]

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 March 24, 2021 by mahenda

[Phi11W]

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.

 

[Barand]

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";

 

[mahenda]

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