PDO正在将ID而不是名称插入数据库

Question

所以我有点困惑，我做了两个下拉菜单，它们都在工作。问题是，当我将得到的数据插入到表中时，下拉元素的id而不是它们的名称。我很确定我需要用另一种方式来做，但我是一个业余爱好者，所以任何解释都将是错误的。这是我的代码：

<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
include('includes/config.php');
if(strlen($_SESSION['alogin'])==0)
{   
header('location:index.php');
}
else{
if(isset($_POST['submit']))
{
$id=null;
$name=$_POST['name'];
$email=$_POST['email'];
$user=$_POST['user'];
$password=md5($_POST['password']);
$mobileno=$_POST['mobileno'];
$bloc=$_POST['industry_name'];
$apartament=$_POST['sub_industry_name'];
$intretinere=$_POST['intretinere'];
$m2=$_POST['m2'];
$notitype='Create Account';
$reciver='Admin';
$sender=$email;
$sqlnoti="insert into notification (notiuser,notireciver,notitype) values (:notiuser,:notireciver,:notitype)";
$querynoti = $dbh->prepare($sqlnoti);
$querynoti-> bindParam(':notiuser', $sender, PDO::PARAM_STR);
$querynoti-> bindParam(':notireciver',$reciver, PDO::PARAM_STR);
$querynoti-> bindParam(':notitype', $notitype, PDO::PARAM_STR);
$querynoti->execute();    
$sql ="INSERT INTO users(id, name, email, user, password, mobile, bloc, apartament, intretinere, m2, status) VALUES(:id, :name, :email, :user, :password, :mobileno, :industry_name, :sub_industry_name, :intretinere, :m2, 1)";
$query= $dbh -> prepare($sql);
$query-> bindParam(':id', $id, PDO::PARAM_STR);
$query-> bindParam(':name', $name, PDO::PARAM_STR);
$query-> bindParam(':email', $email, PDO::PARAM_STR);
$query-> bindParam(':user', $user, PDO::PARAM_STR);
$query-> bindParam(':password', $password, PDO::PARAM_STR);
$query-> bindParam(':mobileno', $mobileno, PDO::PARAM_STR);
$query-> bindParam(':industry_name', $bloc, PDO::PARAM_STR);
$query-> bindParam(':sub_industry_name', $apartament, PDO::PARAM_STR);
$query-> bindParam(':intretinere', $intretinere, PDO::PARAM_STR);
$query-> bindParam(':m2', $m2, PDO::PARAM_STR);
$query->execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
echo "<script type='text/javascript'>alert('Registration Sucessfull!');</script>";
echo "<script type='text/javascript'> document.location = 'adduser.php'; </script>";
}
else 
{
$error="Something went wrong. Please try again";
}
}
?>
<script>
$(document).ready(function(){
$('#category_item').selectpicker();
$('#sub_category_item').selectpicker();
load_data('category_data');
function load_data(type, category_id = '')
{
$.ajax({
url:"../admin/load_data.php",
method:"POST",
data:{type:type, category_id:category_id},
dataType:"json",
success:function(data)
{
var html = '';
for(var count = 0; count < data.length; count++)
{
html += '<option value="'+data[count].id+'">'+data[count].name+'</option>';
}
if(type == 'category_data')
{
$('#category_item').html(html);
$('#category_item').selectpicker('refresh');
}
else
{
$('#sub_category_item').html(html);
$('#sub_category_item').selectpicker('refresh');
}
}
});
}
$(document).on('change', '#category_item', function(){
var category_id = $('#category_item').val();
load_data('sub_category_data', category_id);
});
});
</script>
<div class="form-group">
<label class="col-sm-2 control-label">Bloc<span style="color:red">*</span></label>
<div class="col-sm-4">
<select name="industry_name" id="category_item" class="form-control" required title="Selecteaza Bloc">
<option value="">Selecteaza Bloc</option>
</select>
</div>
<label class="col-sm-2 control-label">Apartament<span style="color:red">*</span></label>
<div class="col-sm-4">
<select name="sub_industry_name" id="sub_category_item" class="form-control" required data-live-search="true" title="Selecteaza Apartament">
<option value="">Selecteaza Apartament</option>
</select>
</div>
</div>

同时加载数据.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
include('includes/config.php');
if(isset($_POST["type"]))
{
if($_POST["type"] == "category_data")
{
$query = "
SELECT * FROM tbl_industry 
ORDER BY industry_name ASC
";
$statement = $dbh->prepare($query);
$statement->execute();
$data = $statement->fetchAll();
foreach($data as $row)
{
$output[] = array(
'id'  => $row["industry_id"],
'name'  => $row["industry_name"]
);
}
echo json_encode($output);
}
else
{
$query = "
SELECT * FROM tbl_sub_industry 
WHERE industry_id = '".$_POST["category_id"]."' 
ORDER BY sub_industry_name ASC
";
$statement = $dbh->prepare($query);
$statement->execute();
$data = $statement->fetchAll();
foreach($data as $row)
{
$output[] = array(
'id'  => $row["sub_industry_id"],
'name'  => $row["sub_industry_name"]
);
}
echo json_encode($output);
}
}
?>

编辑：我已经按照下面的答案更新了代码，但现在我在下拉列表中没有得到任何东西。

这是js:

<script>
$(document).ready(function(){
$('#category_item').selectpicker();
$('#sub_category_item').selectpicker();
load_data('category_data');
function load_data(type, category_id = '')
{
$.ajax({
url:"../admin/load_data.php",
method:"POST",
data:{type:type, category_id:category_id},
dataType:"json",
success:function(data)
{
var html = '';
for(var count = 0; count < data.length; count++)
{
$.each( data, function( key, value ) {
html += '<option value="'+value+'">'+value+'</option>';
});
}
if(type == 'category_data')
{
$('#category_item').html(html);
$('#category_item').selectpicker('refresh');
}
else
{
$('#sub_category_item').html(html);
$('#sub_category_item').selectpicker('refresh');
}
}
});
}
$(document).on('change', '#category_item', function(){
var category_id = $('#category_item').val();
load_data('sub_category_data', category_id);
});
});
</script> 

和load_data.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
include('includes/config.php');
if(isset($_POST["type"]))
{
if($_POST["type"] == "category_data")
{
$query = "
SELECT * FROM tbl_industry 
ORDER BY industry_name ASC
";
$statement = $dbh->prepare($query);
$statement->execute();
$data = $statement->fetchAll();
foreach($data as $row){
$output[$row["industry_id"]] = $row["industry_name"];
}
echo json_encode($output);
}
else
{
$query = "
SELECT * FROM tbl_sub_industry 
WHERE industry_id = '".$_POST["category_id"]."' 
ORDER BY sub_industry_name ASC
";
$statement = $dbh->prepare($query);
$statement->execute();
$data = $statement->fetchAll();
foreach($data as $row){
$output[$row["sub_industry_id"]] = $row["sub_industry_name"];
}
echo json_encode($output);
}
}
?> 

Answer 1

您说要在下拉列表中插入项目的ID，因为这是您在load_data()中设置的。

代替

html += '<option value="'+data[count].id+'">'+data[count].name+'</option>';

尝试

html += '<option value="'+data[count].name+'">'+data[count].name+'</option>';

除非在AJAX调用之前处理数据，否则$_POST将从option标记中获取value，而不是在打开和关闭标记之间显示的文本。

在创建和读取数组时，您添加了不必要的步骤。在load_data中，您可以使用基于结果的密钥创建数组：

foreach($data as $row){
$output[$row["industry_id"]] = $row["industry_name"];
}

然后使用jQuery的$.each函数读取AJAX调用后的数据：

$.each( data, function( key, value ) {
html += '<option value="'+value+'">'+value+'</option>';
});

请参阅https://api.jquery.com/jquery.each/