php 将多个复选框值插入到 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12122156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting multiple checkbox values to MySQL
提问by Isuru
I know there are multiple questions here on SO regarding this same issue already and I've looked into them but didn't quite get a satisfying answer. So here goes my question,
我知道这里已经有多个关于同一问题的问题，我已经研究过这些问题，但并没有得到令人满意的答案。所以这是我的问题，
I have a form which consists of a few textboxes and checkboxes. It looks like this,
我有一个由几个文本框和复选框组成的表单。看起来像这样，
The user can select multiple checkboxes. I'm trying to insert the values(not the displaying text string) of those checkboxes into a MySQL table. It should look like this,
用户可以选择多个复选框。我正在尝试将这些复选框的值（而不是显示的文本字符串）插入 MySQL 表中。应该是这样的
One Service ID(SID) can have multiple Locations(Loc_Code). Those location codes (CO, GQ) are the values of the checkboxes.
一个服务 ID（SID）可以有多个位置（Loc_Code）。这些位置代码（CO、GQ）是复选框的值。
I've written this following code so far.
到目前为止，我已经编写了以下代码。
<html>
<head>
</head>
<body>
<?php
require_once("db_handler.php");
$conn = iniCon();
$db = selectDB($conn);
/* Generating the new ServiceID */
$query = "SELECT SID FROM taxi_services ORDER BY SID DESC LIMIT 1";
$result = mysql_query($query, $conn);
$row = mysql_fetch_array($result);
$last_id = $row["SID"];
$id_letter = substr($last_id, 0, 1);
$id_num = substr($last_id, 1) + 1;
$id_num = str_pad($id_num, 3, "0", STR_PAD_LEFT);
$new_id = $id_letter . $id_num;
//Selecting locations
$query = "SELECT Loc_Code, Name FROM districts";
$result = mysql_query($query, $conn);
$count = mysql_num_rows($result);
?>
<?php
if(isset($_POST["savebtn"]))
{
//inserting the new service information
$id = $_POST["sid"];
$name = $_POST["name"];
$cost = $_POST["cost"];
if($_POST["active"] == "on") $active = 1; else $active = 0;
$query = "INSERT INTO taxi_services(SID, Name, Cost, Active) VALUES('$id', '$name', '$cost', '$active')";
$result = mysql_query($query, $conn);
//inserting the location details
for($j = 0; $j < $count; $j++)
{
$loc_id = $_POST["checkbox2"][$j];
$query = "INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id')";
$result5 = mysql_query($query, $conn);
}
if (!$result || !$result5)
{
die("Error " . mysql_error());
}
else
{
?>
<script type="text/javascript">
alert("Record added successfully!");
</script>
<?php
}
mysql_close($conn);
}
?>
<div id="serv">
<b>Enter a new taxi service</b>
<br/><br/>
<form name="servForm" action="<?php $PHP_SELF; ?>" method="post" >
<table width="300" border="0">
<tr>
<td>Service ID</td>
<td><input type="text" name="sid" readonly="readonly" value="<?php echo $new_id; ?>" style="text-align:right" /></td>
</tr>
<tr>
<td>Name</td>
<td><input type="text" name="name" style="text-align:right" /></td>
</tr>
<tr>
<td>Cost</td>
<td><input type="text" name="cost" style="text-align:right" onkeypress="return isNumberKey(event)" /></td>
</tr>
<tr>
<td>Active</td>
<td><input type="checkbox" name="active" /></td>
</tr>
</table>
</div>
<div id="choseLoc">
Locations <br/><br/>
<table border="0">
<?php
$a = 0;
while($row = mysql_fetch_array($result))
{
if($a++ %5 == 0) echo "<tr>";
?>
<td align="center"><input type="checkbox" name="checkbox2[]" value="<?php echo $row['Loc_Code']; ?>" /></td>
<td style="text-align:left"><?php echo $row["Name"]; ?></td>
<?php
if($a %5 == 0) echo "</tr>";
}
?>
</table>
</div>
<br/>
<div id="buttons">
<input type="reset" value="Clear" /> <input type="submit" value="Save" name="savebtn" />
</form>
</div>
</body>
</html>
It inserts the Service details correctly. But when it inserts location data, a problem like this occurs,
它正确插入了服务详细信息。但是当它插入位置数据时，就会出现这样的问题，
I selected 4 checkboxes and saved. The 4 location codes gets saved along with the service ID. But as you can see from the screenshot above, a bunch of empty rows gets inserted too.
我选择了 4 个复选框并保存。4 个位置代码与服务 ID 一起保存。但是正如您从上面的屏幕截图中看到的那样，也插入了一堆空行。
My question is how can I stop this from happening? How can I insert the data from the checkboxes only I select?
我的问题是如何阻止这种情况发生？如何仅从我选择的复选框中插入数据？
Thank you.
谢谢你。
回答by eggyal
One way would be to only loop over the checkboxes that were submitted:
一种方法是只循环提交的复选框：
//inserting the location details
foreach($_POST["checkbox2"] as $loc_id)
{
$query = "INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id')";
$result5 = mysql_query($query, $conn);
}
I reiterate here the SQL injection warning given above: you would be muchbetter off preparing an INSERTstatement and then executing it with parameters. Using PDO, it would look something like:
我在这里重申上面给出的SQL注入警告：你会多过一个准备好INSERT语句，然后带参数执行它。使用 PDO，它看起来像：
//inserting the location details
$stmt = $dbh->prepare('
INSERT INTO service_locations(SID, Loc_Code) VALUES(:id, :loc)
');
$stmt->bindValue(':id', $id);
$stmt->bindParam(':loc', $loc_id);
foreach($_POST["checkbox2"] as $loc_id) $stmt->execute();
回答by leoyfm
from these sentence:
从这些句子：
for($j = 0; $j < $count; $j++)
{
$loc_id = $_POST["checkbox2"][$j];
$query = "INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id')";
$result5 = mysql_query($query, $conn);
}
i find the problem is that the value of loc_code must be the last loction you selected. because in this loop, the value of loc_code will replaced everytime. if you want to insert all the location, you should put it on the one sentence, like INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id'), the value of $loc_id should be CO,GQ,GL.
我发现问题是 loc_code 的值必须是您选择的最后一个位置。因为在这个循环中，loc_code 的值每次都会被替换。如果你想插入所有的位置，你应该把它放在一个句子里，比如INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id')， $loc_id 的值应该是CO,GQ , GL。
回答by Tieran
This is happening because the checkboxes that weren't ticked still get posted, they just have empty values. Before you do your insert to service_locationsjust check if $loc_idis empty or not, only do the insert if it isn't.
发生这种情况是因为没有勾选的复选框仍然被张贴，它们只有空值。在您插入之前service_locations只检查是否$loc_id为空，只有在不是时才插入。
