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 comment
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));
  • Like 1
Link to comment
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 comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.