Page 1 of 1

Filter retrieved data with more than one select box

Posted: Thu Aug 08, 2013 7:34 pm
by komplexia
Hi!

I am creating a database and have made an example table (persons) for this post with the colums age, city, name and gender. On my webpage I have four select boxes for age, city, name and gender to filter what will displays in content of the page.

My function below has three parts. The first and the second show all the rows if there is no selection in the neither of the four select boxes. The last part show every row that meet the criterias in all four rows.

I also want to be able to show every row with values that meet the criterias when I select only one, two or three select boxes. I could write a part for every possible combination, but is there a better and more effective solution?

Here is the function:

Code: Select all

function show_data(){
	
	if(!isset($_POST['retrieved_gender']) && !isset($_POST['retrieved_age']) && !isset($_POST['retrieved_city']) && !isset($_POST['retrieved_name'])){
		$result=mysql_query("SELECT * from persons") or die(mysql_error());
		while($row=mysql_fetch_array($result)){
			echo $row['name']."<br />".$row['age']."<br />".$row['city']."<br />".$row['gender'];
		}
	}

	else

	if($_POST['retrieved_gender'] == "" && $_POST['retrieved_age'] == "" && $_POST['retrieved_city'] == "" && $_POST['retrieved_name'] == ""){
		$result=mysql_query("SELECT * from persons") or die(mysql_error());
		while($row=mysql_fetch_array($result)){
			echo $row['name']."<br />".$row['age']."<br />".$row['city']."<br />".$row['gender'];
		}
	}

	else
	if($_POST['retrieved_gender'] && $_POST['retrieved_age'] && $_POST['retrieved_city'] && $_POST['retrieved_name']){
		$gender = $_POST['retrieved_gender'];
		$age = $_POST['retrieved_age'];
		$city = $_POST['retrieved_city'];
		$name = $_POST['retrieved_name'];
		$result=mysql_query("SELECT * from persons WHERE gender = '$gender' && age = '$age' && city = '$city' && name = '$name'") or die(mysql_error());
		while($row=mysql_fetch_array($result)){
			echo $row['name']."<br />".$row['age']."<br />".$row['city']."<br />".$row['gender'];
		}
	}
}
Here is the webpage:

Leftbar:

Code: Select all

<?php select_gender( (isset($_POST['retrieved_gender']) ? $_POST['retrieved_gender'] : null) ); ?>
<?php select_age( (isset($_POST['retrieved_age']) ? $_POST['retrieved_age'] : null) ); ?>
<?php select_city( (isset($_POST['retrieved_city']) ? $_POST['retrieved_city'] : null) ); ?>
<?php select_name( (isset($_POST['retrieved_name']) ? $_POST['retrieved_name'] : null) ); ?>

Content:

Code: Select all

show_data();
Suzanne

Re: Filter retrieved data with more than one select box

Posted: Fri Aug 09, 2013 8:58 am
by Christopher
Maybe something like this:

Code: Select all

function show_data(){
	$conditions = array();
	if(isset($_POST['retrieved_gender'])) {
		$gender = mysql_real_escape_string($_POST['retrieved_gender']);
		$conditions[] = "gender = '$gender'";
 	}
	if(isset($_POST['retrieved_age'])) {
		$age = mysql_real_escape_string($_POST['retrieved_age']);
		$conditions[] = "age = '$age'";
 	}
	if(isset($_POST['retrieved_city'])) {
		$city = mysql_real_escape_string($_POST['retrieved_city']);
		$conditions[] = "city = '$city'";
 	}
	if(isset($_POST['retrieved_name'])) {
		$name = mysql_real_escape_string($_POST['retrieved_name']);
		$conditions[] = "name = '$name'";
 	}
	$sql = "SELECT * from persons";
	if ($conditions) {
		$sql .= " WHERE " . implode(' AND ', $conditions);
	}
	$result=mysql_query($sql);
	if (!mysql_errno()) {
		while($row=mysql_fetch_assoc($result)){
			echo $row['name']."<br />".$row['age']."<br />".$row['city']."<br />".$row['gender'];
		}
	} else {
		echo "Error: " . mysql_error();
	}
}
PS - switch to the mysqli extension.