链接下拉列表与HTML + PHP + MySQL [英] Link Dropdown List with HTML+PHP+MySQL
本文介绍了链接下拉列表与HTML + PHP + MySQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想知道如何将三个下拉列表链接在一起。我想让用户选择国家，然后选择州，然后选择城市。实际上，我尝试了多种方法，但我所做的一切如下：
< div class =row>
< div class =col-sm-4>< div class =form-group>
< label for =countries>国家< / label>
< select class =form-controlid =countries>
<？php
$ servername =localhost;
$ username =root;
$ password =123;
$ dbname =countries;
$ conn = new mysqli（$ servername，$ username，$ password，$ dbname）;
$ sql =SELECT * FROM countries;
$ result = $ conn-> query（$ sql）;
// echo< select id ='countries'>;
while（$ row = $ result-> fetch_assoc（））{
echo< option value ='。 $ row ['name']。 >中。 $ row ['name']。 < /选项> 中;
}
// echo< / select>;
$ conn-> close（）;？>
< / select>
< / div>< / div>
< div class =col-sm-4>< div class =form-group>
< label for =countries>状态< / label>
<？php
$ servername =localhost;
$ username =root;
$ password =123;
$ dbname =countries;
//创建连接
$ conn = new mysqli（$ servername，$ username，$ password，$ dbname）;
if（$ _ POST ['submit']&& $ _POST ['submit']！= 0）
{
$ countries = $ _ POST ['countries'];
}
// echo连接成功;
$ sql =SELECT * FROM country where country_id = $ countries;
$ result = $ conn-> query（$ sql）;
// echo< select id ='states'>;
while（$ row = $ result-> fetch_assoc（））{
echo< option value ='。 $ row ['name']。 >中。 $ row ['name']。 < /选项> 中;
}
// echo< / select>;
$ conn-> close（）;？>
< / select>
< / div>< / div>
< / div>
我需要做的是制作三个下拉列表（其中2个显示在上面）用户首先选择国家，然后第二个下拉菜单显示基于用户选择的州/地区，最后根据选定的州选择城市。请告诉我这样做的解决方案。
改变事件。
请不要使用PDO编写我的解决方案我知道你需要mysqli，但是我没有太多时间重新写整个
的东西给mysqli
但是我希望你会看到如何逻辑做到这一点，希望你可以自己将它转换为mysqli，而不是忙碌。
我的索引文件
< script src =jquery.min.js>< / script>
<？php require'db_config.php';？>
< script type =text / javascript> $（'＃country'）。on（'change'，function（）{
var countryID = $（this）.val（）;
if（countryID）{
$ .ajax（{
type：'POST'，
url：'locations.php'，
data：'country_id ='+ countryID，
success：function（html ）{
$（'＃state'）。html（html）;
$（'＃city'）。html（'< option value =>先选择状态< / option> '）;
}
}）;
} else {
$（'＃state'）。html（'< option value =>先选择国家< / option>'）;
$（'＃city'）。html（'< option value =>先选择状态< / option>'）;
}
$（this）.remove（'has-errors'）;
}）; （stateID）
$（'＃state'）。on（'change'，function（）{
var stateID = $（this）.val（）;
{
$ .ajax（{
type：'POST'，
url：'locations.php'，
data：'state ='+ stateID，
成功：function（html）{
$（'＃city'）。html（html）;
}
}）;
} else {
$（'＃城市'）。html（'< option value =>选择城市第一< / option>'）;
}
}）;
< / script>
< form method =POSTaction =id =reg_form>
< option value =0>选择国家< / option>
<？php
$ stmt = $ dbh->准备（SELECT countryID，countryName FROM countries ORDER BY countryName ASC）;
$ stmt-> execute（）;
$ results = $ stmt-> Fetchall（PDO :: FETCH_ASSOC）;
$ b $ if（count（$ results）> 0）{
foreach（$ results as $ row）{
echo'< option value =''。$ row [ 'countryID'] '>'。$行[ '国家名称']。 '< /选项>';
}
} else {
echo'< option value => country not available< / option>';
}
？>
< / select>
< option value =>先选择国家< / option>
< / select>
< option value =>先选择状态< / option>
< / select>
< / form>
locations.php
<？php
/ **
*由PhpStorm创建。
*用户：Masivuye
*日期：2016/12/19
*时间：上午11:27
* /
需要'db_config.php';
$ b if（isset（$ _ POST [country_id]）&&！empty（$ _ POST [country_id]））{
$ sql = $ dbh-> prepare（SELECT DISTINCT states.StateID，states INNER JOIN countries ON states states COUNTID =？）;
$ sql-> bindValue（1，$ _ POST ['country_id']）;
$ sql-> execute（）;
$ results = $ sql-> fetchall（PDO :: FETCH_ASSOC）;
if（count（$ results）> 0）{
echo'< option value =0>选择状态< / option>';
foreach（$ results as $ row）{
echo'< option value =''。$ row ['stateID']。'>'。$ row ['stateName']。' < /选项>';
}
} else {
echo'< option value =>状态不可用< / option>';
$ b if（isset（$ _ POST [state]）&&！empty（$ _ POST [state ））{
$ sql = $ dbh-> prepare（SELECT DISTINCT cities.cityID，cities.cityName，cities.stateID from cities INNER JOIN states ON cities.stateID =？）;
$ sql-> bindValue（1，$ _ POST ['state']）;
$ sql-> execute（）;
$ results = $ sql-> fetchall（PDO :: FETCH_ASSOC）;
if（count（$ results）> 0）{
echo'< option value =0>选择城市< / option>';
foreach（$ results as $ row）{
echo'< option value ='。$ row ['cityID']。'>'。$ row ['cityName']。' < /选项>';
}
} else {
echo'< option value =>城市不可用< / option>';
}
}
？>
db_config.php
<？php
$ servername =localhost;
$ username =hidden;
$ password =hidden;
$ dbname =mytestDB;
尝试{
$ dbh =新的PDO（mysql：host = $ servername; dbname = $ dbname，$ username，$ password）;
$ dbh-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
$ b} catch（PDOException $ e）{
echoCould not connect。$ e-> getMessage（）;
error_log（$ e）;
}
？>
我的表格
CREATE TABLE IF NOT EXISTS`states`（
`stateID` int（6）NOT NULL AUTO_INCREMENT，
`stateName` varchar（255）NOT NULL，
countryID` int（6）NOT NULL，
PRIMARY KEY（`stateID`）
）ENGINE = InnoDB DEFAULT CHARSET = latin1;
-
- 表'states`的转储数据
-
INSERT INTO`states`（`stateID`，`stateName` ，'countryID`）VALUE
（3，'Western Cape'，2），
（4，'东开普'，1），
（5，'North West'，2） ，
（6，'Northen Cape'，2）;
-
- 表cities的表结构
-
创建表如果不存在`cities`（
`cityID` int（6）NOT NULL AUTO_INCREMENT，
`cityName` varchar（255）NOT NULL，
`stateID` int（6）NOT NULL，
PRIMARY KEY `cityID`）
）ENGINE = InnoDB DEFAULT CHARSET = latin1;
-
- 表城市的倾销数据
-
插入`cities`（`cityID`，`cityName` ，'stateID`）VALUE
（1，'Cape Town'，3），
（2，'East London'，4）;
- -------------------------------- ------------------------
-
- 表'countries`的表结构
-
CREATE TABLE IF NOT EXISTS`countries`（
`countryID` int（6）NOT NULL AUTO_INCREMENT，
`countryName` varchar（255）NOT NULL，
PRIMARY KEY（`countryID`）
）ENGINE = InnoDB DEFAULT CHARSET = latin1;
-
- 表'countries`的倾销数据
-
插入`countries`（`countryID`，`countryName` ）价值
（1，'南非'），
（2，'赞比亚'），
（3，'津巴布韦'），
（4，'乌干达'） ，
（5，'USA'），
（6，'巴西'），
（7，'印度'），
（8，'Austrilia'），
（9，'加纳'）;
- ---------------------------------------- ----------------
注意：在桌子上使用自己的字符集，你可以使用utf-8
希望这会指出你正确的方向，希望其他SO用户能够帮助我错过任何东西。
I want to know how I can link three dropdown lists together. I want to let user select the country, then select the state and then select the city. Actually I tried multiple methods but What I have done is as bellow:
<div class="row">
<div class="col-sm-4"><div class="form-group">
<label for="countries">Country</label>
<select class="form-control" id="countries">
<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname = "countries";
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "SELECT * FROM countries";
$result = $conn->query($sql);
//echo "<select id='countries'>";
while ($row = $result->fetch_assoc()) {
echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
}
//echo "</select>";
$conn->close();?>
</select>
</div></div>
<div class="col-sm-4"><div class="form-group">
<label for="countries">State</label>
<select class="form-control" id="states">
<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname = "countries";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if($_POST['submit'] && $_POST['submit'] != 0)
{
$countries=$_POST['countries'];
}
//echo "Connected successfully";
$sql = "SELECT * FROM regions where country_id = $countries";
$result = $conn->query($sql);
//echo "<select id='states'>";
while ($row = $result->fetch_assoc()) {
echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
}
//echo "</select>";
$conn->close();?>
</select>
</div></div>
</div>
What I need to do is to make three dropdown lists (2 of them are shown above) to let the users select country first, then the second dropdown shows the states/regions based on user selection and finally select city based on selected state.Please show me the solution to do this.
解决方案
I had written the code for this sometime last year, what you need is to use ajax, have on change event.
Please not I had written my solution using PDO I know yours requires mysqli, but however I don't have much of a time to re write the whole thing to mysqli
But I hope you will see the logic on how to do this and hopefully you will be able to convert it to mysqli by yourself with no hustle.
my index file
<script src="jquery.min.js"></script>
<?php require 'db_config.php';?>
<script type="text/javascript">
$('#country').on('change', function() {
var countryID = $(this).val();
if (countryID) {
$.ajax({
type: 'POST',
url: 'locations.php',
data: 'country_id=' + countryID,
success: function(html) {
$('#state').html(html);
$('#city').html('<option value="">Select state first</option>');
}
});
} else {
$('#state').html('<option value="">Select country first</option>');
$('#city').html('<option value="">Select state first</option>');
}
$(this).remove('has-errors');
});
$('#state').on('change', function() {
var stateID = $(this).val();
if (stateID) {
$.ajax({
type: 'POST',
url: 'locations.php',
data: 'state=' + stateID,
success: function(html) {
$('#city').html(html);
}
});
} else {
$('#city').html('<option value="">Select city first</option>');
}
});
</script>
<form method="POST" action="" id="reg_form">
<select name="country" id="country" class="input">
<option value="0">Select country</option>
<?php
$stmt= $dbh->Prepare("SELECT countryID, countryName FROM countries ORDER BY countryName ASC");
$stmt->execute();
$results= $stmt->Fetchall(PDO::FETCH_ASSOC);
if(count($results) > 0){
foreach($results as $row){
echo '<option value="'.$row['countryID'].'">'.$row['countryName'].'</option>';
}
}else{
echo '<option value="">country not available</option>';
}
?>
</select>
<select name="state" id="state" class="input">
<option value="">Select country first</option>
</select>
<select name="city" id="city" class="input">
<option value="">Select state first</option>
</select>
</form>
locations.php
<?php
/**
* Created by PhpStorm.
* User: Masivuye
* Date: 2016/12/19
* Time: 11:27 AM
*/
require 'db_config.php';
if(isset($_POST["country_id"]) && !empty($_POST["country_id"])){
$sql=$dbh->prepare("SELECT DISTINCT states.stateID,states.stateName from states INNER JOIN countries ON states.countryID = ? ");
$sql->bindValue(1,$_POST['country_id']);
$sql->execute();
$results =$sql->fetchall(PDO::FETCH_ASSOC);
if(count($results) > 0){
echo '<option value="0">Select state</option>';
foreach($results as $row){
echo '<option value="'.$row['stateID'].'">'.$row['stateName'].'</option>';
}
}else{
echo '<option value="">state not available</option>';
}
}
if(isset($_POST["state"]) && !empty($_POST["state"])){
$sql=$dbh->prepare("SELECT DISTINCT cities.cityID,cities.cityName,cities.stateID from cities INNER JOIN states ON cities.stateID= ? ");
$sql->bindValue(1,$_POST['state']);
$sql->execute();
$results =$sql->fetchall(PDO::FETCH_ASSOC);
if(count($results) > 0){
echo '<option value="0">Select City</option>';
foreach($results as $row){
echo '<option value="'.$row['cityID'].'">'.$row['cityName'].'</option>';
}
}else{
echo '<option value="">city not available</option>';
}
}
?>
db_config.php
<?php
$servername = "localhost";
$username = "hidden";
$password = "hidden";
$dbname = "mytestDB";
try {
$dbh= new PDO("mysql:host=$servername;dbname=$dbname",$username,$password);
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Could not connect".$e->getMessage();
error_log($e);
}
?>
my tables
CREATE TABLE IF NOT EXISTS `states` (
`stateID` int(6) NOT NULL AUTO_INCREMENT,
`stateName` varchar(255) NOT NULL,
`countryID` int(6) NOT NULL,
PRIMARY KEY (`stateID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `states`
--
INSERT INTO `states` (`stateID`, `stateName`, `countryID`) VALUES
(3, 'Western Cape', 2),
(4, 'Eastern Cape', 1),
(5, 'North West', 2),
(6, 'Northen Cape', 2);
--
-- Table structure for table `cities`
--
CREATE TABLE IF NOT EXISTS `cities` (
`cityID` int(6) NOT NULL AUTO_INCREMENT,
`cityName` varchar(255) NOT NULL,
`stateID` int(6) NOT NULL,
PRIMARY KEY (`cityID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `cities`
--
INSERT INTO `cities` (`cityID`, `cityName`, `stateID`) VALUES
(1, 'Cape Town', 3),
(2, 'East London', 4);
-- --------------------------------------------------------
--
-- Table structure for table `countries`
--
CREATE TABLE IF NOT EXISTS `countries` (
`countryID` int(6) NOT NULL AUTO_INCREMENT,
`countryName` varchar(255) NOT NULL,
PRIMARY KEY (`countryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
--
-- Dumping data for table `countries`
--
INSERT INTO `countries` (`countryID`, `countryName`) VALUES
(1, 'South Africa'),
(2, 'Zambia'),
(3, 'Zimbabwe '),
(4, 'Uganda'),
(5, 'USA'),
(6, 'Brazil'),
(7, 'India'),
(8, 'Austrilia'),
(9, 'Ghana');
-- --------------------------------------------------------
NB: use your own characterset on the tables, you may use utf-8
Hope this will point you to the correct direction, hoping also other SO users will help where I missed something.
这篇关于链接下拉列表与HTML + PHP + MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
