INSERT INTO table1 SELECT * FROM table2 USING PDO SQLITE3

Question

Hi, it’s been a while since I’ve been searching the entire internet and trying to make this query work. In the database works, however, I need to make my application run this query, I use PDO in PHP to access the SQLITE3 database this way:

  // Conexão a base de dados do coletor
  $pdoColetor = new PDO('sqlite:C:\xampp\htdocs\dashboard\downloadFiles\dbcoletor.s3db') or die("Erro ao abrir a base"); 

  // Conexão a base de dados dos 10 ultimos minutos do coletor
  $pdoUltimos10 = new PDO('sqlite:C:\xampp\htdocs\dashboard\downloadFiles\coleta.ultimo') or die("Erro ao abrir a base");

So, I try to run the query for each db but do not know how to "concatenate" because they are objects...

$sqlUltimos10min = $pdoUltimos10->query('SELECT * from DEVICES');

$sqlAtualizaColetor = $pdoColetor->query('INSERT INTO devices ');

Would anyone know the correct syntax or have any idea how I can do it using PDO? In Sqlitestudio I run like this and it works:

insert into dbcoletor.devices SELECT * from coleta.devices;

Would you have any way to do that for the Sqlite PDO? I’m sorry if I made a mistake or I didn’t express my doubt correctly. Thank you for your attention...

Answer 1

I believe that this first query "SELECT * FROM DEVICES" is an array, so you would have to pass the values to an array... try like this:

  foreach($sqlUltimos10min as $valor) {
      //$pdoColetor1 = db::getInstance();
      $sql10min = $pdoColetor->prepare("INSERT INTO DEVICES (coluna1, coluna2, coluna3, coluna4) VALUES (? ,? ,? ,? )");
      $sql10min->bindParam(1, $valor["nome_coluna1"]);
      $sql10min->bindParam(2, $valor["nome_coluna2"]);
      $sql10min->bindParam(3, $valor["nome_coluna3"]);
      $sql10min->bindParam(4, $valor["nome_coluna4"]);
      $sql10min->execute();
    } 

Reference: https://www.php.net/manual/en/book.pdo.php