Question 284339 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL > JSON > SQL

I do a lot of data transfers from sites to sites mainly DB data.. Latest was a simple pdo DB query, fetchall, then Jason encode the data, send to remote site and populate database with data, same database structure.

I thought i could simplify this, I use last record as staring point. I send request to source server with a start value of last ID in receiving db then run this on source.

if ($_GET['start']){
    $startId = $_GET['start'];
    $q = $db->query("SELECT * FROM `table` where `id` > $startId  LIMIT 0,5 ");
    $r= $q->fetchall(PDO::FETCH_ASSOC);
    $out = json_encode($r);
    print $out;
}

this generates the jason data as expected, I take that on receiving side and convert to array, then use pdo to update, but after 24 hours of not inserting I give up!

Any thoughts where this is going wrong? or a better way to transfer data

code on receiving side

    $a = json_decode($html,true);
    print '<pre>';
    print_r($a);
    print '</pre>';
}


foreach ($a as $data){
    if(!$valc){
        $valc = '(';
        foreach($data as $val){
            $valc = $valc.'?,';
        }
        $valc = rtrim($valc, ",");
        $valc = $valc.")";
    }
    $sql = "INSERT INTO table (".implode(', ', array_keys($data)).") VALUES ".$valc." ";
    print '<h4>'.$sql.'</h4>';
    $stmt = $dbo->prepare( $sql );
    $stmt->execute($data);

everything looks correct yet no insert is happening. I tried to first insert many rows with one insert but now to looping but still not working…

Tables are same structure on both side.

Tags:

2

Answers


  1. 0

    It seems like you’re attempting to transfer data from a source database to a remote one using PHP and PDO. The approach you’re using involves querying the source database for records with IDs greater than a given value and then inserting this data into the remote database.

    you can try this code

    $a = json_decode($html, true);

foreach ($a as $data) {
    $columns = implode(', ', array_keys($data));
    $values = rtrim(str_repeat('?, ', count($data)), ', '); // Prepare the placeholder values

    $sql = "INSERT INTO table ($columns) VALUES ($values)";
    $stmt = $dbo->prepare($sql);

    $stmt->execute(array_values($data));
}
    Login or Signup to reply.
  2. 0

    @Nekkanti’s answer will work but is inefficient. If all the $data have the same set of array keys, then you can move the prepare out of the loop and just do an execute in the loop:

    $a = json_decode($html, true);

$columns = implode(', ', array_keys($a[0]));
$values = implode(', ', array_fill(0, count($a[0]), '?')); // Prepare the placeholder values

$sql = "INSERT INTO table ($columns) VALUES ($values)";
$stmt = $dbo->prepare($sql);

foreach ($a as $data) {
    $stmt->execute(array_values($data));
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search