How to INSERT SELECT results into another table using PHP code?

Question

I have two databases and I'm trying to perfom a SELECT in db1.table1 and input the results from this select INTO db2.table2.

Basic things like connection to databases and SELECT are tested, I just need help to configure the correct way to put the SELECT resultS in array and INPUT it INTO db2.table2.

A very important thing, I named the SELECT columns and the db2.table2 columns with exact same name, so I can use the IMPLODE command from PHP.

You can see my code bellow:

<?php
//data to connect in both databases
require("connect_otrs_2.php");
require("connect_local_db.php");


//files with select queries
require("select_worklog.php");
require("select_backlog.php");

//connections to databases      
$connect=mysqli_connect($db_host,$db_user,$db_password,$db_name);
$connect2=mysqli_connect($db_localhost,$db_localuser,$db_localpassword,$db_localname);

//set utf8
mysqli_set_charset($connect, "utf8");
mysqli_set_charset($connect2, "utf8");

//the variable $qworklog contains the select from db1.table1, that I want insert into db2.table2
$worklog = mysqli_query($connect,$qworklog);


//this is my best try to insert values from $worklog select into the second database
while ($list = mysqli_fetch_assoc($worklog)){
    $prep = array();
    foreach($list as $k => $v) {
    $prep[':'.$k] = $v;
    }
    $sth = $connect2->prepare("INSERT INTO worklog ( " . implode(', ',array_keys($list)) . ") VALUES (" . implode(', ',array_keys($prep)) . ")");
    $res = $sth->execute($prep);

}

//close connection
mysqli_close($connect);
mysqli_close($connect2);
?>

When a run this code, I have this error:

Fatal error: Uncaught Error: Call to a member function execute() on boolean in C:\wamp64\www\Dashboard_Suporte\replicador.php on line 30

Please research the error message, this gets discussed all the time. (Apart from the fact that it is pretty self-explanatory to begin with.) — 04FS, Feb 18 '19 at 11:56
If you don’t need to manipulate any values in your PHP script to begin with, then you should probably not loop over the data in PHP in the first place, but use an [INSERT ... SELECT](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html) statement instead. — 04FS, Feb 18 '19 at 11:57

score 0 · Answer 1 · answered Feb 18 '19 at 11:57

0

You should change your implode to use quotes:

$sth = $connect2->prepare("INSERT INTO worklog ( " . implode('", "',array_keys($list)) . ") VALUES (" . implode('", "',array_keys($prep)) . ")");

answered Feb 18 '19 at 11:57

Andrii Filenko

954
7
17

How to INSERT SELECT results into another table using PHP code?

1 Answers1