PHP, MySQL multiple dropdowns

Stocktrader · Feb 4, 2013

Thanks in advance for any help.

I currently have a dropdown that pulls "sport" from database.
I need help figuring out how to pull from MULTIPLE drop downs.

Ideally, I'd like 4-5 dropdown boxes that act like a filter.
If none are selected the entire table is listed and results are updated as dropdowns are selected.

<html>
<head>
<script>
function showUser(str)
{
if (str=="")
{
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getuser2.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>

<form>
<select name="sport" onchange="showUser(this.value)">
<option value="">Select a sport:</option>
<option value="Hockey">Hockey</option>
<option value="Soccer">Soccer</option>
<option value="Basketball">Basketball</option>
<option value="Football">Football</option>
<option value="Baseball">Baseball</option>
<option value="Other">Lax & Other Sports</option>
</select>
</form>
<br>
<div id="txtHint"><b>Sport info will be here.</b></div>

<form>
<select name="state" onchange="showUser(this.value)">
<option value="">Select a State:</option>
<option value="NY">NY</option>
<option value="NV">NV</option>
<option value="GA">GA</option>
<option value="NH">NH</option>
<option value="OH">OH</option>
<option value="CT">CT</option>
</select>
</form>
<br>
<div id="txtHint"><b>State info will be here.</b></div>

</body>
</html>

getuser.php:
<?php
$q=$_GET["q"];

$con = mysql_connect("localhost","nystockt_steve","eli91363");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("nystockt_test", $con);

$sql="SELECT * FROM tourney WHERE sport = '".$q."'";

$result = mysql_query($sql);

echo "<table border='1'>
<tr>
<th>Sport</th>
<th>State</th>
<th>Male</th>
<th>Female</th>
<th>Indoor</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['sport'] . "</td>";
echo "<td>" . $row['state'] . "</td>";
echo "<td>" . $row['male'] . "</td>";
echo "<td>" . $row['female'] . "</td>";
echo "<td>" . $row['indoor'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

elppa · Feb 4, 2013

Disclaimer – I am a bit tired, so I may have made some mistakes, but hopefully this should set you on your way.

You actually have most of the pieces in place.

The first step is on the server side. You need to dynamically build up the query. So now, instead of just one parameter being sent, we are to expect multiple.

I put these it a multi-dimensional array (or map) so we can save a bit of time later building up the query.

PHP:

$params = array();
$sport = $_GET["sport"];
if($sport != "")
{
	$params["sport"] = $sport;
}

$state = $_GET["state"];
if($state != "")
{
	$params["state"] = $state;
}

The next step is to build up the query, using the parameters above. We may have a sport and a state, just a state, just a sport or neither sport nor state. Either way, the query will be valid.

PHP:

$sql = "SELECT * FROM tourney";

$i = 0;
for($params as $column_name => $parameter_value)
{
	if($i == 0)
	{
		$sql .= " WHERE ";
	}
	else
	{
		$sql .= " AND ";
	}
	
	$sql .= "$column_name = \"{$parameter_value}\"";
	$i++;
}

$result = mysql_query($sql);

That was the hard part. Now the easy bit.

We just need to sort out the JavaScript so that it sends multiple parameters. This should hopefully be self explanatory:

Code:

function loadResults()
{
	if (window.XMLHttpRequest)
	{
		// code for IE7+, Firefox, Chrome, Opera, Safari
		xmlhttp=new XMLHttpRequest();
	}
	else
	{
		// code for IE6, IE5
		xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
	}

	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
			document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
		}
	}
	
	var sport = document.getElementById("sport");
	var state = document.getElementById("state");
	xmlhttp.open("GET","getuser2.php?sport=" + sport.value + "&state=" + state.value, true);
	xmlhttp.send();
}

Finally, a couple of small markup changes to the HTML, to add IDs (unique identifiers to the menus) and to call loadResults() instead of showUser().

Code:

<select name="sport" id="sport" onchange="loadResults()">

Code:

<select name="state" id="state" onchange="loadResults()">

Obviously there are lots more improvements which can be made. But with this in place, you should be able to easily filter by additional criteria.

Search

Search

PHP, MySQL multiple dropdowns

Stocktrader

macrumors member

elppa

macrumors 68040

Our Staff