<?
//this class contains functions that generate sql queries specific for this application.
class Sql {

	//main function, call on init
	function Sql() {
		
	}
	
	//check if a movie exists
	function checkID($id) {
		return "
		SELECT *
		FROM movie
		WHERE id = '$id'
		";
	}
	
	//add movie
	function addMovie($id,$title,$cover,$year,$rating,$tagline,$outline,$outlinemore) {
		return "
		INSERT INTO movie (id,title,cover,year,rating,tagline,outline,outlinemore)
		VALUES (
			'".$id."',
			'".str_replace("'","\'",$title)."',
			'".str_replace("'","\'",$cover)."',
			'".$year."',
			'".$rating."',
			'".str_replace("'","\'",$tagline)."',
			'".str_replace("'","\'",$outline)."',
			'".str_replace("'","\'",$outlinemore)."'
		)
		";
	}
	
	//check for existence of a genre
	function getGenre($genre) {
		return "
		SELECT *
		FROM genre
		WHERE genre = '".strtolower($genre)."'
		";
	}
	
	//check for existence of an actor
	function getActor($actor) {
		return "
		SELECT *
		FROM actor
		WHERE name = '".strtolower($actor)."'
		";
	}
	
	//check for existence of an director
	function getDirector($director) {
		return "
		SELECT *
		FROM director
		WHERE name = '".strtolower($director)."'
		";
	}
	
	//create a genre
	function createGenre($genre) {
		return "
		INSERT INTO genre (genre)
		VALUES ('".str_replace("'","\'",$genre)."')
		";
	}
	
	//create an actor
	function createActor($actor) {
		return "
		INSERT INTO actor (name)
		VALUES ('".str_replace("'","\'",$actor)."')
		";
	}
	
	//create an director
	function createDirector($director) {
		return "
		INSERT INTO director (name)
		VALUES ('".str_replace("'","\'",$director)."')
		";
	}
	
	//add a movie to a genre
	function addMovieToGenre($movieid,$genreid) {
		return "
		INSERT INTO moviegenre (movieid,genreid)
		VALUES ('$movieid','$genreid')
		";
	}
	
	//add an actor to a movie
	function addActorToMovie($movieid,$actorid,$part) {
		return "
		INSERT INTO role (actorid,movieid,part)
		VALUES ('$actorid','$movieid','$part')
		";
	}
	
	//add an director to a movie
	function addDirectorToMovie($movieid,$directorid) {
		return "
		INSERT INTO directed (directorid,movieid)
		VALUES ('$directorid','$movieid')
		";
	}
	
	//get all movies
	function getAllMovies() {
		return "
		SELECT *
		FROM movie
		ORDER BY rating DESC
		";
	}
	
	//get a movie cast
	function getCast($movieid) {
		return "
		SELECT role.id,actor.name,role.part
		FROM role
		LEFT JOIN actor ON actor.id = role.actorid
		WHERE movieid = '".$movieid."'";
	}

	
}
?>