Multiple dropdown lists

PHP
1

I have 2 dropdown lists the one has US States in it and the other has cities that I get the information from a database. When I select the first one it should automatically show the cities in that state. Below is my code?


<td>

    <?php
	
	$sql = "SELECT SID, StateName FROM State";
	$result = mysql_query($sql);
	//$result = mysql_fetch_array(mysql_query($queryData));   //$result now has database tables
	
	echo '<select name="State" id="State">';
	echo '<option selected="selected">Please select a State</option>';
	
	while($row = mysql_fetch_array($result))
	{
		
    	echo '<option values=' . $row["SID"] . '>' . $row["StateName"] . '</option>';

	}
	echo '</select>';
	
	
	?>

    </td>
    <td>City</td>
    <td>
    <?php
	$parent_cat = $_GET['State'];
	
	$Mysql = "SELECT CID, CityName FROM City WHERE StateID =" . $parent_cat;
	$Data = mysql_query($Mysql);
	
	echo '<select name="City" id="City">';
	echo '<option selected="selected">Please select a City</option>';
	
	while($rs = mysql_fetch_array($Data))
	{
		
    	echo '<option values=' . $rs["CID"] . '>' . $rs["CityeName"] . '</option>';

	}
	echo '</select>';
	
	?>
    </td>
2

I presume you are using javascript to submit the form? Otherwise it doesn’t seem clear how you are expecting to use the $_GET variable?

Also it looks like your code is vulnerable to sql injection. (you don’t clean or validate the $parent_cat variable).

3

Thanks RT. I am new to PHP and JavaScript. Does anyone know of links where I can read up on SQL Injection.

Do I use a onChange event to get the ID of state and then declare it?

4

I think you would be better off concentrating on PHP then coming to JavaScript later. Because it is good practice for your website to be able to function without any JavaScript, and if the PHP isn’t solid, it will be easy to introduce many security risks.

This is probably the best place to start:
http://www.php.net/manual/en/tutorial.forms.php

Or ideally, invest a little in a proper book to take you through the concepts. Well worth the time and money!
http://www.sitepoint.com/store/php-mysql-novice-to-ninja/

Sorry I couldn’t help more directly, but I think there is too much to cover here.

Best of luck
-RT-

5

To avoid SQL injection (assuming you are using a mySQL database) simply use prepare and bind to keep the data separate from the SQL rather than jumbling them together.

All user inputs should be validated as the first thing in your PHP code. Any values being passed that you set and which the user is not supposed to touch but potentially could alter should be sanitized. Only once data has been validated or sanitised should it be processed.

Most coding examples leave out the validate/sanitize code to save space as depending on what you are going to do with the data this initial processing might be most of the script.

6

The first drop down list shows the initial database data but when one makes a selection the page refreshes but does not show in the drop down list. the second dropdownlist does not show any data.


<?Php


//$cat=$_GET['cat'];

//echo $cat;

$queryData = mysql_query("SELECT SID, StateName FROM State");
    echo "<select name='State' id='State' onchange='reload(this.form)'>";
	echo "<option>Select State</option>";
    while($row2 = mysql_fetch_array($queryData))
	{
		if($row2["StateID"]==$cat)
		{
			echo '<option selected="selected" value=' . $row2["SID"] . '>' . $row2["StateName"] . '</option>';
		}
		else
		{
			echo  '<option value=' . $row2["SID"] . '>' . $row2["StateName"] . '</option>';
		}
	}
    echo '</select>';
?>
					
 </td>
<td>City</td>
 <td>

<?php
						
//////////        Starting of second drop downlist /////////
if(isset($cat) and strlen($cat) > 0){
$quer="SELECT CityName, CID FROM City where StateID=". $cat  ;
}else{$quer="SELECT CityName, CID FROM City"; }

echo '<select name="City" id="City">';
echo "<option>Select City</option>";
    while($row = mysql_fetch_array($quer))
    {
		
        echo '<option values=' . $row["SID"] . '>' . $row["CityName"] . '</option>';
    }
	echo '</select>';
//////////////////  This will end the second drop down list ///////////

						
?>

Thanks

7

Well you need to submit the form, and I think you need double quote marks for attribute tags.

echo '<select name="State" id="State" onchange="this.form.submit()">';

And I’m assuming you have a form element in your code that you are not showing here?

8

No it calls a javascript function where it reloads the page and supposed to change the 2 dropdownlists accordingly.


<SCRIPT language=JavaScript>
<!--
function reload(form)
{
var val=form.State.options[form.State.options.selectedIndex].value;
self.location='Hospital.php?cat=' + val ;
}

//-->

</script>

I have done some testing and in the if statements it only goes into the first if and not in the else if… like when I am trying to set the selected value in the state it makes them all selected.

9

When I change the State drop down list the page reloads but does not make the selected value equal to what was changed.

[javascript]
function reload(form)
{
var val=form.State.options[form.State.options.selectedIndex].value;
self.location=‘Hospital.php?cat=’ + val ;
}
[/javascript]


<?Php 


//$cat=$_GET['cat']; 

//echo $cat; 

$queryData = mysql_query("SELECT SID, StateName FROM State");   
    echo "<select name='State' id='State' onchange='reload(this.form)'>"; 
    echo "<option>Select State</option>";   
    while($row2 = mysql_fetch_array($queryData))   
    { 
        if($row2["StateID"]==$cat) 
        { 
            echo '<option selected="selected" value=' . $row2["SID"] . '>' . $row2["StateName"] . '</option>'; 
        } 
        else 
        { 
            echo  '<option value=' . $row2["SID"] . '>' . $row2["StateName"] . '</option>'; 
        }  
    } 
    echo '</select>'; 
?> 
                     
 </td> 
<td>City</td> 
 <td> 
                     
<?php 
                         
//////////        Starting of second drop downlist ///////// 
if(isset($cat) and strlen($cat) > 0){ 
$quer=mysql_query("SELECT CityName, CID FROM City where StateID=". $cat)  ;  
}else{$quer=mysql_query("SELECT CityName, CID FROM City"); }  

echo '<select name="City" id="City">';  
echo "<option>Select City</option>";  
    while($row = mysql_fetch_array($quer))   
    { 
           
        echo '<option values=' . $row["SID"] . '>' . $row["CityName"] . '</option>';   
    }   
    echo '</select>'; 
//////////////////  This will end the second drop down list /////////// 

                         
?>