How to fit a scalar peg into a round hole?

[tunage]

I am trying to make a script that copies data from one table on one server to an identical table on another server.

My issue is trying to inset the retrieved data via $insert_stmt->execute($row);

If I load the data via fetch(), I get nothing, not even an error.

If I use fetchAll(), I get an array to string conversion error:

Notice: Array to string conversion in /usr/home/www/admin/tmpchkr2.php on line 107

             $pdo = new PDO(
                        'mysql:host=' . DB_HOST_R2D2 . ';dbname=' . DB_DATABASE_DNS,
                         DB_USER_DNS,
                         DB_PASSWORD
                );
                //yoda pdo settings
                $pdoyd = new PDO(
                        'mysql:host=' . DB_HOST_YODA . ';dbname=' . DB_DATABASE_DNS,
                         DB_USER_DNS,
                         DB_PASSWORD
                );
                $pdoyd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $pdoyd->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                foreach($tblnames as $tbl){
                        $sql = 'truncate '.$tbl;
                        $statementyd = $pdoyd->prepare($sql);
                        $useryd = $statementyd->execute();
                        var_dump($statementyd);
                        echo '<br>';
                }
                foreach($tblnames as $tbl){
                        var_dump($tbl);
                        if($tbl == 'cryptokeys'){
                                $insert_stmt = $pdoyd->prepare("INSERT INTO cryptokeys (id, domain_id, flags, active, content) VALUES (:id, :domain_id, :flags, :active, :content)");
                                $select_results = $pdo->prepare("SELECT * FROM cryptokeys");
                                $sresult = $select_results->execute();
                                //$sresult = $select_results->fetchAll();
                                while ($row = $select_results->fetchAll(PDO::FETCH_ASSOC)) {
                                        echo '<br>boo3';
                                        $insert_stmt->execute($row);
                                        var_dump($insert_stmt);
                                }
                        }

                }

How do I properly insert retrieved data from a select query of a different server table?

[requinix]

fetch() will return an array. If your placeholders use the same names as the returned columns (hint: they don't) then that would work.

fetchAll() will return an array of arrays. One, you cannot do a while loop on it because you will keep executing fetchAll() every time. Two, you can't execute() with an array of arrays.

 

Use the question mark placeholders so they don't have names, then the array from fetch(). Making damn sure that you get the right columns in the right order. Which is not something I would leave to chance.

$select_results = $pdo->prepare("SELECT * FROM cyptokeys");
$select_results->execute();

$header = $select_results->fetch(PDO::FETCH_ASSOC);
if (!$header) {
	// no results? abort
}

try {
	$insert_stmt = $pdoyd->prepare(
		"INSERT INTO cyptokeys (`" . implode("`, `", array_keys($header)) . "`)
		VALUES (" . implode(", ", array_fill(0, count($header), "?")) . ")"
	);
} catch (PDOException $e) {
	// do something and abort
}

$row = array_values($header);
do {
	$insert_stmt->execute($row);
} while ($row = $select_results->fetch(PDO::FETCH_NUM));

[tunage]

fetch() will return an array. If your placeholders use the same names as the returned columns (hint: they don't) then that would work.

fetchAll() will return an array of arrays. One, you cannot do a while loop on it because you will keep executing fetchAll() every time. Two, you can't execute() with an array of arrays.

 

Use the question mark placeholders so they don't have names, then the array from fetch(). Making damn sure that you get the right columns in the right order. Which is not something I would leave to chance.

$select_results = $pdo->prepare("SELECT * FROM cyptokeys");
$select_results->execute();

$header = $select_results->fetch(PDO::FETCH_ASSOC);
if (!$header) {
	// no results? abort
}

try {
	$insert_stmt = $pdoyd->prepare(
		"INSERT INTO cyptokeys (`" . implode("`, `", array_keys($header)) . "`)
		VALUES (" . implode(", ", array_fill(0, count($header), "?")) . ")"
	);
} catch (PDOException $e) {
	// do something and abort
}

$row = array_values($header);
do {
	$insert_stmt->execute($row);
} while ($row = $select_results->fetch(PDO::FETCH_NUM));

Thank you!