通过PHP将一个表从MySQL复制到SQLite

I have a bunch of music data in my MySQL database, and I need to copy it to another MySQL, but only selected columns (Artist, Song, Duration).

我的MySQL数据库中有一堆音乐数据,我需要将它复制到另一个MySQL,但只能选择列(艺术家,歌曲,持续时间)。

MySQL 127.0.0.1 - Database Music

MySQL 127.0.0.1 - 数据库音乐

| Artist | Song   | Duration | Location    | Bitrate | Rating | Times Played |
------------------------------------------------------------------------------
| Bob    | Song 1 |  11:06   | C:\b\1.mp3  | 160kbps | 5      | 132          |
| Jack   | Song 7 |   0:06   | C:\j\7.mp3  | 160kbps | 2      | 10           |
| Mike   | Song 3 |   3:06   | C:\m\3.mp3  | 128kbps | 4      | 150          |
| Mike   | Song 5 |   5:06   | C:\m\5.mp3  | 128kbps | 1      | 222          |
| ------------------------------ 5000+ records ------------------------------|

SQLite 192.168.10.100 - Database Music

SQLite 192.168.10.100 - 数据库音乐

| Artist | Song   | Duration |
------------------------------
| Bob    | Song 1 |  11:06   | 
| Mike   | Song 3 |   3:06   | 
| Mike   | Song 5 |   5:06   | 
|~~~~~~~ and so on... ~~~~~~~|

The challenge is to copy three columns where the the Times Played is over 100+, so Jack song Song 7 shouldn't be copied over the new database.

挑战是复制Times Played超过100的三列,因此Jack Song Song 7不应该复制到新数据库中。

While I tried to just dump a select query to a database.sql file, and feeding it to SQLite, the dumping has to be manually done each time. Instead, I need a PHP script that I can CRON every day.

当我尝试将select查询转储到database.sql文件并将其提供给SQLite时,每次都必须手动完成转储。相反,我需要一个我每天都可以CRON的PHP脚本。

1 个解决方案

#1

basic approach:

INSERT INTO databese1.Music (  Artist , Song   , Duration )
SELECT  Artist , Song   , Duration
FROM    database2.Music

you will need to uniquely identify each database

您需要唯一标识每个数据库