CODEWITHSUNDEEP
phpmysqlsql-serverdatabasesqlite
DarkGhostHunter
DarkGhostHunter

Reputation: 1550

Copying one table from MySQL to SQLite through PHP

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 127.0.0.1 - Database Music

| 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

| 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.

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.

Upvotes: 1

Views: 349

Answers (1)

user557846
user557846

Reputation:

basic approach:

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

you will need to uniquely identify each database

Upvotes: 1

Related Questions