Need help with SQL query..

I'm trying to use search filters to output a database, The search filters are basically stuff in the database which I want it to sort by:

$type = $_POST['Type'];
		$genre = $_POST['Genre'];
		$location = $_POST['Location'];
		$year = $_POST['Year'];
		$rating = $_POST['Rating'];

PHP:

Here I select entries in drop down lists which correspond to the things I want displayed. For example the Location menu will have listed different countries, if I select "canada" I want the database to output entries which contain only Canada, etc..

$sql = "SELECT * FROM CNF WHERE Type = '$type'
				UNION
		SELECT * FROM CNF WHERE Genre = '$genre'
				UNION 
		SELECT * FROM CNF WHERE Country = '$location'
				UNION 
		SELECT * FROM CNF WHERE Year = '$year'
				UNION 
	 	SELECT * FROM CNF WHERE Rating = '$rating'";

PHP:

However this only works if I select only one filter at a time, If I select mutliple filters it doesn't work so well. Example, I want entries from Canada in the year 2006 - This will display all entries with Canada, as well as all entries with 2006, But I want just the combination of the two.. as well as the others..

Any ideas how to combine all those search filters into one query?

 

$fields 	= array("Type", "Genre", "Location", "Year", "Rating");
$conditions = array();

foreach($fields as $field)
{
	if($_POST[$field])
	{
		$conditions[] = $field . " = '" . mysql_real_escape_string($_POST[$field]) . "'";
	}
}

$sql = "SELECT * FROM CNF";

if(count($conditions))
{
	$sql .= " WHERE " . implode(" AND ", $conditions);
}

PHP:

 

Instead of UNION, why don't you just use OR?
SELECT * FROM CNF WHERE Type = '$type' OR Genre = '$genre' OR ...

 

use AND if you want results that match ALL of the conditions.
use OR if you want results that match ANY of the conditions.

 

Right - union isn't what you want here.