Ok, here is my situation.
I use a script that scans a directory of videos that retrieves imdb info on the videos and then stores the data in text files. 1 per movie. I want to take the data from the txt files and insert into database.
The script would need to check to see whats new and what isn't and only insert the new info.
This is how all the txt files are laid out.
09/04/2015 02:11:54
Cast Away
movie
tt0162222
Adventure,Drama
7.7
videos/Cast.Away.2000.mp4
pg_13
A FedEx executive must transform himself physically and emotionally to survive a crash landing on a deserted island.
Explaination of each line
Line 1 - Date txt/time file created
Line 2 - Title of Movie
Line 3 - Type of video (Movie or TV show)
Line 4 - image file associated with movie
Line 5 - Genre
Line 6 - Viewer Rating (0.0 to 10)
Line 7 - video directory
Line 8 - Movie Rating
Line 9 - Description
So basically I need to figure out how to get the txt data into a mysql database so that I can use it on my home media server. Basically I'm making a netflix like site for my Large DVD collection.
BTW the directory layout is
/data - (txt files)
/pics - (cover photos)
/videos - (video files)
script.php
This is updated code (9/5/2015 4:22) Still the same result. Blank row.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Test script.</title>
</head>
<body>
<?php
$host = "localhost";
$username = "testing";
$password = "*******";
$database = "zadmin_testing";
$textFilesArray = scandir("data");
foreach( $textFilesArray AS $textFile )
{
$handle = fopen($textFile, "r");
# Get the lines of the file
$dateCreated = fgets($handle);
$videoTitle = fgets($handle);
$videoType = fgets($handle);
$videoImage = fgets($handle);
$videoGenre = fgets($handle);
$videoViewerRating = fgets($handle);
$videoPath = fgets($handle);
$videoMovieRating = fgets($handle);
$videoDesc = fgets($handle);
}
// Creating my connection
{
$conn = mysql_connect("$host","$username","$password");
//Checking connection
if (!$conn)
{
die('Could not connect: ' . mysql_error());
}
}
//My query
$query = "INSERT INTO `zadmin_testing`.`videos` (`date`, `title`, `type`, `cover`, `genre`, `vrating`, `path`, `mrating`, `description`) VALUES ('$dateCreated', '$videoTitle', '$videoType', '$videoImage', '$videoGenre', '$videoViewerRating', '$videoPath', '$videoMovieRating', '$videoDesc')";
{
//Run my query
mysql_query($query);
}
echo "<h2>testing 12</h2>";
mysql_close($conn);
?>
</body>
</html>
Assuming all the text files you want to import into the database are in one folder, this is what I would do:
First, use scandir() to get a listing of all the text files in the directory.
$textFilesArray = scandir("directoryContainingTheFiles");
Then, you can loop through the files
foreach( $textFilesArray AS $textFile ){
Read all the data out of the file....
# Open the file for reading (r)....
$handle = fopen($textFile, "r");
# Get the first line of the file
$dateCreated = fgets($handle);
# get the next line from the file
$movieTitle = fgets($handle);
Now, you have several options on how to avoid duplicate records.
one option would be to save a flag at the beginning / end of the file, and when the flag is present, skip the attempt to insert the movie.
another option would be to create a unique key on one of your mySQL fields. (perhaps on the movie title). Then, when you do your inserts, no duplicates will be created. (duplicate entry attempts will result in error, which means nothing will happen. If you want to suppress those mySQL errors, you could always use "INSERT IGNORE ...." instead of "INSERT ....")
so in summary:
$textFilesArray = scandir("directoryContainingTheFiles");
foreach( $textFilesArray AS $textFile ){
# Open the file for reading (r)....
$handle = fopen($textFile, "r");
# Get the first line of the file
$dateCreated = fgets($handle);
# get the next line from the file
$movieTitle = fgets($handle);
# get the rest of the file into variables here
# preform checks on variables before attempting the insert
# (make sure strings are escaped, and number values contain all numbers, etc.)
# PREFORM mySQL INSERT QUERY HERE!
# something like: "INSERT INTO movies (date, title, etc) VALUES ($dateCreated, $movieTitle, etc)"
}