Error Inserting the result of a SELECT into tables of different Mysql databases

Question

I’m getting an error when generating a select and then giving an input, I have 2 different banks but on the same server and I’m giving a select in the registered database whose table is record and giving an Insert to the target database which is the migrate_test whose table is sites the two tables have the same columns and types, my connection.php.

$servername = "localhost";
$username   = "root";
$password   = "";
$bdOrigem   = 'registro_duplicado';
$bdDestino  = 'migracao_teste';
$conn = mysql_connect($servername, $username, $password) or die("Falha na 
conexão com o BD " . $conn->connect_error);
mysql_select_db($bdOrigem , $conn) or die('Não foi possível selecionar o BD 
'. $bdOrigem );

A file called funcao.php.

function consulta($sql){
//gera o SQL
$result = mysql_query($sql);
//se o SQL est´acorreto
if (!$result) {die('SQL Invalido: ' . mysql_error());}
//se tem resultados
if (mysql_num_rows($result) > 0) {
    //retorna os dados
    return $result;
}else{
    return false;
}   
}

And the.php sites where the select and the Insert.

 require('conexao.php');
 require('funcoes.php');
 $sql = 'SELECT registros.id AS id, registros.descricao AS descricao, 
 registros.observacao as observacao
 FROM registro_duplicado.registro AS registros
 ORDER BY registros.id';
 $registros = consulta($sql);
 $insert = null;
 if($registros){ 
   while ($registro = mysql_fetch_array($registros)) {
     $id = $registro['id'];
     $descricao = ((!empty($registro['descricao'])) ? 
     utf8_encode($registro['descricao']) : '');
     $observacao = ((!empty($registro['observacao'])) ? $registro['observacao'] 
     . '.site.localhost' : '');
     $insert .= "INSERT INTO sites (descricao, observacao) VALUES 
     ('{$descricao}', '{$observacao}');";
   } 
 } else {
    echo 'Nenhum resultado encontrado';
 }
 if (!empty($insert)) {
    print "<pre>";
    print($insert);
    print "</pre>";
    mysql_select_db($bdDestino , $conn) or die('Não foi possível selecionar o BD 
    '.$bdDestino );
    $result = mysql_query($insert);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }
 }
 mysql_close($conn);
 header('Content-Type: text/plain;');
 print($insert);

When I give the F5 in the browser I have the error below:

Invalid query: You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'INSERT INTO sites (descricao, observacao) VALUES ('REGISTRO 2', 'teste.site.loca' at line 1

And the error happens at the moment that it will give an input in the second record, if you put a LIMIT of 1 it generates the query and saves the input in the database

Answer 1

Good morning Henry. I have been looking at the code and realized that your goal is to insert the result of select in another table of another bank on the same server. If there is no need to work with the return of select you can use the following query:

INSERT INTO migracao_teste.sites (descricao, observacao)
SELECT
    registros.descricao AS descricao, 
    registros.observacao as observacao
FROM registro_duplicado.registro AS registros
ORDER BY registros.id'