How to I insert into one database/table data from a select from another database/table?

Questions Components & MVC
1

Project: I have a separate production and development database. Periodically I’d like to update most of the data in development with recent entries in production.

Currently I’ve been relying on MySQL procedures which simply drop the table and create new tables from selecting all records in production. (i.e. CREATE TABLE dev.table AS (SELECT * FROM prod.table); This is becoming inefficient.

Goal: From within my application, I’d like to be able to initiate an update. Every record has a date created and date modified field. Simply, select the latest record based on date created, DESC LIMIT 1, and retrieve the date. Then insert into dev (select from prod where date_created > $var).

This isn’t that bad, until you put the two databases into the equation. Here’s something along the lines of what I’m looking for.

$sql = new Sql($production_adapter)
…
$select = new Select();
$select->from($table)->where($where->greaterThan(‘DATE_CREATED’, $target_date));
…
$insert = new Insert();
$insert->into($table)->values($select);
…
$statement = $sql->prepareStatementForSqlObject($insert);
$statement->execute();

I can get it to select and insert from the same database, but not to an alternate database. Any suggestions?

2

Laminas db supports using named adapters. Maybe this could help?

3

I did setup two named adapters.

'services' => [
    'dev-model-adapter-config' => [
        'driver' => 'PDO',
        'dsn' => 'mysql:host=host;dbname=database_dev',
        'username' => 'user',
        'password' => 'pass',
    ],
    'prod-model-adapter-config' => [
        'driver' => 'PDO',
        'dsn' => 'mysql:host=host;dbname=database_prod',
        'username' => 'user',
        'password' => 'pass',
    ],
],

Normally though, when preparing SQL statement, one adapter is passed. I’ve used multiple adapters before to pull data from two different databases, but looking for an easy efficient way to reference to databases (two named adapters) from one SQL statement.

$sql = new Sql($this->production_adapter);
...
$statement = $sql->prepareStatementForSqlObject($select);
4

You would need a secondary SQL instance as far as I am aware tied to the secondary adapter.

5

Hi @COMCDUARTE,

I think you need to change your configuration key. You’ve to follow the framework’s guidelines and use the appropriate keys. You’ve used an imaginary key and thought that it would work. So it would be best to change the following key as suggested in the documentation.

return [
  'db'/*Changed to db instead of services as suggested.*/ => [
     'dev-model-adapter-config' => [...],
     'prod-model-adapter-config' => [...],
  ],
...
];

Another suggestion would be to create a stored procedure. It would be much faster and memory efficient. Thanks!

6

I took it that the OP has somehow configured the application to correctly use the config shown since they can get it to read from two different DB’s currently, however for the sake of correctness see below.

According to the docs I linked to it should be:

'db' => [
     'adapters' => [
         // named adapter config here, this is what the AbstractFactory looks for
    ],
]

To see why please see: