PHP - 将复选框表单中的数据插入到 MYSQL 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11125704/
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
PHP - To Insert data from checkbox forms into MYSQL
提问by Adam Ross Bowers
This is my first website that makes use of server-side technology.
And it's also my first delving into PHPworking along side MYSQL.
I've been working and learning as I go.
这是我第一个使用服务器端技术的网站。这也是我第一次深入研究PHP并肩工作MYSQL。我一直在工作和学习。
I've created a basic management page for a database of product entries for a small Prom Dress business.
我为小型舞会礼服企业的产品条目数据库创建了一个基本管理页面。
I've been working on the functionality of a form that the administrator can use to upload new entries to the database.
我一直在研究管理员可以用来将新条目上传到数据库的表单的功能。
Some of these entries include:
其中一些条目包括：
Dress Name [text input]
Colour Availability [check boxes]
衣服名称[文字输入]
颜色可用性 [复选框]
I've run into trouble getting data from multiple check boxes from a HTML form into the database.
我在从 HTML 表单的多个复选框中获取数据到数据库时遇到了麻烦。
The choices in the check boxes should represent a boolean value (yes/no - 1/0)
复选框中的选项应代表一个布尔值（是/否 - 1/0）
Here's the form structure:
这是表单结构：
<form action="inventory_list.php" enctype="multipart/form-data" name="myForm" id="myform" method="post">
<table width="90%" border="0" cellspacing="0" cellpadding="6">
<tr>
<td width="20%" align="right">Dress Name</td>
<td width="80%"><label>
<input name="Dress_name" type="text" id="Dress_name" size="64" />
</label>
</td>
</tr>
<tr>
<td align="right">Collections</td>
<td>
<label>
<select name="collections" id="collections">
<option value="other">Other</option>
<option value="hermione">Hermione</option>
<option value="jora">Jora</option>
<option value="manon">Manon</option>
</select>
</label></td>
</tr>
<tr>
<td align="right">Available Sizes</td>
<td>
<input type="checkbox" name="sizes[]" value="1" /> Sizes 2-6<br />
<input type="checkbox" name="sizes[]" value="1" /> Sizes 6-10 <br />
<input type="checkbox" name="sizes[]" value="1" /> Sizes 10-14<br />
<input type="checkbox" name="sizes[]" value="1" /> Sizes 14-18 <br />
<input type="checkbox" name="sizes[]" value="1" /> Sizes 18-22<br />
<input type="checkbox" name="sizes[]" value="1" /> Sizes 22-26 <br />
</td>
</tr>
<tr>
<td align="right">Dress Colours</td>
<td>
<input type="checkbox" name="colours[]" value="1" /> Pinks/Reds <br />
<input type="checkbox" name="colours[]" value="1" /> Blues/Greens <br />
<input type="checkbox" name="colours[]" value="1" /> Violet/Purple<br />
<input type="checkbox" name="colours[]" value="1" /> Yellow/Orange/Brown/Gold <br />
<input type="checkbox" name="colours[]" value="1" /> Black/White<br />
</td>
</tr>
<tr>
<td align="right">Product Image</td>
<td><label>
<input type="file" name="fileField" id="fileField" />
</label></td>
</tr>
<tr>
<td> </td>
<td><label>
<input type="submit" name="button" id="button" value="Add This Item Now" />
</label></td>
</tr>
</table>
</form>
And here is the PHP that I've built to deal with the form submitting data to the Database.
这是我构建的用于处理向数据库提交数据的表单的 PHP。
<?php
if (isset($_POST['Dress_name'])) {
$dress_name = mysql_real_escape_string($_POST['Dress_name']);
$collections = mysql_real_escape_string($_POST['collections']);
$sizesArray = $_POST['sizes'];
$coloursArray = $_POST['colours'];
foreach ($sizesArray as $key => $value)
{
echo "Key = $key || Value = $value<br />";
}
foreach ($coloursArray as $ckey => $cvalue)
{
echo "Key = $ckey || Value = $cvalue<br />";
}
//See if that product name is an identical match to another product in the system
$sql = mysql_query("SELECT ID FROM names WHERE Dress='$dress_name' LIMIT 1");
$productMatch = mysql_num_rows($sql); // count the output amount
if ($productMatch > 0) {
echo 'Sorry you tried to place a duplicate "Product Name" into the system, <a href="inventory_list.php">click here</a>';
exit();
}
//Add this product into the database now
$sql = mysql_query("INSERT INTO names (Dress)
VALUES('$dress_name')") or die (mysql_error());
$pid = mysql_insert_id();
//Place image in the folder
$newname = "$pid.jpg";
move_uploaded_file( $_FILES['fileField']['tmp_name'], "../inventory_images/$newname");
header("location: inventory_list.php");
exit();
}
?>
As you can see, I've got as far as putting the data into an array. And now I've hit a bit of a brick wall, I can't seem to find an answer that makes sense anywhere.
如您所见，我已经将数据放入数组中。现在我遇到了一点砖墙，我似乎无法在任何地方找到有意义的答案。
How do I get the boolean values into correct columns in the database table?
如何将布尔值放入数据库表中的正确列中？
Appreciate your time!
珍惜你的时间！
回答by Nathaniel Ford
The first thing to know is that HTML checkboxes will return a result if checked, and will return nothing if unchecked. Therefore, it is important in your html to have your values increment:
首先要知道的是，如果选中 HTML 复选框将返回结果，如果未选中则不返回任何内容。因此，在您的 html 中让您的值增加很重要：
<td align="right">Available Sizes</td>
<td>
<input type="checkbox" name="sizes[]" value="1" /> Sizes 2-6<br />
<input type="checkbox" name="sizes[]" value="2" /> Sizes 6-10 <br />
<input type="checkbox" name="sizes[]" value="3" /> Sizes 10-14<br />
<input type="checkbox" name="sizes[]" value="4" /> Sizes 14-18 <br />
<input type="checkbox" name="sizes[]" value="5" /> Sizes 18-22<br />
<input type="checkbox" name="sizes[]" value="6" /> Sizes 22-26 <br />
</td>
A fully normalized database schema would look something like this:
一个完全规范化的数据库架构看起来像这样：
//Note that 'names' and 'collections are overloaded
//words and should be avoided
table BRANDS
id INT AUTOINCREMENT
collectionname VARCHAR
table SIZES
id INT AUTOINCREMENT
sizecategory VARCHAR //ie 'Sizes 18-22'
table COLOURS
id INT AUTOINCREMENT
colourname VARCHAR
table INVENTORY
id INT AUTOINCREMENT
brandid INT FOREIGN KEY (BRAND)
imageurl VARCHAR
table INVENTORY_SIZES
inventoryid INT FOREIGN KEY
sizeid FOREIGN KEY
table INVENTORY_COLOURS
inventoryid INT FOREIGN KEY
colourid FOREIGN KEY
Your BRAND, COLOURSand SIZEStables would need to be properly populated with your available options, and you could in theory dynamically load your page with data from those tables. The ids of each of the rows in those tables should end up as the values in your checkbox arrays.
您的BRAND,COLOURS和SIZES表格需要使用您的可用选项正确填充，并且理论上您可以使用这些表格中的数据动态加载您的页面。这些表中每一行的 id 最终应作为复选框数组中的值。
//get your inputs
$query_values = array();
$query_values[':brandid'] = $dress_name = $_POST['Dress_name'];//note that you should change the value in your options here to the unique ids in your database.
$query_values[':sizeid'] = getSize($_POST[]);
$query_values[':colourid'] = getColour($_POST[]);
$query_values[':quantity'] = $_POST['quantity'];
$query_values[':imageurl'] = $_POST['imageurl'];
//Prepare and execute your sql
//Note that PDO will handle escaping problematic inputs.
$sql = "INSERT INTO INVENTORY ('brandid', 'imageurl') VALUES (:brandid, :sizeid, :colourid, :quantity, :imageurl)";
executeInsert($sql, $query_values);
$inventoryid = mysql_insert_id();
saveSizes($_POST['sizes'], $inventoryid);
saveColours($_POST['colours'], $inventoryid);
function saveSizes($size_array, $inventoryid) {
$sql = "INSERT INTO INVENTORY_SIZES ('inventoryid', 'sizeid') VALUES (:inventoryid, :sizeid)";
foreach ($size_array as $sizeid) {
$query_values = array();
$query_values[':inventoryid'] = $inventoryid;
$query_values[':sizeid'] = $sizeid;
executeInsert($sql, $query_values);
}
}
function saveColours($colour_array) {
$sql = "INSERT INTO INVENTORY_COLOURS ('inventoryid', 'colourid') VALUES (:inventoryid, :colourid)";
foreach ($size_array as $sizeid) {
$query_values = array();
$query_values[':inventoryid'] = $inventoryid;
$query_values[':colourid'] = $colourid;
executeInsert($sql, $query_values);
}
}
function executeInsert($sql, $query_values) {
$query = $conn->prepare($sql);
$query->execute($query_values);
}
Be sure to use PHP PDOto guard against security issues. I'm fairly sure some of that could be abstracted out/cleaned up better, but this should do the job. (Though I'm also fairly sure there are little bugs as I'm doing this cold and without testing.)
请务必使用PHP PDO来防范安全问题。我相当确定其中一些可以更好地抽象/清理，但这应该可以完成工作。（虽然我也相当确定有一些小错误，因为我在做这种冷的并且没有测试的情况下。）
回答by fineTuneFork
You need to place the insert statement in an 'else' statement after the 'if' which checks if the name already exists in the database.
您需要将插入语句放在“if”之后的“else”语句中，以检查名称是否已存在于数据库中。
I am not sure what exactly you are trying to do , since there is no statement that you are executing to put the 'checkbox' values in the database. You are echo-ing the sizesarray to as an output for the administrator to see after he submits this form ,but you are not putting it in the database at all.
我不确定您到底要做什么，因为没有任何语句表明您正在执行将“复选框”值放入数据库中。您正在回显sizes数组作为管理员提交此表单后查看的输出，但您根本没有将其放入数据库中。
What will help is if you can share the database structure ( the column names) . If you don't have any columns for sizes 2-5 & other categories , add those & then change your insert statement to the following :
如果您可以共享数据库结构（列名），将会有什么帮助。如果您没有大小为 2-5 和其他类别的任何列，请添加这些列，然后将插入语句更改为以下内容：
INSERT INTO names (Dress,Size2to5,Size5to10,Size10to15,Size15to20) VALUES('$dress_name','$size[0]','$size[1]','size[2]','size[3]')
INSERT INTO names (Dress,Size2to5,Size5to10,Size10to15,Size15to20) VALUES('$dress_name','$size[0]','$size[1]','size[2]','size[3]')
Hope this helps. Do let us know if this works for you.
希望这可以帮助。请让我们知道这是否适合您。
