Location: PHPKode > projects > TVEz - Media Library > tvez-0.2.1/html/shared/mysql.inc
<?php

# ------------------------------------------------------------------------------
#
# This file contains the mysql php functions used for the movies
#
# ------------------------------------------------------------------------------
#
# Copyright (C) 2003 Christian Eheim and Alex Pachikov
#                                                                               
# This file is part of TVEz (tvez.sourceforge.net).
#                                                                               
# TVEz is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#                                                                               
# TVEz is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#                                                                               
# You should have received a copy of the GNU General Public License
# along with TVEz; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#                                                                               
# ------------------------------------------------------------------------------
#
# Created on 02/12/2003 by Christian Eheim (hide@address.com)
#
# LAST MODIFIED:
# $Date: 2004/01/25 02:11:34 $
# $Revision: 1.6 $
# $Author: eheim $
#
# ------------------------------------------------------------------------------

# ------------------------------------------------------------------------------
# Handler for database errors
# ------------------------------------------------------------------------------
function dbErrorHandler($errstr) {
#	echo '<span class="error">'.localize_string("Configuration Error").':<br>'.$errstr.'</span>';
	global $DBERROR;
	$DBERROR = $errstr;
}
function myDBErrorHandler($errno, $errstr, $errfile, $errline) {
	dbErrorHandler($errstr);
	return;
}

# ------------------------------------------------------------------------------
# Updates the rating for a movie
# ------------------------------------------------------------------------------
function cast_vote($id,$points) {

#	require "../config/config_file.php";
	global $MYSQL;

	# --------------------------------------------------------- 
	# Establish a mysql connection
	# ---------------------------------------------------------
	$old_error_handler = set_error_handler("myDBErrorHandler");
	if (! $db_connection = mysql_connect($MYSQL{'host'},$MYSQL{'user'},$MYSQL{'passwd'}) ) {
		dbErrorHandler(mysql_error());
		return false;
	}
	if(! $db_select = mysql_select_db($MYSQL{'db'}) ) {
		dbErrorHandler(mysql_error());
		return false;
	}

	# --------------------------------------------------------- 
	# Generate the update string
	# --------------------------------------------------------- 
	$update = "UPDATE movies set votes=votes+1, points=points+$points where id = $id";
	if (mysql_query($update)) $ret = true;
	else $ret = false;
	mysql_close($db_connection);
	return $ret;
}

# ------------------------------------------------------------------------------
# Gets a list of all the existing categories
# ------------------------------------------------------------------------------
function get_genres() {

	global $MYSQL;

	$list = array();

	$query = "SELECT DISTINCT genres FROM movies";

	# --------------------------------------------------------- 
	# Establish a mysql connection
	# ---------------------------------------------------------
	$old_error_handler = set_error_handler("myDBErrorHandler");
    if (! $db_connection = mysql_connect($MYSQL{'host'},$MYSQL{'user'},$MYSQL{'passwd'}) ) {
		dbErrorHandler(mysql_error());
		return false;
	}
    if (! $db_select = mysql_select_db($MYSQL{'db'}) ) {
		dbErrorHandler(mysql_error());
		return false;
	}

	if ($db_query = mysql_query($query,$db_connection)) {
		while ($movie =  mysql_fetch_row($db_query)) {
			$fields = preg_split("/\//", $movie{'0'});
			foreach ($fields as $field)  
				if ($field != "")
					array_push($list, $field);
		}
	} else {
		dbErrorHandler(mysql_error());
		return false;
	}

    mysql_close($db_connection);

	$uList = array_unique($list);
	sort($uList);
    return $uList;
}

# ------------------------------------------------------------------------------
# Generate the mysql query string
# ------------------------------------------------------------------------------
function generate_query($order, $genres, $search,$field) {

	global $MYSQL;

	# --------------------------------------------------------------------------
	# Set the ordering
	# --------------------------------------------------------------------------
	if ($order == "date") { $order .= " DESC,title"; }
	elseif ($order == "vote") { $order = "points/votes DESC,title"; }
	elseif ($order == "") { $order = "title"; }
	else { $order .= ",title"; }

	# --------------------------------------------------------------------------
	# Add the genre
	# --------------------------------------------------------------------------
	$gStr = ""; $inStr = "";
	if ($genres != "") {
		$gStr = " AND genres regexp \"".$genres."\"";
		$inStr = " in ".$genres;
	}

	# --------------------------------------------------------------------------
	# If there is a search string we are creating the query
	# --------------------------------------------------------------------------
	if ($search != "") {
		$search = preg_replace("/ \((I|II|III|IV|V|VI)\)/","",$search);
		# ----------------------------------------------------------------------
		# If the desired search field is set, we are adding it
		# ----------------------------------------------------------------------
		if ($field != "all") {
			if ($field == "time")
				$query = "SELECT * FROM movies where $field regexp \"$search\"".$gStr." ORDER by $field,$order";
			else if ($field == "id")
				$query = "SELECT * FROM movies where id=$search";
			else
				$query = "SELECT * FROM movies where $field regexp \"$search\"".$gStr." ORDER by $order";
			$str = "'$search' in ".ucfirst($field)."$inStr.";
		# ----------------------------------------------------------------------
		# otherwise, we search in all fields
		# ----------------------------------------------------------------------
		} else {
			# How should this be done???
			#       $query = "SELECT * FROM movies where ".
			#               "having \"$search\"";

			$query = "SELECT * FROM movies where ".
					"(title regexp \"$search\" OR ".
					"aka regexp \"$search\" OR ".   # this doesn't work (alex) -> sure it does (chris)
					"year regexp \"$search\" OR ".
					"director regexp \"$search\" OR ".
					"writers regexp \"$search\" OR ".
					"actors regexp \"$search\" OR ".
					"country regexp \"$search\" OR ".
					"plot regexp \"$search\" OR ".
					"id = \"$search\" OR ".
					"path regexp \"$search\" OR ".
					"language regexp \"$search\")";
			 
			$str = "'$search' in all available";
			if ($HTTP_POST_VARS{'genres'} != "") {
				$query .= $gStr;
				$str .= " ".$HTTP_POST_VARS{'genres'};
			}
			$query .= " order by $order";
			$str .= " movies.";
		}
	# --------------------------------------------------------------------------
	# If there is no search string
	# --------------------------------------------------------------------------
	} else  { #  if ($search != "")
		# ----------------------------------------------------------------------
		# If the genre is selected we search it
		# ----------------------------------------------------------------------
		if ($genres != "") {
			$query = "select * from movies where genres regexp \"".$genres."\" order by $order";
			$str = "all available ".$genres." movies.";
		# ----------------------------------------------------------------------
		# Otherwise we select all movies
		# ----------------------------------------------------------------------
		} else {
			$query = "select * from movies order by $order";
			$str = "all available movies.";
		}
	} # if ($search != "")

	#echo $query;
	return $query;
}

# ------------------------------------------------------------------------------
# Querries the database
# ------------------------------------------------------------------------------
function query_db($query) {

#	require "../config/config_file.php";
	global $MYSQL;

	# --------------------------------------------------------- 
	# Establish a mysql connection
	# ---------------------------------------------------------
	$old_error_handler = set_error_handler("myDBErrorHandler");
	if (! $db_connection = mysql_connect($MYSQL{'host'},$MYSQL{'user'},$MYSQL{'passwd'}) ) {
		dbErrorHandler(mysql_error());
		return array(0,0);
	}
	if (! $db_select = mysql_select_db($MYSQL{'db'}) ) {
		dbErrorHandler(mysql_error());
		return array(0,0);
	}

	$entries = array();
	if ($db_query = mysql_query($query,$db_connection)) {
		$movie_total = mysql_affected_rows();
        while ($db_fields =  mysql_fetch_array($db_query)) {
			if (isset($db_fields['id'])) 
				while (strlen($db_fields['id'])<7)
					$db_fields['id'] = "0".$db_fields['id'];
			array_push($entries,$db_fields);
		}
	} else {
		dbErrorHandler(mysql_error());
		$movie_total = 0;
		$entries	= '';
	}
	mysql_close($db_connection);

	return array($movie_total,$entries);
}

# ------------------------------------------------------------------------------

# ------------------------------------------------------------------------------
# Delete a movie entry
# ------------------------------------------------------------------------------
function delete_movie($id) {

	require "admin/image_lib.php";

	global $MYSQL;

	# --------------------------------------------------------- 
	# Establish a mysql connection
	# --------------------------------------------------------- 
	$old_error_handler = set_error_handler("myDBErrorHandler");
	if (! $db_connection = mysql_connect($MYSQL{'host'},$MYSQL{'user'},$MYSQL{'passwd'}) ) {
		dbErrorHandler(mysql_error());
		return;
	}
	if (! $db_select = mysql_select_db($MYSQL{'db'}) ) {
		dbErrorHandler(mysql_error());
		return;
	}

	$query = "DELETE from movies where id=$id";

	# Remove the movie from the database
	if ($db_query = mysql_query($query,$db_connection)) {
		print "
		<blockquote>
		<p><p>
		<b>".localize_string("Deleted movie with ID = ==1==", $id)."</b>
		<p>
		".localize_string("The movie has been deleted from the database, but the file still exists on your system if you haven't deleted it yet. As long as the file remains on your system, the update script will prompt you if you want to add it to the database").".
		<p>
		".localize_string("To keep the update from trying to re-add this movie, remove it from the movie path specified in the configuration file").".
		</blockquote>
		<blockquote>
		";
		# Remove the images for the movie
		if (remove_images($id)) {
			print localize_string("Successfully removed the images from your system.");
		}
		else {
			print "<span class=\"warning\"><b>
				".localize_string("Could not delete images for movie ==1==", $id).".<br>
				".localize_string("Remove them manually from your system").".
				</b></span>";
		}
		print "</blockquote>";
	} else 
		print "<span class=\"warning\"><b>".localize_string("Could not delete movie ==1==",$id).".</b></span>";

		echo '
		<blockquote>
		'.localize_string("Return to the ==1==Admin Page", '<a href="tvez.php?content=admin">').'</a>
		</blockquote>
		';

	mysql_close($db_connection);
}

# ------------------------------------------------------------------------------
# Insert a movie
# ------------------------------------------------------------------------------
function insert_movie($movie) {

	global $MYSQL;
	# --------------------------------------------------------- 
	# Establish a mysql connection
	# --------------------------------------------------------- 
	$old_error_handler = set_error_handler("myDBErrorHandler");
	if (! $db_connection = mysql_connect($MYSQL{'host'},$MYSQL{'user'},$MYSQL{'passwd'}) ) {
		dbErrorHandler(mysql_error());
		return false;
	}
	if (! $db_select = mysql_select_db($MYSQL{'db'}) ) {
		dbErrorHandler(mysql_error());
		return false;
	}

	 $replace = "REPLACE INTO movies VALUES('".$movie['id']."','".$movie['title']."','".$movie['aka']."','".$movie['path']."','".$movie['imgext']."','".$movie['year']."','".$movie['director']."','".$movie['writers']."','".$movie['actors']."','".$movie['genres']."','".$movie['time']."','".$movie['country']."','".$movie['language']."','".$movie['plot']."','".$movie['votes']."','".$movie['points']."','".$movie['played']."','".$movie['quality']."','".$movie['date']."')";

	if ($db_query = mysql_query($replace,$db_connection)) {
		mysql_close($db_connection);
		return true;
	} else {
		mysql_close($db_connection);
		return false;
	}
}

?>
Return current item: TVEz - Media Library