使用复选框值更新SQL数据库 [英] Updating SQL Database With Checkbox Values
问题描述
好吧，我知道这个问题已经被回答了几次，但是我似乎无法让我的工作正常！
Ok I know this has been answered a few times, but I just can't seem to get mine working!
我在数据库中有8周的时间(设置为列)，并使用php显示这些值，如下所示:
I have 8 weeks in my database (set up as columns) and I display the value of these with php as follows
<form method="post" id="updating" action="<?php $_PHP_SELF ?>">
<?php
$count = 1;
foreach ($results as $v) {
while($count<9){
$week = Week.$count;
$checkvalue = $v -> $week;
?>
Week<?echo $count;?> <input type="checkbox" id="Week<?php echo$count;?>" name="week[]" value="<?php echo $checkvalue;?>" <?php if($checkvalue==1){?>
checked="checked"
<?php } ?>
/>
<?php
$count++;
}
}
?>
<input name="update" type="submit" id="update" value="Update">
</form>
我知道它可能不尽如人意，但它确实为我输出了正确的HTML并显示了数据库中显示的信息.
I know it's probably not as graceful as it should be, but it does output the correct HTML for me and displays the information as it's presented in the database.
<form method="post" id="updating" action="">
Week1 <input type="checkbox" id="Week1" name="week[]" value="1" checked="checked"
/>
Week2 <input type="checkbox" id="Week2" name="week[]" value="0" />
Week3 <input type="checkbox" id="Week3" name="week[]" value="1" checked="checked"
/>
Week4 <input type="checkbox" id="Week4" name="week[]" value="1" checked="checked"
/>
Week5 <input type="checkbox" id="Week5" name="week[]" value="0" />
Week6 <input type="checkbox" id="Week6" name="week[]" value="1" checked="checked"
/>
Week7 <input type="checkbox" id="Week7" name="week[]" value="1" checked="checked"
/>
Week8 <input type="checkbox" id="Week8" name="week[]" value="1" checked="checked"
/>
<input name="update" type="submit" id="update" value="Update">
</form>
我遇到的问题是，当我勾选或取消选中这些复选框时，似乎无法捕获更新的值，因此数据库无法正确更新.这是处理自我发布请求的地方.
The problem I'm having is when I tick or untick those checkboxes, the updated values don't appear to be captured, and hence the database isn't being updated properly. Here's where the self post request is handled.
<?php if(isset($_POST['update']))
{
$count = 1;
if(isset($_POST['week']) && !empty($_POST['week']))
foreach($_POST['week'] as $w){ echo $w; //prints 1 all the time
$week = Week.$count;
echo $week;//prints out weeks I want to update correctly (database headings)
$query = $db->getQuery(true);
// Fields to update.
$fields = array(
$week.'=' . $db->quote( $w ));
// Conditions for which records should be updated.
$conditions = array(
'UserID='. (int) $user_id);
$query->update($db->quoteName('tools_engage'))->set($fields)->where($conditions);
$db->setQuery($query);
try {
$result = $db->query(); // Use $db->execute() for Joomla 3.0.
echo ("Values saved and updated </br>");
$count++;
} catch (Exception $e) {
// Catch the error.
}
}
}?>
我正在Joomla中执行此操作，因此使用了怪异的数据库更新语法，但是它确实有效，并且我在程序中的其他地方使用了它们，所以我知道它们不是问题
I'm doing this in Joomla, hence the weird database update syntax, but it does work and I've used them elsewhere in this program so I know they're not the problem
正如我在代码中评论的那样，无论是否选中任何框，数组week []似乎都包含值1.然后导致数据库中的每个值都更新为1/true，这显然是不正确的.
As I commented in the code, the array week[] just seems to contain the value 1 for everything, no matter what boxes I check or uncheck. Which then results in every value in the database being updated to 1/true which obviously isn't right.
我怀疑问题出在更新表单的HTML输出中，但似乎无法破解，非常感谢您的帮助！
I have a suspicion the problem lies in my HTML output for the update form, but can't seem to crack it, any help is much appreciated!
更新: 好吧，继续nvuono的建议，我已经更新了我的表格，就像这样
Update: Ok so going on nvuono's suggestion I have updated my form to be like this
<?php
$count = 1;
foreach ($results as $v) {
while($count<9){
$week = Week.$count;
$checkvalue = $v -> $week;
?>
Week<?echo $count;?> <input type="checkbox" id="Week<?php echo$count;?>" name="week[<?php echo $count;?>]" value="1" <?php if($checkvalue==1){?>
checked="checked"
<?php } ?>
/>
<?php
$count++;
}
}
?>
所以现在每个名称在数组中都有一个索引，不是吗?如果是这样，我的
So now each name has an index in the array does it not? If that is the case, should my
if(isset($_POST['update']))
部分现在不按输入顺序打印出数组中的所有值吗?
section not now be printing out all the values in the array in the order they've been entered?
即.我的逻辑是，它会打印出1,0,0,1,0,0,0(选中了框1和4)或选中了什么框.但是相反，它只是打印出1,1，所以我无法知道它们是什么索引，以及随后我应该在哪个星期更新
ie. My logic would be that it prints out 1,0,0,1,0,0,0 (box 1 and 4 checked) or what ever boxes are checked. But instead it just prints out 1,1 so I've no way of knowing what index they are, and subsequently what week I should be updating
推荐答案
我添加了一些非常hacky的更新方式.使用post时，需要提防SQL注入和其他不好的事情.确保始终验证和清理数据.之所以采用"hacky"方式，是因为我们正在处理整数值，并且很容易清除它们.
I added some really hacky way of doing the update. You need to beware of SQL Injection and other bad things when using post... make sure you always validate and sanitize your data. The "hacky" way works because we're working with integer values and those are easily cleaned.
屏幕截图:
这是我的桌子...我这样称呼它是为了stackoverflow ...
This is my table... I called it so for stackoverflow...
+----+-------+-------+-------+-------+-------+-------+-------+-------+
| id | week1 | week2 | week3 | week4 | week5 | week6 | week7 | week8 |
+----+-------+-------+-------+-------+-------+-------+-------+-------+
| 1 | | | | | | | | |
| 2 | 1 | | | | | | | |
| 3 | 1 | | 1 | | | | | |
| 4 | 1 | | 1 | | 1 | | | |
| 5 | 1 | | 1 | | 1 | | 1 | |
+----+-------+-------+-------+-------+-------+-------+-------+-------+
这是我的PHP文件....(将其粘贴到一个空的PHP文档中运行，除了更改数据库连接和查询外，它还包含在内)
This is my PHP file.... (stick it in an empty PHP document to run, it's self contained besides changing the db connection and query)
<?php
$mysqli = new mysqli("localhost", "root", "password", "test");
if (!empty($_POST)) {
print "<pre>POST VARS: \n".print_r($_POST,true)."</pre>";
foreach($_POST as $i => $data)
{
if (substr($i,0,3)=='row' && is_numeric(substr($i,3)))
{
$row_id = substr($i,3);
$data = array_flip($data);
$values = array();
for ($x=1; $x<9; $x++) {
$values[] = "week$x = ". ((isset($data[$x])) ? '1' : '0');
}
$stmt = "\nupdate so ".
"\n set ".implode(", \n ",$values).
"\n where id = $row_id; \n";
$update = $mysqli->query($stmt);
if ($update) { print "Row $row_id updated successfully.<br/>"; }
}
print "<br/>";
}
}
$result = $mysqli->query("select * from so");
$mysqli->close();
?>
<form method="post" id="updating" action="<?php $_PHP_SELF ?>">
<?php
while($row = $result->fetch_object())
{
$count = 1;
print "<div style='border:1px solid black; display:inline-block;'>\n";
print "Row ".$row->id."<br/>\n";
while($count < 9)
{
$week = "week$count";
$checkvalue = $row->{$week};
?>
Week<?php echo $count ?> <input type="checkbox" id="Week<?php echo$count;?>" name="row<?php echo $row->id
?>[]" value="<?php echo $count;?>" <?php if($checkvalue==1){
?> checked="checked" <?php } ?> />
<?php
$count++;
}
print "</div><br/><br/>\n\n\n";
}
?>
<input name="update" type="submit" id="update" value="Update">
</form>
这是提交后的$_POST:
POST VARS:
Array
(
[row2] => Array
(
[0] => 1
)
[row3] => Array
(
[0] => 1
[1] => 3
)
[row4] => Array
(
[0] => 1
[1] => 3
[2] => 5
)
[row5] => Array
(
[0] => 1
[1] => 3
[2] => 5
[3] => 7
)
[update] => Update
)
我的查看源代码"的形式:
My "view source" of the form:
<form method="post" id="updating" action="">
<div style='border:1px solid black; display:inline-block;'>
Row 1<br/>
Week1 <input type="checkbox" id="Week1" name="row1[]" value="1" />
Week2 <input type="checkbox" id="Week2" name="row1[]" value="2" />
Week3 <input type="checkbox" id="Week3" name="row1[]" value="3" />
Week4 <input type="checkbox" id="Week4" name="row1[]" value="4" />
Week5 <input type="checkbox" id="Week5" name="row1[]" value="5" />
Week6 <input type="checkbox" id="Week6" name="row1[]" value="6" />
Week7 <input type="checkbox" id="Week7" name="row1[]" value="7" />
Week8 <input type="checkbox" id="Week8" name="row1[]" value="8" />
</div><br/><br/>
<div style='border:1px solid black; display:inline-block;'>
Row 2<br/>
Week1 <input type="checkbox" id="Week1" name="row2[]" value="1" checked="checked" />
Week2 <input type="checkbox" id="Week2" name="row2[]" value="2" />
Week3 <input type="checkbox" id="Week3" name="row2[]" value="3" />
Week4 <input type="checkbox" id="Week4" name="row2[]" value="4" />
Week5 <input type="checkbox" id="Week5" name="row2[]" value="5" />
Week6 <input type="checkbox" id="Week6" name="row2[]" value="6" />
Week7 <input type="checkbox" id="Week7" name="row2[]" value="7" />
Week8 <input type="checkbox" id="Week8" name="row2[]" value="8" />
</div><br/><br/>
<div style='border:1px solid black; display:inline-block;'>
Row 3<br/>
Week1 <input type="checkbox" id="Week1" name="row3[]" value="1" checked="checked" />
Week2 <input type="checkbox" id="Week2" name="row3[]" value="2" />
Week3 <input type="checkbox" id="Week3" name="row3[]" value="3" checked="checked" />
Week4 <input type="checkbox" id="Week4" name="row3[]" value="4" />
Week5 <input type="checkbox" id="Week5" name="row3[]" value="5" />
Week6 <input type="checkbox" id="Week6" name="row3[]" value="6" />
Week7 <input type="checkbox" id="Week7" name="row3[]" value="7" />
Week8 <input type="checkbox" id="Week8" name="row3[]" value="8" />
</div><br/><br/>
<div style='border:1px solid black; display:inline-block;'>
Row 4<br/>
Week1 <input type="checkbox" id="Week1" name="row4[]" value="1" checked="checked" />
Week2 <input type="checkbox" id="Week2" name="row4[]" value="2" />
Week3 <input type="checkbox" id="Week3" name="row4[]" value="3" checked="checked" />
Week4 <input type="checkbox" id="Week4" name="row4[]" value="4" />
Week5 <input type="checkbox" id="Week5" name="row4[]" value="5" checked="checked" />
Week6 <input type="checkbox" id="Week6" name="row4[]" value="6" />
Week7 <input type="checkbox" id="Week7" name="row4[]" value="7" />
Week8 <input type="checkbox" id="Week8" name="row4[]" value="8" />
</div><br/><br/>
<div style='border:1px solid black; display:inline-block;'>
Row 5<br/>
Week1 <input type="checkbox" id="Week1" name="row5[]" value="1" checked="checked" />
Week2 <input type="checkbox" id="Week2" name="row5[]" value="2" />
Week3 <input type="checkbox" id="Week3" name="row5[]" value="3" checked="checked" />
Week4 <input type="checkbox" id="Week4" name="row5[]" value="4" />
Week5 <input type="checkbox" id="Week5" name="row5[]" value="5" checked="checked" />
Week6 <input type="checkbox" id="Week6" name="row5[]" value="6" />
Week7 <input type="checkbox" id="Week7" name="row5[]" value="7" checked="checked" />
Week8 <input type="checkbox" id="Week8" name="row5[]" value="8" />
</div><br/><br/>
<input name="update" type="submit" id="update" value="Update">
</form>
修改
我回到这个答案来引用一些东西，我才意识到我正在为复选框重新使用ID值.在这种情况下这不是问题，但始终是不好的做法.每个复选框应具有在页面上唯一的ID.所以....它实际上应该是id="row1week1"，id="row1week2" ... id="row8week1" ... id="row8week8".
edit
I came back to this answer to reference something and I just realized I was re-using ID values for the checkboxes. It wasn't a problem in this situation but it is always bad practice. Each checkbox should have an ID that is unique on the page. So.... it should really be id="row1week1", id="row1week2"... id="row8week1"... id="row8week8".
这篇关于使用复选框值更新SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
