SQL > JSON > SQL-CodePudding

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.

CodePudding user response：

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