install.packages(c("stringr", "XML", "maps", "RCurl","RMySQL"))
install.packages('knitr',dependencies=TRUE,repos="http://cran.rstudio.com/")

# load pacakages need to be used
library(stringr)

library(XML)

library(maps)

library(RCurl)

library(RMySQL)

library(knitr)

movie <- htmlParse("http://www.imdb.com/chart/moviemeter/?ref_=nv_mv_mpm_7",encoding = "UTF-8")
tables <- readHTMLTable(movie, stringsAsFactors = FALSE)

# what type of structure it is? 
class(tables)

length(tables)

# use str(tables)to check the structure and contents of tables. tables[1] is 'NULL', tables[2] is `amazon-affiliates`, the data I want is contained in tables[1].

# cleaning the data
tables[2] <- NULL  # remove tables[2], which is table$`amazon-affiliates`

# renamed tables[[1]]
movie_df <- tables[[1]]

# rearrange the data
colnames(movie_df)[1] <- "YearRelease"
colnames(movie_df)[2] <- "Title"
colnames(movie_df)[4] <- "MovieID"
movie_df[5] <- NULL

movie_df[,1] <- str_extract (movie_df[,2], "[[:digit:]]{4}")
movie_df[,2] <- str_extract (movie_df[,2], "[[:alpha:] [:punct:]]{2,}")


# get top 6 movies  
movie_top6 <- movie_df[1:6,]
movie_top6[,4] <- c(1, 2, 3, 4, 5, 6)

# create friends table
x <- 1:5
y <- c('Ming', 'Hao', 'Alison', 'Eran', 'Orshi')
FriendsTable <- data.frame(FriendsID=x,FriendsName=y)
kable(FriendsTable)

# generate a series of pretended rating from my friends
a <- c(0, 0.5, -0.5)
set.seed(1)
b<- sample(rep(a,10))

set.seed(2)
c <- sample (1.5:4.5, replace=T, 30)

rating <- b+c

# friend ID
e <- nrow(movie_top6)
fr.ID <- c(rep(1, e),rep(2,e),rep(3,e),rep(4,e),rep(5,e))

# moive ID
mo.ID <- rep(1:6,5)

# friend name
fr.Name <- c(rep('Ming',6),rep('Hao',6),rep('Alison',6),rep('Eran',6),rep('Orshi',6))

# movie name
mo.name<- rep(movie_top6$Title,5)

MainTable <- data.frame(MovieID=mo.ID, MovieName=mo.name, FriendID=fr.ID, FriendName=fr.Name, FriendRating=rating)
kable(MainTable)

MovieTable <- data.frame(MovieID=movie_top6$MovieID, MovieName=movie_top6$Title)
kable(MovieTable)

# connect to MySQL local database
movie_db <- dbConnect(MySQL(), user='root', password=mysql_pw, dbname='movie_rating', host='localhost')

# wirte table in the database
dbWriteTable(movie_db, name="Movie", value=MovieTable, overwrite=FALSE, append=TRUE)

dbWriteTable(movie_db, name="Friends", value=FriendsTable, overwrite=FALSE, append=TRUE)

dbWriteTable(movie_db, name="Rating", value=MainTable, overwrite=FALSE, append=TRUE)

# list tables
dbListTables(movie_db)

dbListFields(movie_db, 'movie')

dbListFields(movie_db, 'friends')

dbListFields(movie_db, 'rating')

#running queries 
rq.movie <- dbSendQuery(movie_db, "SELECT * FROM movie")

#retrieving data from MySQL
moive.data <- dbFetch(rq.movie, n=-1)
kable(moive.data)

#running queries 
rq.friend <- dbSendQuery(movie_db, "SELECT * FROM friends")

#retrieving data from MySQL
friend.data <- dbFetch(rq.friend, n=-1)
kable(friend.data)

#running queries 
rq.rating <- dbSendQuery(movie_db, "SELECT * FROM rating")

#retrieving data from MySQL
rating.data <- dbFetch(rq.rating, n=-1)
kable(rating.data)