Insert a mysqli_result Object

Question

I have a mysqli_result:

$stmt = $db->prepare("SELECT * FROM customer");
$stmt->execute();
$result = $stmt->get_result();

Is it possible to directly do an INSERT with this result? (Data needs to be transferred in another database) like $another_db->insert($result) or at least to convert the whole result Object to a simple Mysql Insert String without iterating over the result.

why not do it in a single sql query rather than query/php/query? — Professor Abronsius, Oct 19 '19 at 08:29
If your user has access to both databases - https://stackoverflow.com/questions/33595178/copy-fields-from-one-database-to-another-in-mysql — Nigel Ren, Oct 19 '19 at 08:29
so, as they reside on different servers presumably the credentials are different? — Professor Abronsius, Oct 19 '19 at 08:31
Yes, due to security reasons the 2nd database runs on a different host with different credentials — simonheinrich, Oct 19 '19 at 08:31

score 1 · Answer 1 · edited Nov 25 '20 at 18:51

I do a workaround like this:

$stmt = $db->prepare("SELECT * FROM customer");
$stmt->execute();
$result = $stmt->get_result();
$newsql="";
while($row =  $result->fetch_object())
{
    $newsql = "INSERT INTO customer VALUES (";
    foreach($row as $key => $value)
    {
        $newsql .=  "'".mysqli_real_escape_string($db,$value)."',";
    }
    $newsql = substr($newsql,0,-1);
    $newsql .="); ";
}
// $newsql can be inserted.

Insert a mysqli_result Object

1 Answers1