[SOLVED] Advanced search?

[izbryte]

I have a form with two dropdown boxes where the user can search my database of jobs by either job category or location or both. Right now I can pull up results if they select an item from each dropdown (both) but I don't know how to pull up the results if they select only one or the other.

 

if (isset($_POST['Search'])) { // Handle the form. 

	// Check for an location. 
	   if (!empty($_POST['location'])) { 
		  $l = ($_POST['location']); 
		  $search = TRUE;
	   }
	// Check for a category. 
	   if (!empty($_POST['category'])) { 
		  $c = ($_POST['category']); 
		  $search = TRUE;
	   } 
	$query  = "SELECT * FROM jobs WHERE (location = '$l') AND ( category = '$c') ORDER BY job_id DESC";	

}

 

So now, if they only select a location (for example) then no results will show. How can I fix this?

[hitman6003]

<?php

if (isset($_POST['Search'])) {

$query  = "SELECT * FROM jobs WHERE ";	

// Check for an location. 
if (!empty($_POST['location'])) { 
	$query .=  "location = '" . mysql_real_escape_string($_POST['location']) . "'"; 
	$search = TRUE;
}

// Check for a category. 
	if (!empty($_POST['category'])) { 
		if (!empty($_POST['location']) {
			$query .= " AND ";
		}

		$query .= "category = '" . mysql_real_escape_string($_POST['category']) . "'";

	$search = TRUE;
} 

$query .= "ORDER BY job_id DESC";

}

// or

if (isset($_POST['Search'])) {
$where = array();

// Check for an location. 
if (!empty($_POST['location'])) { 
	$where[] .=  "location = '" . mysql_real_escape_string($_POST['location']) . "'"; 
	$search = TRUE;
}

// Check for a category. 
	if (!empty($_POST['category'])) { 
	$where[] .= "category = '" . mysql_real_escape_string($_POST['category']) . "'";
	$search = TRUE;
} 

$query  = "SELECT * FROM jobs WHERE " . implode(" AND ", $where) . " ORDER BY job_id DESC";

}

[jvrothjr]

Build your query based on selection

 

$query = "select * from jobs"
   if ($l <> "" or $c <> "") {$query = $query." where";}
   if ($l <> "") {$query = $query." Location = '$l' ";}
   if ($l <> "" and $c <> "") {$query = $query." and";}
   if ($c <> "") {$query = $query." Catrgory = '$c' ";}
$query = $query." ORDER BY job_id DESC";

[revraz]

Probably an easier way, but this should work

 

if (isset($_POST['Search'])) { // Handle the form. 

	// Check for an location. 
	   if (!empty($_POST['location'])) { 
		  $l = ($_POST['location']); 
		  $search = TRUE;
	   }
	// Check for a category. 
	   if (!empty($_POST['category'])) { 
		  $c = ($_POST['category']); 
		  $search = TRUE;
	   } 
                         (empty($POST['category'] || empty($_POST['location'])) ? $condition="OR" : $condition="AND";
	$query  = "SELECT * FROM jobs WHERE (location = '$l') '$condition' ( category = '$c') ORDER BY job_id DESC";	

[mr_mind]

Try this on for size:

 

<?php
if(isset($_POST['Search'])) {
	if(isset($_POST['location'])) {
		$search_loc = $_POST['location'];
	}
	elseif(isset($_POST['category'])) {
		$search_cat = $_POST['category'];
	} 
	$sql = "SELECT * FROM jobs WHERE ";
	if(isset($search_loc)) {
		$sql .= "location='" . $search_loc . "' ";
	}
	if(isset($search_loc) && isset($search_cat)) {
		$sql .= "AND ";
	}
	if(isset($search_cat)) {
		$sql .= "category='" . $search_cat . "' ";
	}
	$sql .= "ORDER BY job_id DESC";
	$query = mysql_query($sql);
	$array = mysql_fetch_array($query);
	print_r($array);
}
?>

[izbryte]

Wow! So many options and I couldn't even think of one! Thanks guys!