<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Page Title</title>
</head>
<body>
<?php
$servername = "127.0.0.1";
$username = "hkim";
$password = "hkimaleldj";
// Create connection
$conn = new mysqli($servername, $username, $password, $username);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully <br /><br />";
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<sizeof($tables);$j++) {
if (table_exist($tables[$j]) > 0) {
echo "Table " . $tables[$j] . " already exists.<br />";
} else if ($conn->query($sqls[$j]) === TRUE) {
echo "Table created with no prob <br />";
} else {
echo "Error creating table: " . $conn->error . "<br />"; }
}
echo "<br />";
//
// 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<sizeof($lines);$i++) {
$query=trim($lines[$i]);
if ($query == '') { //skip for empty line
} else {
$url = "http://openapi.naver.com/search?key=".$key."&query=\"".$query."\"&target=movie";
// echo $url ."\n\n<br />\n\n"; // 실제 사용되는 html request를 프린트 해봄
$xml = simplexml_load_file($url); // 결과값을 xml parsing 함
foreach ($xml->channel->item as $movie) {
// echo "$query :: trim(strip_tags($movie->title)) <br><br>";
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 . ")<br />";
$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. <br>";
$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 . "<br />";
} else {
echo "Error: ". $sql . "<br>" . 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 <br />";
// this
$sql5 = "INSERT INTO dirfilm(filmID, dirID) VALUES ('$fID','$dID')";
insert_sql($conn,$sql5);
} else {
echo "Error: ". $sql . "<br>" . 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"] ."<br>";
echo "at the same time: we are holding " .$fID ."<br>";
$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"] ."<br>";
echo "at the same time: we are holding " . $fID . "<br>";
$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. <br />";
} // 이미 있음
} // 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 . "<br>";
return $id;
} else {
// $error = "Error: ". $sql . "<br>" . 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);
}
?>
</body></html>