Insert rows into database table from 2d json data using a PDO prepared statement

Question

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.

Answer 1

@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));
}

You could also do the entire insert in one query, although you may run into limits on the number of parameters in a prepared query:

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

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

// flatten array
$data = array_merge(...array_map('array_values', $a));
$stmt->execute($data);