php musql Query results showing when no results found.

I have a search page that is searching through movie titles. The problem is that is you search for a work that is not found all titles show up in the results page.
Example:
Search: ball   <-- shows two results // Correct
Search balls <-- Shows all titles // not correct should show none.
 
Here is the site:
http://beta.newcinema.com/search/

I have attached the code below:
<?php
/**
 * Search
 *
 * Search direct in mysql tables using LIKE '%$keyword%'
 * The filter variable is: $add_to_query (will store the filter for the query)
 *

 */

// load the template file needed for this template
$tpl->loadTemplatefile('search.html');
/** includes the global functions */
include(DIR_ROOT . './libs/stars_rating.php');
// set the variables for this particular page
$keyword = filter_input(INPUT_GET, 'keyword', FILTER_SANITIZE_STRING);
$genre_id = isset($_GET['genre']) ? filter_input(INPUT_GET, 'genre', FILTER_VALIDATE_INT) : 0;
$b_isError = false;
if ($_SERVER['REQUEST_METHOD'] === "GET" && $keyword != '')
{
	$add_to_query = '';
	/** Include the Pagination class */
	include(DIR_ROOT . 'libs/classes/Pagination.php');
		$tpl->setVariable('KEYWORD_ADVANCED', $keyword);

		// Validate $genre_id
//		if ($genre_id === 0) {
//			$b_isError	= true;
//			$a_message[]= 'Invalid genre!';
//		}
		$movies_ids = array();
		$search_actor_count		= $dbh->query("SELECT COUNT(actor_id) as cnt FROM actor WHERE actor_name LIKE '%" . $keyword . "%' ")->fetch(PDO::FETCH_OBJ)->cnt;
		$search_movie_count		= $dbh->query("SELECT COUNT(movie_id) as cnt FROM movie WHERE movie_name LIKE '%" . $keyword . "%' ")->fetch(PDO::FETCH_OBJ)->cnt;
		$search_director_count	= $dbh->query("SELECT COUNT(director_id) as cnt FROM director WHERE director_name LIKE '%" . $keyword . "%' ")->fetch(PDO::FETCH_OBJ)->cnt;

		// Searching movies by actor
		if ($search_actor_count > 0) {
			$stmt = $dbh->prepare("
			SELECT
				movie_actor.movie_id
			FROM actor
			LEFT JOIN movie_actor ON movie_actor.actor_id = actor.actor_id
			WHERE actor.actor_name LIKE '%" . $keyword . "%'");
			$stmt->execute();
			while($res = $stmt->fetch(PDO::FETCH_ASSOC)) {
				if ($res['movie_id'] != '') {
					$movies_ids[] .= $res['movie_id'];
				}
			}
		}

		// Searching movies by director
		if ($search_director_count > 0) {
			$stmt = $dbh->prepare("
			SELECT
				movie_director.movie_id
			FROM director
			LEFT JOIN movie_director ON movie_director.director_id = director.director_id
			WHERE director.director_name LIKE '%" . $keyword . "%'");

			$stmt->execute();
			while($res = $stmt->fetch(PDO::FETCH_ASSOC)) {
				if ($res['movie_id'] != '') {
					$movies_ids[] .= $res['movie_id'];
				}
			}
		}

		if ($search_movie_count > 0) {
			$stmt = $dbh->prepare("SELECT movie_id FROM movie WHERE movie_name LIKE '%" . $keyword . "%' AND movie_status = 'enabled' ");
			$stmt->execute();
			while($res = $stmt->fetch(PDO::FETCH_ASSOC)) {
				if ($res['movie_id'] != '') {
					$movies_ids[] .= $res['movie_id'];
				}
			}
		}
		if (count($movies_ids) === 0) {
			$b_isError = true;
			$a_message[] = 'No results!';
			// Popular movies if no results
			$count_query = "SELECT Count(*) AS cnt FROM movie WHERE movie_status = 'enabled' AND 1=1";
			$count_resultz = $dbh->query($count_query)->fetchColumn();
			$pagination = new Pagination(DEFAULT_PER_PAGE, $count_resultz);
			$stmt = $dbh->query("SELECT
									movie.movie_id,
									movie.movie_name,
									movie.movie_rating,
									movie.movie_votes,
									movie.movie_release_date,
									movie.movie_cost,
									movie.movie_cost_type,
									movie_cover.cover_filename
								FROM movie
									LEFT JOIN movie_cover ON (movie.movie_id = movie_cover.movie_id AND movie_cover.cover_default='1')
								WHERE  movie_status = 'enabled'
								ORDER BY movie.movie_rating DESC, movie.movie_votes DESC
								LIMIT " . $pagination->getOffset() . ", " . DEFAULT_PER_PAGE
								);
			$n_col2 = 0;
			while ($movie = $stmt->fetch(PDO::FETCH_ASSOC)) {
				$tpl->setVariable('MOVIE_ID', $movie['movie_id']);
				$tpl->setVariable('MOVIE_NAME', $movie['movie_name']);
				if ('Free' == $movie['movie_cost_type']) {
					$tpl->setVariable('COST', 'Free');
				} else {
					$tpl->setVariable('COST', '$' . $movie['movie_cost']);
				}

				if (++$n_col2 % 4 == "0")
				{
					$tpl->setVariable('DIV_CLEAR', '<div class="clear"></div>');
				}

				$movie_path = '/files/movies/' . floor($movie['movie_id']/1000) . '/' . $movie['movie_id'] . '/covers/';
				if (file_exists(DIR_ROOT . $movie_path . $movie['cover_filename']) && is_file(DIR_ROOT . $movie_path . $movie['cover_filename'])) {
					$tpl->setVariable('COVER_FILENAME', WWW_ROOT . $movie_path . $movie['cover_filename']);
				} else {
					$tpl->setVariable('COVER_FILENAME', WWW_ROOT . '/files/movies/default_cover.jpg');
				}
				$tpl->parse('parse_column');
			}
			$tpl->setVariable('TOTAL_RESULTS', $count_resultz);
			$tpl->setVariable("PAGER_RECORDS", $pagination->display_count("Displaying <strong>%s</strong> to <strong>%s</strong> out of <strong>%s</strong> entries found"));
			$tpl->setVariable("PAGER_LINKS", $pagination->display_links(5, 'search/advanced/true/', '?keyword=' . $keyword . '&genre=' . $genre_id ));
		}

		// If genre, add filter to query
		if ($genre_id != '' && $genre_id > 0) {
			$movie_ids = $dbh->query("SELECT movie_id FROM movie_genre WHERE genre_id=" . $genre_id)->fetchAll(PDO::FETCH_ASSOC);
			if (count($movie_ids) > 0) {
				$movies_genre_ids = array();
				foreach ($movie_ids as $k => $v){
					$movies_genre_ids[] .= $v['movie_id'];
				}
			}elseif($genre_id > 0 && count($movie_ids) === 0){
				$b_isError	= true;
				$a_message[]= 'No movies found in this genre maching your query!';
			}else{
				$b_isError	= true;
				$a_message[]= 'No movies found in this genre maching your keyword!';
			}
		}
		if (isset($movies_genre_ids) && count($movies_genre_ids) > 0) {
			$ids_array = array_intersect($movies_ids, $movies_genre_ids);
			$ids = implode(', ', $ids_array);

			if (count($ids_array) > 0) {
				$add_to_query .= " AND movie.movie_id IN (" . $ids . ") " ;
			}else {
				$add_to_query .= " AND movie.movie_id IN ('') " ;
			}

		}elseif(isset($movies_ids) && count($movies_ids) > 0) {
			$add_to_query .= " AND movie.movie_id IN (" . implode(', ', $movies_ids) . ") " ;
		}

		// If no error was foud, we run the mysql query
		if (false === $b_isError) {
			$count_query = "SELECT Count(*) AS cnt FROM movie WHERE movie_status = 'enabled' AND 1=1 " . $add_to_query . " ";
			$count_results = $dbh->query($count_query)->fetchColumn();

			if ($count_results == 0) {
				$b_isError = true;
				$a_message[] = 'No results!';
			}

			$pagination = new Pagination(DEFAULT_PER_PAGE, $count_results);
			// MySQL query construct
			$query = "
					SELECT
						movie.movie_id,
						movie.movie_name,
						mpaa.mpaa_code,
						movie.movie_rating,
						movie.movie_votes,
						movie.movie_release_date,
						movie.movie_cost_type,
						movie.movie_cost,
						user.user_username,
						user.user_id,
						movie.movie_acl,
						movie.movie_acl_level,
						movie_cover.cover_filename
					FROM movie
					LEFT JOIN mpaa ON movie.mpaa_id = mpaa.mpaa_id
					LEFT JOIN user ON movie.user_id = user.user_id
					LEFT JOIN movie_cover ON movie.movie_id = movie_cover.movie_id AND movie_cover.cover_default='1'
					WHERE movie_status = 'enabled' AND 1=1
					" . $add_to_query . "
					LIMIT " . $pagination->getOffset() . ", " . DEFAULT_PER_PAGE;

			// PDO prepare query
			$stmt = $dbh->prepare($query);
			// Execute mysql query
			$stmt->execute();
			$n_col = 0;
			while($result = $stmt->fetch(PDO::FETCH_ASSOC)){

				/**
				* check if the movie has geographical restriction
				* even if this will make the site hardly loading, todd requested it
				*/
				switch ($result['movie_acl']) {
				case 'all':
					break;
				case 'deny':
					switch ($result['movie_acl_level']){
						case '1000':
							// Country only
							$stmt = $dbh->prepare('SELECT movie_acl_country FROM movie_acl WHERE movie_id=:movie_id');
							$stmt->bindValue(':movie_id', $result['movie_id']);
							$stmt->execute();
							$denied_countries = array();
							while ($result_restriction = $stmt->fetch(PDO::FETCH_ASSOC)) {
								$denied_countries[] = $result_restriction['movie_acl_country'];
							}
							if (in_array($_SESSION['user_location']['country'], $denied_countries)) {
								continue 3;
							}
							break;
						case '1100':
							// Country, region
							$stmt = $dbh->prepare('
								SELECT
								count(movie_acl.movie_acl_id) as cnt
								FROM movie_acl
								LEFT JOIN movie_acl_region ON movie_acl_region.movie_acl_id=movie_acl.movie_acl_id
								WHERE movie_acl.movie_id=:movie_id
								AND movie_acl_region.movie_acl_region =:region AND movie_acl.movie_acl_country=:country
							');
							$stmt->bindValue(':movie_id',  $result['movie_id']);
							$stmt->bindValue(':region' , $_SESSION['user_location']['region']);
							$stmt->bindValue(':country' , $_SESSION['user_location']['country']);
							$stmt->execute();
							if ($stmt->fetch(PDO::FETCH_OBJ)->cnt > 0) {
								continue 3;
							}
							break;
						case '1110':
							// Country, region, city
							$stmt = $dbh->prepare('
								SELECT
								count(movie_acl.movie_acl_id) as cnt
								FROM movie_acl
								LEFT JOIN movie_acl_region ON movie_acl_region.movie_acl_id=movie_acl.movie_acl_id
								LEFT JOIN movie_acl_city ON movie_acl_city.movie_acl_id=movie_acl.movie_acl_id
								WHERE movie_acl.movie_id=:movie_id
								AND movie_acl_region.movie_acl_region =:region
								AND movie_acl.movie_acl_country=:country
								AND movie_acl_city.movie_acl_city=:city
							');
							$stmt->bindValue(':movie_id',  $result['movie_id']);
							$stmt->bindValue(':region' , $_SESSION['user_location']['region']);
							$stmt->bindValue(':country' , $_SESSION['user_location']['country']);
							$stmt->bindValue(':city' , $_SESSION['user_location']['city']);
							$stmt->execute();

							if ($stmt->fetch(PDO::FETCH_OBJ)->cnt > 0) {
								continue 3;
							}
							break;
						case '1111':
							// Country, region, city, zip
							$stmt = $dbh->prepare('
								SELECT
								count(movie_acl.movie_acl_id) as cnt
								FROM movie_acl
								LEFT JOIN movie_acl_region ON movie_acl_region.movie_acl_id=movie_acl.movie_acl_id
								LEFT JOIN movie_acl_city ON movie_acl_city.movie_acl_id=movie_acl.movie_acl_id
								LEFT JOIN movie_acl_zip ON movie_acl_zip.movie_acl_id=movie_acl.movie_acl_id
								WHERE movie_acl.movie_id=:movie_id
								AND movie_acl_region.movie_acl_region =:region
								AND movie_acl.movie_acl_country=:country
								AND movie_acl_city.movie_acl_city=:city
								AND movie_acl_zip.movie_acl_zip=:zip
							');
							$stmt->bindValue(':movie_id',  $result['movie_id']);
							$stmt->bindValue(':region' , $_SESSION['user_location']['region']);
							$stmt->bindValue(':country' , $_SESSION['user_location']['country']);
							$stmt->bindValue(':city' , $_SESSION['user_location']['city']);
							$stmt->bindValue(':zip' , $_SESSION['user_location']['zip']);
							$stmt->execute();

							if ($stmt->fetch(PDO::FETCH_OBJ)->cnt > 0) {
								continue 3;
							}
							break;
					}
					break;
				case 'allow':
					switch ($result->movie_acl_level){
						case '1000':
							// Country only
							$stmt = $dbh->prepare('SELECT movie_acl_country FROM movie_acl WHERE movie_id=:movie_id');
							$stmt->bindValue(':movie_id',  $result['movie_id']);
							$stmt->execute();
							$denied_countries = array();
							while ($result_restriction = $stmt->fetch(PDO::FETCH_ASSOC)) {
								$denied_countries[] = $result_restriction['movie_acl_country'];
							}
							if (!in_array($_SESSION['user_location']['country'], $denied_countries)) {
								continue;
							}
							break;
						case '1100':
							// Country, region
							$stmt = $dbh->prepare('
								SELECT
								count(movie_acl.movie_acl_id) as cnt
								FROM movie_acl
								LEFT JOIN movie_acl_region ON movie_acl_region.movie_acl_id=movie_acl.movie_acl_id
								WHERE movie_acl.movie_id=:movie_id
								AND movie_acl_region.movie_acl_region =:region AND movie_acl.movie_acl_country=:country
							');
							$stmt->bindValue('movie_id',  $result['movie_id']);
							$stmt->bindValue('region' , $_SESSION['user_location']['region']);
							$stmt->bindValue('country' , $_SESSION['user_location']['country']);
							$stmt->execute();
							if ($stmt->fetch(PDO::FETCH_OBJ)->cnt == 0) {
								continue 3;
							}
							break;
						case '1110':
							// Country, region, city
							$stmt = $dbh->prepare('
								SELECT
								count(movie_acl.movie_acl_id) as cnt
								FROM movie_acl
								LEFT JOIN movie_acl_region ON movie_acl_region.movie_acl_id=movie_acl.movie_acl_id
								LEFT JOIN movie_acl_city ON movie_acl_city.movie_acl_id=movie_acl.movie_acl_id
								WHERE movie_acl.movie_id=:movie_id
								AND movie_acl_region.movie_acl_region =:region
								AND movie_acl.movie_acl_country=:country
								AND movie_acl_city.movie_acl_city=:city
							');
							$stmt->bindValue(':movie_id',  $result['movie_id']);
							$stmt->bindValue(':region' , $_SESSION['user_location']['region']);
							$stmt->bindValue(':country' , $_SESSION['user_location']['country']);
							$stmt->bindValue(':city' , $_SESSION['user_location']['city']);
							$stmt->execute();

							if ($stmt->fetch(PDO::FETCH_OBJ)->cnt == 0) {
								continue 3;
							}
							break;
						case '1111':
							// Country, region, city, zip
							$stmt = $dbh->prepare('
								SELECT
								count(movie_acl.movie_acl_id) as cnt
								FROM movie_acl
								LEFT JOIN movie_acl_region ON movie_acl_region.movie_acl_id=movie_acl.movie_acl_id
								LEFT JOIN movie_acl_city ON movie_acl_city.movie_acl_id=movie_acl.movie_acl_id
								LEFT JOIN movie_acl_zip ON movie_acl_zip.movie_acl_id=movie_acl.movie_acl_id
								WHERE movie_acl.movie_id=:movie_id
								AND movie_acl_region.movie_acl_region =:region
								AND movie_acl.movie_acl_country=:country
								AND movie_acl_city.movie_acl_city=:city
								AND movie_acl_zip.movie_acl_zip=:zip
							');
							$stmt->bindValue(':movie_id',  $result['movie_id']);
							$stmt->bindValue(':region' , $_SESSION['user_location']['region']);
							$stmt->bindValue(':country' , $_SESSION['user_location']['country']);
							$stmt->bindValue(':city' , $_SESSION['user_location']['city']);
							$stmt->bindValue(':zip' , $_SESSION['user_location']['zip']);
							$stmt->execute();

							if ($stmt->fetch(PDO::FETCH_OBJ)->cnt == 0) {
								continue 3;
							}
							break;
					}
					break;
				}
				// end geographical restrictions

				// Assign variables for template
				$tpl->setVariable('MOVIE_ID', $result['movie_id']);
				$tpl->setVariable('MOVIE_NAME', $result['movie_name']);
				$tpl->setVariable('COVER_FILENAME', $result['cover_filename']);
				$tpl->setVariable('MOVIE_URL', WWW_ROOT . 'movie_details/movie/' . $result['movie_id']);
				$tpl->setVariable('MPAA_CODE', $result['mpaa_code']);
				$tpl->setVariable('RATING', display_stars($result['movie_rating']));
				$tpl->setVariable('NO_OF_VOTES', $result['movie_votes']);
				$tpl->setVariable('RELEASE_DATE',date(US_DATETIME_FORMAT,$result['movie_release_date']) );
				$tpl->setVariable('COST_TYPE', $result['movie_cost_type']);
				$tpl->setVariable('COST', $result['movie_cost']);
				$tpl->setVariable('USER_USERNAME', $result['user_username']);
				$tpl->setVariable('USER_ID', $result['user_id']);
				$tpl->setVariable('USER_URL', WWW_ROOT . 'profile/' . $result['user_id']);
				$s_dir_path = "/files/movies/" . floor($result['movie_id'] / 1000) . "/". $result['movie_id'] ."/covers/";
				if (file_exists(DIR_ROOT . $s_dir_path . $result['cover_filename']) && is_file(DIR_ROOT . $s_dir_path . $result['cover_filename']))
				{
					$tpl->setVariable('COVER_FILENAME',$s_dir_path . $result['cover_filename']);
				} else {
					$tpl->setVariable('COVER_FILENAME',"/files/movies/default_cover.jpg");
				}

				if (++$n_col % 5 == "0")
				{
					$tpl->setVariable('DIV_CLEAR', '<div class="clear"></div>');
				}
				$tpl->parse("parse_column");
				//$tpl->parse('movie');
			}

			$tpl->setVariable('TOTAL_RESULTS', $count_results);
			$tpl->setVariable("PAGER_RECORDS", $pagination->display_count("Displaying <strong>%s</strong> to <strong>%s</strong> out of <strong>%s</strong> entries found"));
			$tpl->setVariable("PAGER_LINKS", $pagination->display_links(5, 'search/advanced/true/', '?keyword=' . $keyword . '&genre=' . $genre_id ));
		}


	}

	$tpl->setVariable('PAGE_TITLE_ADVANCED', 'Search movie');
	$tpl->setVariable('URL_ADVANCED', WWW_ROOT);

	// Build genres dropdown
	$genres = $dbh->query("SELECT genre_id, genre_name FROM genre")->fetchAll(PDO::FETCH_ASSOC);
	foreach ($genres as $k => $genre){
		$tpl->setVariable('GENRE_ID', $genre['genre_id']);
		if ($genre_id > 0) {
			$tpl->setVariable('GENRE_SELECTED', ($genre['genre_id'] == $genre_id) ? HTML_SELECTED: '');
		}

		$tpl->setVariable('GENRE_NAME', $genre['genre_name']);
		$tpl->parse('genres');
	}

	$tpl->parse('advanced_search');

/** Include header */
include(DIR_ROOT . './includes/common/header.php');
/** Include footer */
include(DIR_ROOT . './includes/common/footer.php');

// show the templates
$tpl->show();

Open in new window

LVL 3
jbrashear72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jaxbrianCommented:
I think that if you put an or die statement below line 98 like the one i posted below you could direct them to some error page. you seem to be having alot going on in the script.

or you may try after line 46 what i posted below after the first die statement.
so it would say if there is no movie_id then display the die message or go to the page you make for it.

try that, if that doesnt work, I am sure an expert will be along shortly. lol

B
1)
{
die (header('Location: http://www.YOURERRORPAGE.com',true,302));
}

2) if ($res['movie_id'] == '') {
die (header('Location: http://www.YOURERRORPAGE.com',true,302));
}

Open in new window

jbrashear72Author Commented:
thanks Jax, Ill give that a shot.
mcbSolutionsCommented:
You are using the wrong JOIN.

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

The INNER JOIN keyword return rows when there is at least one match in both tables.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.