how can i get countries states list from states ta

2019-09-17 18:32发布

站内问答 / PHP
468 3 4

how to get countries states list from states table when i select country from countries table using multiple selection dropdown list ? here is my coding.

mysql tables

CREATE TABLE `countries` (
  `countryID` varchar(3) NOT NULL default '',
  `countryName` varchar(52) NOT NULL default '',
  `localName` varchar(45) NOT NULL,
  `webCode` varchar(2) NOT NULL,
  `region` varchar(26) NOT NULL,
  `continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL,
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  `surfaceArea` float(10,2) NOT NULL default '0.00',
  `population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`countryID`),
  UNIQUE KEY `webCode` (`webCode`),
  UNIQUE KEY `countryName` (`countryName`),
  KEY `region` (`region`),
  KEY `continent` (`continent`),
  KEY `surfaceArea` (`surfaceArea`),
  KEY `population` (`population`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Structure for the states table :

CREATE TABLE `states` (
  `stateID` smallint(5) unsigned NOT NULL auto_increment,
  `stateName` varchar(50) NOT NULL default '',
  `countryID` varchar(3) NOT NULL,
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  PRIMARY KEY  (`stateID`),
  KEY `stateName` (`stateName`),
  KEY `countryID` (`countryID`),
  KEY `unq` (`countryID`,`stateName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data for the countries table (LIMIT 0,500)

INSERT INTO `countries` (`countryID`, `countryName`, `localName`, `webCode`, `region`, `continent`, `latitude`, `longitude`, `surfaceArea`, `population`) VALUES 
  ('BRA','Brazil','Brasil','BR','South America','South America',-10,-55,8547403.00,170115000),
  ('CHN','China','Zhongquo','CN','Eastern Asia','Asia',35,105,9572900.00,1277558000),
  ('FRA','France','France','FR','Western Europe','Europe',47,2,551500.00,59225700),
  ('IND','India','Bharat/India','IN','Southern and Central Asia','Asia',28.47,77.03,3287263.00,1013662000),
   ('USA','USA','United States','US','North America','North America',38,-97,9363520.00,278357000);
COMMIT;

Data for the states table (LIMIT 0,500)

INSERT INTO `states` (`stateID`, `stateName`, `countryID`, `latitude`, `longitude`) VALUES 
  (5,'California','USA',37.42,-122.06),
  (6,'Beijing','CHN',39.93,116.39),
  (9,'Iowa','USA',43.03,-96.09),
  (10,'New York','USA',40.76,-73.97),
  (12,'... ....','CHN',32.06,118.78);
COMMIT;

Countries dropdown

<? $Type_sql="SELECT countryName FROM countries ORDER by countryName ASC";
                                $Type_result=mysql_query($Type_sql);
                                while($Type_rows=mysql_fetch_array($Type_result)){
                                echo "<option value='"; 
                                echo $Type_rows['countryName']; 
                                echo "'>";
                                echo $Type_rows['countryName'];
                                echo "</option>";
                                } ?>
            </select>

states dropdown

<? $Type_sql="SELECT stateName FROM countries c, states s where c.countryID = s.countryID ORDER by stateName ASC";
                                $Type_result=mysql_query($Type_sql);
                                while($Type_rows=mysql_fetch_array($Type_result)){
                                echo "<option value='"; 
                                echo $Type_rows['stateName']; 
                                echo "'>";
                                echo $Type_rows['stateName'];
                                echo "</option>";
                                } ?>
            </select>

3条回答
走好不送
2楼-- · 2019-09-17 19:04

Hi Ahmed,

Please have a look at the below code. I reworked the code and modified for multiple selections Anyway i have ignored the javascript validation. please work on that and name of this page was form3.php

<html>
<head>
<script type="text/javascript">
    function viewstates()
    { 
        jQuery('#langucountry').change(function () { });
    }
    $(function() {
        $('#basicmultiselect').multiSelect({select_all_min: 3});
        $('#langucountry').multiSelect({
            select_all_min: 3, no_selection: "Please select!", selected_text: " clicked" });
        $('#methods').multiSelect(); });
</script>
</head>
<body>
<form name="form3" action="form3.php" method="post">
<?php
/****** connection string *******/
/* start */
    $conn = mysql_connect("localhost","root","");
    $dbSelect = mysql_select_db("test");
/* end */

/*----checks whether form is submited -------*/
if(isset($_POST['cbocountry']))
{
    $lstStates = array();
        foreach($_POST['cbocountry'] as $countryCode)
        {
            $fetch_states="SELECT stateName FROM  states where countryID = '$countryCode' ORDER by stateName ASC";
            $Type_result=mysql_query($fetch_states);
            while($Type_rows=mysql_fetch_array($Type_result)){

                        if(!in_array($Type_rows['stateName'],$lstStates))
                            array_push($lstStates,$Type_rows['stateName']);
             } 
        }

}


                                $Type_sql="SELECT countryID,countryName FROM countries ORDER by countryName ASC";
                                $Type_result=mysql_query($Type_sql);
                                echo"Countries:<select  id=\"langucountry\" name=\"cbocountry[]\" class=\"arc90_multiselect\" multiple=\"multiple\" title=\"Languages\"\>";
                                while($Type_rows=mysql_fetch_array($Type_result)){

                                        echo "<option value='".$Type_rows['countryID']."'>";
                                        echo $Type_rows['countryName'];
                                        echo "</option>";
                                } 
                                echo"</select>";





                    echo"States:<select><option value=''>--select--</option>";
                            if(count($lstStates) > 0)
                            {
                                foreach($lstStates as $item)
                                {
                                    echo "<option value='"; 
                                    echo $item; 
                                    echo "'>";
                                    echo $item;
                                    echo "</option>";
                                } 
                            }
                    echo"</select>";

                    echo"<input type=\"submit\" value=\"submit\" text=\"submit\"/>";

?>
</form>
</body>
</html>

Enjoy!!!!

查看更多
0人赞 添加讨论(0) 举报
霸刀☆藐视天下
3楼-- · 2019-09-17 19:20

This can be accomplished by putting the code for the states dropdown into a separate php handler, a bit modified:

<select name="state">
<?php 
    $countryID= mysql_real_escape_string($_GET['countryID']);
    $Type_sql="SELECT stateName FROM states s where s.countryID = '$countryID' ORDER by stateName ASC";
                $Type_result=mysql_query($Type_sql);
                while($Type_rows=mysql_fetch_array($Type_result)){
                    echo "<option value='";
                    echo $Type_rows['stateName'];
                    echo "'>";
                    echo $Type_rows['stateName'];
                    echo "</option>";
                } 
?>
</select>

This script will be called by javascript using Ajax every time there is an onChange event in the countries select list. Please refer to ajax documentation like http://www.javascriptkit.com/dhtmltutors/ajaxgetpost.shtml or use jQuery for that: http://api.jquery.com/jQuery.get/

Modifying the content of the state select like your plan is now is possible too, but then you'd need two select lists, one more hidden list to store your removed entries or just all of them.

查看更多
0人赞 添加讨论(0) 举报
做自己的国王
4楼-- · 2019-09-17 19:22

This is a fully working code. Just place this code in a php page and change the name of the form in the onChange parameter to your page name.Also, change the database name in mysql_select_db.

My page name was form1.php

<html>
<body>
<form name="form1">
<?php

if(isset($_REQUEST['stateId']))
{
    $stateId = $_REQUEST['stateId'];
}
else
{
    $stateId = "";
}
$conn = mysql_connect("localhost","root","");
$dbSelect = mysql_select_db("test");
$Type_sql="SELECT countryID,countryName FROM countries ORDER by countryName ASC";
                                $Type_result=mysql_query($Type_sql);
                                echo"Countries:<select onChange=\"window.location='form1.php?stateId='+this.value\">";
                                while($Type_rows=mysql_fetch_array($Type_result)){
                                    if($Type_rows['countryID']==$_REQUEST['stateId'])
                                    {
                                        echo "<option value='".$Type_rows['countryID']."' selected>";
                                        echo $Type_rows['countryName'];
                                        echo "</option>";
                                    }
                                    else
                                    {
                                        echo "<option value='".$Type_rows['countryID']."'>";
                                        echo $Type_rows['countryName'];
                                        echo "</option>";
                                    }
                                } 
                                echo"</select>";

 $Type_sql="SELECT stateName FROM  states where countryID = '$stateId' ORDER by stateName ASC";
                                $Type_result=mysql_query($Type_sql);
                    echo"States:<select><option value=''>--select--</option>";
                                while($Type_rows=mysql_fetch_array($Type_result)){
                                echo "<option value='"; 
                                echo $Type_rows['stateName']; 
                                echo "'>";
                                echo $Type_rows['stateName'];
                                echo "</option>";
                                } 
                    echo"</select>";

?>
</form>
</body>
</html>
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)