0

I would like to insert all the data of a table presented on the database of our network on a remote database (present on a remote server)

(this action will automate every 30 minutes)

The problem is that I do not see how to retrieve all the data from the table_local and insert them directly into the table_remote.

Indeed, to connect to these two databases, I use PDO

<?php 

// LOCAL

$user = 'user1';
$password = 'password1';
$dns = 'completeDNS1';
$bdd = new PDO($dns, $user, $password);

$request = $bdd->prepare("SELECT * FROM table_local");
$request ->execute();

// REMOTE

$user = 'user2';
$password = 'password2';
$dns = 'completeDNS2';
$bdd = new PDO($dns, $user, $password);

// How to insert the previous data on the table_remote ?

?>

I would like to avoid, if possible, the foreach because the script will be launched very often and the table_local contains a lot of line

Is there a simple solution?

Rocstar
  • 1,427
  • 3
  • 23
  • 41
  • 1
    Possible duplicate of [How to copy a table from one mysql database to another mysql database](https://stackoverflow.com/questions/3932608/how-to-copy-a-table-from-one-mysql-database-to-another-mysql-database) – pr1nc3 Oct 09 '18 at 12:16
  • 1
    It's not simple but you could look into [replication](https://dev.mysql.com/doc/refman/8.0/en/replication.html) – Nick Oct 09 '18 at 12:18
  • @Nick replication is too complicated for a simple table "move" – Rocstar Oct 09 '18 at 12:36
  • @Rocstar I did say it wasn't simple! :-) Just wanted to throw it out there as I couldn't be sure of the complexity of your overall problem. – Nick Oct 09 '18 at 12:43

2 Answers2

1

One method is using one tool like navicat or sequel pro to achieve. Another method is using following codes:

$sql = "INSERT INTO table_name (column1, column2...) VALUES ";
foreach($res $key => $val) {
    $sql .= "($val['column1'],$val['column2']...),";
}
$sql = rtrim($sql, ',');
...
yusher
  • 226
  • 1
  • 5
  • Your solution is good but only if I limit the first `select` otherwise I have this error `Warning: PDOStatement :: execute (): MySQL server has gone away in .. on line 58` (`$ request -> execute ();`) why? – Rocstar Oct 09 '18 at 13:16
  • For this situation, you can try methods `set_time_limit(0);` `ini_set('mysql.connect_timeout',300);` `ini_set('default_socket_timeout',300);` to avoid timeout in your code above, also, I think you should add `order by` and `limit` to your `select` sql, and using loop statement to achieve. – yusher Oct 10 '18 at 01:43
0
<?php 

// LOCAL

$user = 'user1';
$password = 'password1';
$dns = 'completeDNS1';
$bdd1 = new PDO("mysql:host=localhost;dbname=$dns", $user, $password);


$user = 'user2';
$password = 'password2';
$dns = 'completeDNS2';
$bdd2 = new PDO("mysql:host=localhost;dbname=$dns", $user, $password);



$request = $bdd1->prepare("SELECT * FROM table_local");

// REMOTE
while ($row = $request->fetch()) {
    $sql = "INSERT INTO table_remote (name, surname, sex) VALUES (?,?,?)";
    $stmt= $bdd2->prepare($sql);
    $stmt->execute([$row['name'], $row['surname'], $row['sex']]);


}

?>

for reference check this link https://phpdelusions.net/pdo_examples/insert

sangamesh
  • 21
  • 4