connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully
";
mysqli_set_charset( $conn, 'utf8' );
//
// queries for creating a table
//
$tables = array("Film", "Actor", "Director", "ActorFilm", "DirFilm");
$sqls = array();
$talbes[0] = "Film";
$sqls[0] = "CREATE TABLE Film (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
subtitle VARCHAR(255) NOT NULL,
year int(4) NOT NULL,
url VARCHAR(255),
userRating FLOAT,
imageUrl VARCHAR(255),
linkUrl TEXT
) CHARACTER SET utf8";
$talbes[1] = "Actor";
$sqls[1] = "CREATE TABLE Actor (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
actor VARCHAR(255) NOT NULL
) CHARACTER SET utf8";
$talbes[2] = "Director";
$sqls[2] = "CREATE TABLE Director (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
dir VARCHAR(255) NOT NULL
) CHARACTER SET utf8";
$talbes[3] = "ActorFilm";
$sqls[3] = "CREATE TABLE ActorFilm (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
filmID INT,
actorID INT
) CHARACTER SET utf8";
$talbes[4] = "DirFilm";
$sqls[4] = "CREATE TABLE DirFilm (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
filmID INT,
dirID INT
) CHARACTER SET utf8";
// check if the table exists already by using a function "table_exist"
for ($j=0;$j 0) {
echo "Table " . $tables[$j] . " already exists.
";
} else if ($conn->query($sqls[$j]) === TRUE) {
echo "Table created with no prob
";
} else {
echo "Error creating table: " . $conn->error . "
"; }
}
echo "
";
//
// end of creating tables
//
// read movie titles for parsing movie info from naver api
$lines = file('movie.dat');
/* api 처리 */
$key = "677eabb7e94745f7f3a043908c540dc5";
for ($i=0;$i\n\n"; // 실제 사용되는 html request를 프린트 해봄
$xml = simplexml_load_file($url); // 결과값을 xml parsing 함
foreach ($xml->channel->item as $movie) {
// echo "$query :: trim(strip_tags($movie->title))
";
if ($query == trim(strip_tags($movie->title))) {
$title = trim(strip_tags($movie->title));
$title = mysqli_real_escape_string($conn, $title);
$subtitle = trim(strip_tags($movie->subtitle));
$subtitle = mysqli_real_escape_string($conn, $subtitle);
// echo "title (sub): " . $title . " (" . $subtitle . ")
";
$year = $movie->pubDate;
$dirString = trim($movie->director);
$actorString = trim($movie->actor);
$dirA = explode("|", $dirString);
$actorA = explode ("|", $actorString);
$userRating = $movie->userRating;
$imageUrl = $movie->image;
$linkUrl = $movie->link;
// 영화 정보 처리
// title check
$sql = "SELECT * FROM Film WHERE title='$title' and year='$year' ";
$res = $conn->query($sql);
if ($res->num_rows == 0) { // title is new
echo "this $title is new.
";
$sql = "INSERT INTO Film(title, subtitle, year, userRating, imageUrl, linkUrl) VALUES ('$title', '$subtitle', '$year', '$userRating', '$imageUrl', '$linkUrl')";
if (mysqli_query($conn, $sql)) {
$fID = mysqli_insert_id($conn);
echo "Inserted film ID is: " . $fID . "
";
} else {
echo "Error: ". $sql . "
" . mysqli_error($conn);
}
// title is new 인경우의 if문 아직 닫지 않음
// director 정보
// almost one; but just in case
for ($j=0;$j<(sizeof($dirA)-1);$j++) {
// check first director exist
// if new
// INSERT into director table
// get ID
// INSERT ID and fID into dirFilm
$sql = "SELECT * FROM Director WHERE dir='$dirA[$j]'";
$res = $conn->query($sql);
if ($res->num_rows == 0) { // director name is new
// INSERT director name into Director table
$sql = "INSERT INTO Director(dir) VALUES ('$dirA[$j]')";
if (mysqli_query($conn, $sql)) {
$dID=mysqli_insert_id($conn);
echo "Director: " . $dirA[$j] . " Inserted director ID is: " . $dID . " and associated film ID: $fID
";
// this
$sql5 = "INSERT INTO dirfilm(filmID, dirID) VALUES ('$fID','$dID')";
insert_sql($conn,$sql5);
} else {
echo "Error: ". $sql . "
" . mysqli_error($conn);
}
} else {
// get the existing director ID
$sql3 = "SELECT id FROM Director WHERE dir='$dirA[$j]' LIMIT 1";
$res3 = $conn->query($sql3);
$row = $res3->fetch_assoc();
$dirID = $row["id"];
echo "director, $dirA[$j] ID: " . $row["id"] ."
";
echo "at the same time: we are holding " .$fID ."
";
$sql = "INSERT INTO dirfilm(filmID, dirID) VALUES ('$fID','$dirID')";
insert_sql($conn,$sql);
}
}
for ($k=0;$k<(sizeof($actorA)-1);$k++) {
// check the actor exist in the actor table with name
// if new INSERT
// get ID
// INSERT actorID with fID
$sql = "SELECT * FROM Actor WHERE actor='$actorA[$k]'";
$res = $conn->query($sql);
if ($res->num_rows == 0) { // actor name is new
// INSERT actor name into actor table
$sql2 = "INSERT INTO Actor(actor) VALUES ('$actorA[$k]')";
$id = insert_sql($conn,$sql2);
$sql4 = "INSERT INTO actorfilm(filmID, actorID) VALUES ('$fID','$id')";
insert_sql($conn,$sql4);
} else { // get the existing actor ID
$sql3 = "SELECT id FROM Actor WHERE actor='$actorA[$k]' LIMIT 1";
$res3 = $conn->query($sql3);
$row = $res3->fetch_assoc();
$actorID = $row["id"];
echo "actor, $actorA[$k] ID: " . $row["id"] ."
";
echo "at the same time: we are holding " . $fID . "
";
$sql = "INSERT INTO actorfilm(filmID, actorID) VALUES ('$fID','$actorID')";
insert_sql($conn,$sql);
}
} // actorA for 문
} // if문 is it new? --> title is new
else {
echo "title (sub): " . $title . " (" . $subtitle . ") is not new.
";
} // 이미 있음
} // if query = search title 인 경우. 쓸데 없는 영화정보가 자꾸 들어가는 걸 막음.
} // foreach
} // checking empty line (else 문)
} // data 영화제목 line
$conn->close();
function insert_sql($conn,$sql) {
if (mysqli_query($conn, $sql)) {
$id = mysqli_insert_id($conn);
// echo "Inserted ID is: " . $id . "
";
return $id;
} else {
// $error = "Error: ". $sql . "
" . mysqli_error($conn);
return mysqli_error($conn);
}
}
function table_exist($table){
global $conn;
$sql = "show tables like '".$table."'";
$res = $conn->query($sql);
return ($res->num_rows > 0);
}
?>