There is an old (1) and a new (2) database. I transfer the data to mezhu two tables. The question is how to do this if the main table pulls other FK tables from (1) behind it. Now I have one SELECT for a table in a DB (1), and I do INSERT 's into tables from a DB (2). To transfer data from other (*) tables, you need to do another SELECT ? Now the script structure is as follows:

 //подключение к (1) SELECT из таблицы БД(1) //подключение к (2) while($row = $result->fetch_assoc()) {... $data=$row['data']; ...} INSERT в таблицы БД(2)

It is necessary to make one more SELECT for the related tables from (1) and to push the data into the same $result ?

  • one
    The easiest way is to keep the existing key values. If in some place a new database does not allow this, then it must be solved separately for each such place. - user239133
  • one
    In the same result is not needed, just copy the tables one by one, starting from the "main" one which itself does not refer to anyone and then all the subordinates. - Mike
  • one
    The most missing, perhaps, important fact in the question is whether the receiving tables are empty or not. If empty, it is enough to build the correct order of data transfer, i.e. table data is transferred only when the data of all tables to which it refers is transferred (alternatively, disable referential integrity check before transfer and enable upon completion). If there is data in the target tables, the task becomes non-trivial, especially if there are duplicates in the data. - Akina
  • one
    And - it is much more reasonable to do data migration with server tools, and not to drive data through the client. - Akina

1 answer 1

In your case, you need to transfer all the tables you need in sequence. Those. the data to push in the same result is not necessary.

You can do something like this:

 //подключение к (1) SELECT из таблицы1 БД(1) //подключение к (2) while($row = $result->fetch_assoc()) {... $data=$row['data']; ...} INSERT в таблицу1 БД(2) //подключение к (1) SELECT из таблицы2 БД(1) //подключение к (2) while($row = $result->fetch_assoc()) {... $data=$row['data']; ...} INSERT в таблицу2 БД(2)

Well and so on according to all necessary tables.

It may be easier to organize a direct transfer from one database to another, like this:

 INSERT INTO db2.table1 (column1, column2, ...) SELECT column1, column 2, ... FROM db1.table1

For this, it is enough that both databases are on the same server and there is a user who had access to both databases.

If the database structure does not change, it may be easier and more correct to dump one database and deploy it to another server (to another database). There are many different tools for this, I can advise PHPMyAdmin and HeidiSQL