Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Special Interests > Visual Media > Web Design and Development

Reply
 
Thread Tools Search this Thread Display Modes
Old Feb 4, 2013, 11:55 AM   #1
Stocktrader
macrumors member
 
Join Date: Oct 2007
PHP, MySQL multiple dropdowns

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);
?>
Stocktrader is offline   0 Reply With Quote
Old Feb 4, 2013, 05:42 PM   #2
elppa
macrumors 68040
 
elppa's Avatar
 
Join Date: Nov 2003
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 Code:
$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 Code:
$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.

Last edited by elppa; Mar 9, 2014 at 04:49 AM.
elppa is offline   0 Reply With Quote

Reply
MacRumors Forums > Special Interests > Visual Media > Web Design and Development

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 11:39 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC