There is the following situation: every day the site "A" base in the .sql format is unloaded on the FTP. Then, manually, two necessary tables are selected from it, old values ​​are filtered out (which already exist in database "B") and manually imported into database "B". Is it possible to automate this process? If so, which way to look and with what help can it be done? There are suspicions that everything can be done using PHP, but I would like to hear if this is real.

    2 answers 2

    PHP is not needed here, if the databases are identical, then a simple BASH for 3 lines is needed

    1. Create a TMP database on the same server with the same user as the main database.

    2. Download site dump into it

      mysql -uuser -ppass TMP </tmp/databasename.sql

    3. We select from the TMP database from the required table those records that are not in the main database and right there we insert

      INSERT INTO DB1.table1

      SELECT * FROM TMP.table1 B WHERE B.id NOT IN (SELECT id FROM DB1.table1);

    4. By analogy we do with other tables.

    5. We add task in kroner for daily data updating and we forget.
    • There is no access to the base "A". They are unloaded by the owner of the base "A" on the FTP server, where the base "B" is located, in sql format. And here I need to take this file with ftp, pull out two tables from there, weed out the data that is already in base "B" and import into base "B". - Victor Barannik
    • Well, what's not my decision, you have ftp, take the file, upload it to the C database, then use it with SQL tools. - Redr01d

    Yes, php is possible.

    You yourself very well described the logic, and follow it as well :)

    You need to follow the path:
    Start by working with php files
    Then understand by what criteria pull out the necessary tables (I think, regular)
    And export using standard database tools (for example, PDO)

    • "Then, by what criteria, pull out the necessary tables (I think, with a regular schedule)." - Victor Barannik
    • I can highlight the criteria, but I don’t know how to edit a document in sql format using php code. - Victor Barannik
    • @Victor Barannik, just like with any other file (for example .txt) - Nikolay