PHP, MySQL multiple dropdowns

Discussion in 'Web Design and Development' started by Stocktrader, Feb 4, 2013.

  1. macrumors member

    Joined:
    Oct 28, 2007
    #1
    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);
    ?>
     
  2. elppa, Feb 4, 2013
    Last edited: Mar 9, 2014

    macrumors 68040

    elppa

    Joined:
    Nov 26, 2003
    #2
    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.
     

Share This Page