Jump to content

How to fit a scalar peg into a round hole?


tunage

Recommended Posts

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?

Link to post
Share on other sites

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));
Link to post
Share on other sites

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!

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.