entertain

knitr::opts_chunk$set(echo = TRUE)

CODE

mysqldbconn <- function(schema, uid, pwd){
     
     
     library(RMySQL)
     
     rmysql.settingsfile<-"C:/ProgramData/MySQL/MySQL Server 5.6/my.ini"
     
  #  rmysql.settingsfile<-"https://raw.githubusercontent.com/DataDriven-MSDA/DATA607/master/Week2A/config/my.ini"
     conn <- dbConnect(RMySQL::MySQL(), dbname = schema, username=uid ,password = pwd)
     
          return(conn)
     
}

##################################################################################################
# @ Kumudini Bhave 
# DATA 607 Week 2 Assignment
# 
# Libraries Used : RCurl, countrycode, ggplot2, ggthemes, gcookbook
# ################################################################################################

# Loading RCurl package to help scrape data from web (stored on GitHub).
library(RCurl)

## Loading required package: bitops

library(ggplot2)
library(gcookbook)
library(countrycode)
library(ggthemes)

# Library for data display in tabular format

library(DT)

# Forming MYSQL DB Connection

conn <- mysqldbconn("entertainment","root","WhiteLotus21")

## Loading required package: DBI

# List the database tables

entdbtables <- dbListTables(conn)

entdbtables

## [1] "certificate" "genre"       "movies"      "survey"

# List Survey Table Details

for(i in entdbtables)  # for each row
{
     cat("\nTable Name : ", i,"\n")
     
     coltab <- dbListFields(conn,i)
     cat("Columns : ","\n",coltab,"\n")
     
     
     if (i == "survey")
     {
          cat(" We have a SURVEY!!")
          cat("\n\n")
          querystr<-paste("select count(*) from ",i)
          
          if(isIdCurrent(conn))
          {
               cat("\nConn current")
               sres <- dbGetQuery(conn,querystr)
               print(sres)
               
               
          }
          
     }
}

## 
## Table Name :  certificate 
## Columns :  
##  certificateid certificatedesc 
## 
## Table Name :  genre 
## Columns :  
##  genreid genretype 
## 
## Table Name :  movies 
## Columns :  
##  movieid moviename genre yearreleased leadcast dirrectedby countryreleased language watchtimemin certificate awards info 
## 
## Table Name :  survey 
## Columns :  
##  respondentid movieid rating responsedt 
##  We have a SURVEY!!
## 
## 
## Conn current  count(*)
## 1       90

# Capturing genre based movie lists

if(isIdCurrent(conn))
{
     
     action <- dbGetQuery(conn,"select movieid, genre from movies where genre='A'")
     horror <- dbGetQuery(conn,"select movieid, genre from movies where genre='H'")
     animated <- dbGetQuery(conn,"select movieid, genre from movies where genre='N'")
     adventure <- dbGetQuery(conn,"select movieid, genre from movies where genre='V'")
     western <- dbGetQuery(conn,"select movieid, genre from movies where genre='W'")
     
}

#    View(action)
#   View(horror)
#   View(animated)
#   View(adventure)
#   View(western)

# Viewing the movie, survey join

     joinstr<-paste("select respondentid, survey.movieid, moviename, genre, rating from movies, survey where movies.movieid = survey.movieid")
     smjoinres <- dbGetQuery(conn,joinstr)
     

     
     datatable(smjoinres, options = list(
          searching = FALSE, 
          pageLength = 5,
          lengthMenu = c(5, 10, 15, 20)
     ), rownames = FALSE)

    # print(dim(smjoinres))
     
     ratehi <- subset(smjoinres, rating >= 5 )
     
     View(ratehi)
     
     resp1liked <- subset(ratehi, respondentid == 1, select=c(movieid,genre))
 #    recom1 <- sample(horror$movieid - resp1liked$movieid, size=1, replace = FALSE)
     
     
     
   #  plot(smjoinres$moviename,smjoinres$rating)
     View(smjoinres)
  
dbDisconnect(conn)

## [1] TRUE

#@https://raw.githubusercontent.com/DataDriven-MSDA/DATA607/master/Week2A/sql/entertainment.sql
#gapminder.gitdata <- getURL(data.giturl)