CODEWITHSUNDEEP
phpmysqldatabase
rharper
rharper

Reputation: 565

Adapt MySQL database according to text file

I'm doing a movie database project to learn MySQL and also PHP. So far, I've got a Python script to search a folder and see if a file was added or removed. If it has detected that a file was added or removed, it will run another Python script that gets the name of the movie file and then searches OMDb and returns certain values (like title, year, plot, rating, etc). This information is then stored into a file for MySQL to use.

The file is formatted like

"\N Title Year Plot Rating" (tabs between each)

In my PHP file I have this code:

if (!mysqli_query($con, "LOAD DATA LOCAL INFILE '/home/user/movies.txt' INTO TABLE films")) {
    printf("Errormessage: %s\n", mysqli_error($con));
}

$result = mysqli_query($con, "SELECT title, yr, genre, plot, rating FROM films") or die("Unable to query.");

And then after that I have a loop which echoes some HTML to produce a table.

This all works fine. When I add a file, Python detects it, gets the movie info from the internet and adds the info to a file, and then PHP loads that information to a MySQL database and then displays it.

However, when I remove a file from the directory that Python looks in, the file for the MySQL db is updated, but MySQL still has the removed movie/file in the database.

How could I get MySQL (through PHP) to load the data from the file, but replacing it instead of appending it?

Upvotes: 1

Views: 60

Answers (1)

SArnab
SArnab

Reputation: 585

If you want the table to be completely identical to the input file, the simplest solution is to TRUNCATE it beforehand (assuming there are no foreign keys pointing to the rows).

TRUNCATE will wipe the table clean, after which the LOAD DATA command will insert the fresh, new rows.

Upvotes: 1

Related Questions