如何根据复选框输入更新MySQL列 [英] How to update a MySQL column based on a checkbox input
问题描述
我是这个论坛的新增内容。
我遇到的问题如下。
我有一个MySQL表'Announce'，包含以下字段：
id
------
advert
----------
date
----------
file
----------
approv
----------
从一个页面填充数据 - 其中所有列获取除approv之外的值。要填充字段有一个新的PHP页面。字段approv根据其中的复选框的标记获得已批准值
我遇到的问题是我无法读取文本框的值显示id并获取相应的复选框值，以便特定记录获得批准并更新到MySQL表。
我写的代码如下：
<！DOCTYPE html PUBLIC - // W3C // DTD XHTML 1.0 Transitional // ENhttp：//www.w3 .org
/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
< html xmlns =http://www.w3.org/1999/xhtml>
< head>
< meta http-equiv =Content-Typecontent =text / html; charset = utf-8/>
< title> Annpouncements |待处理列表< / title>
< style type =text / css>
.textinput {
height：20px;
width：20px;
border-top-width：0px;
border-right-width：0px;
border-bottom-width：0px;
border-left-width：0px;
border-top-style：none;
border-right-style：none;
border-bottom-style：none;
border-left-style：none;
}
< / style>
< / head>
< body>
< table width =800border =0align =centercellpadding =0cellspacing =0>
< tr>
< td>< table width =100％border =0cellspacing =0cellpadding =0>
< tr>
< td>& nbsp;< / td>
< / tr>
< tr>
< td height =164>< form id =form1name =form1method =postaction =>
< table width =100％border =0cellspacing =0cellpadding =0>
< tr>
< td width =11％height =31align =center>< label> Id< / label>< / td&
< td width =15％height =31align =center>< label>日期< / label>< / td&
< td width =52％align =center>< label>标题< / label>< / td&
< td width =22％align =center>< label> Status< / label>< / td>
< / tr>
<？php
//从数据库打开表格声明并按降序列出日期
$ result = mysql_query（SELECT * FROM announce ORDER by date DESC）或
die（mysql_error（））;
//定义一个变量来获取表的行
$ ann = mysql_fetch_array（$ result）;
//定义一个变量来获取行的数量
$ num = mysql_num_rows（$ result）;
$ i = 0;
while（$ i <$ num）{？>
<？php $ approv [$ i] = mysql_result（$ result，$ i，approv）; >
< tr>
<？php if（$ approv [$ i]！=approved）{？>
< td height =36align =center>< input name =idtype =text
class =textinputid =idvalue =< ;？php echo mysql_result（$ result，$ i，id）;
？> />< / td>
<？php $ ids = mysql_result（$ result，$ i，id）;
// $ inp = $ _POST [id] [$ i];
// echo'输入值：'。$ inp。 '< br />'？>
< td height =36align =center>< label>
<？php echo mysql_result（$ result，$ i，date）; ？>< / label>< / td>
< td align =center>< label><？php echo mysql_result（$ result，$ i，advert）;
？>< / label>< / td>
< td align =center>< / label>< input type =checkboxname =approv []/>
< label for =approv>< / label>< / td>
<？php $ idan = mysql_query（SELECT * FROM announce WHERE id == $ ids）; >
<？php
if（isset（$ _ POST ['button']））
{
$ apprv = $ _POST [approv];
// echo'id ='。$ ids。'< br />';
$ how_many = count（$ apprv）;
// echo'Row selected'。$ how_many。 '< br />';
foreach（$ _POST ['approv'] as $ apprValue）
$ txtvalue [] = $ _POST [$ apprValue];
echo'txtvalue ='。$ txtvalue。 '< br />';
mysql_query（UPDATE announce SET approv ='approved'WHERE id ==
$ idan）;
}
}
？>
<？php}？>
< / tr>
<？php
$ i ++;
}
？>
< tr>
< td height =44>& nbsp;< / td>
< td>& nbsp;< / td>
< td align =center>& nbsp;< / td>
< td align =center>< input type =submitname =buttonid =button
value =Submit/>< / td>
< / tr>
< / table>
< / form>< / td>
< / tr>
< / table>< / td>
< / tr>
< / table>
帮助我获得正确的解决方案，更新表的值为已批准的值已经检查的行。
期待您的宝贵帮助ASAP
好的，所以这里有很多问题，但是我要对这个问题采取行动的代码生成您的复选框。特别是以下位：
< input name =idtype =textclass =textinputid = idvalue =<？php echo mysql_result（$ result，$ i，id）;？> />
< input type =checkboxname =approv []/>
这不是一个可靠的方法来创建你想要的信息， 。
-
$ _ POST ['id']将只包含一个值。 -
$ _ POST ['approv']将会创建一个地图，是一个零索引的数组，让你没有有用的方法来关联复选框到id，而不保留id到循环计数。 （但如果你打算这样做，只需使用PHP给你的构造。）
好，现在我们知道需要做一些改变。首先，我们将尝试使复选框工作。但是我们仍然有一些你的代码的结构的问题。特别是因为它看起来像你不明白请求如何工作在所有与PHP。
首先，我们想要一个方法，以确保我们知道每个ID。这实际上很容易补救。我们可以更改：
< input type =checkboxname =approv []/>
到
< input type =checkboxname =approv []value =<？= $ id？> />
这样，当你循环 $ _ POST ['approv'] ，你实际上得到一个id，而不是一个偏移量。
但仍然是其他问题 b $ b
没有理由在你的渲染循环中查看$ _POST（ while（$ i <$ num））。使用PHP构造有利。 foreach（$ result_rows as $ row）比您的代码更加惯用和可读：
code> //定义一个变量来获取表的行
$ ann = mysql_fetch_array（$ result）;
//定义一个变量来获取行的数量
$ num = mysql_num_rows（$ result）;
$ i = 0;
while（$ i <$ num）{？>
... // do work
... //使用$ _POST执行某些操作。 < - 错！
< ;? }？>
应该是更像：
if（empty（$ _ POST））{
//当我们需要显示信息时才运行查询
$ result = mysql_query（SELECT * FROM announce ORDER by日期DESC）或die（mysql_error（））;
//定义一个变量来获取表的行
$ result_rows = mysql_fetch_array（$ result）;
foreach（$ result_rows as $ row）{？>
... //显示形式
< ;? }
} else {
$ success = array（）;
foreach（$ _ POST ['approv'] as $ approved_id）{
//理想情况下，我们将所有这些更新合并到一个语句
$ stmt = mysqli_prepare（$ link，UPDATE announce SET approv ='approved'WHERE id =？i）;
mysqli_stmt_bind_params（$ stmt，$ approved_id）;
//然后可以查看$ success数组，看看是否有失败。
$ success [$ approved_id] = mysqli_stmt_execute（$ stmt）;
}
}
I am rather a new addition to this forum. and to coding with PHP so as novice I would like to have all your help.
The problem I face is as follows.
I have a MySQL table 'Announce' with the following fields
id
----------
advert
----------
date
----------
file
----------
approv
----------
to which data gets populated from a page - in which all the columns gets its value except approv. To populate the field there is a new PHP page. The field approv gets the value 'approved' based on the tick of the checkbox in it
The problem I face is that I can't read the values of the text box that display the id and get the corresponding checkbox value so that the particular record gets approved and gets updated to the MySQL table.
The code I wrote is given below
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org
/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Annpouncements | Pending List</title>
<style type="text/css">
.textinput {
height: 20px;
width: 20px;
border-top-width: 0px;
border-right-width: 0px;
border-bottom-width: 0px;
border-left-width: 0px;
border-top-style: none;
border-right-style: none;
border-bottom-style: none;
border-left-style: none;
}
</style>
</head>
<body>
<table width="800" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td> </td>
</tr>
<tr>
<td height="164"><form id="form1" name="form1" method="post" action="">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="11%" height="31" align="center"><label>Id</label></td>
<td width="15%" height="31" align="center"><label>Date</label></td>
<td width="52%" align="center"><label>Title</label></td>
<td width="22%" align="center"><label>Status</label></td>
</tr>
<?php
//Open the table announce from the database and list date in descending order
$result = mysql_query("SELECT * FROM announce ORDER by date DESC" )or
die(mysql_error());
//Define a variable to get the rows of the table
$ann = mysql_fetch_array($result);
//Define a variable to get the no of rows
$num = mysql_num_rows($result);
$i=0;
while($i<$num) {?>
<?php $approv[$i]= mysql_result($result,$i,"approv"); ?>
<tr>
<?php if($approv[$i] !== "approved"){?>
<td height="36" align="center"><input name="id" type="text"
class="textinput" id="id" value="<?php echo mysql_result($result,$i,"id");
?>" /></td>
<?php $ids = mysql_result($result,$i,"id");
//$inp = $_POST["id"][$i];
//echo 'Input value : ' .$inp. '<br/>' ?>
<td height="36" align="center"><label>
<?php echo mysql_result($result,$i,"date"); ?></label></td>
<td align="center"><label><?php echo mysql_result($result,$i,"advert");
?></label></td>
<td align="center"></label><input type="checkbox" name="approv[]" />
<label for="approv"></label></td>
<?php $idan = mysql_query("SELECT * FROM announce WHERE id == $ids"); ?>
<?php
if (isset($_POST['button']))
{
$apprv = $_POST["approv"];
//echo 'id = '.$ids.'<br/>';
$how_many = count($apprv);
//echo 'Row selected' .$how_many. '<br/>';
foreach ($_POST['approv'] as $apprValue)
$txtvalue[] = $_POST[$apprValue];
echo 'txtvalue = ' .$txtvalue. '<br/>';
mysql_query("UPDATE announce SET approv = 'approved'WHERE id ==
$idan ");
}
}
?>
<?php } ?>
</tr>
<?php
$i++;
}
?>
<tr>
<td height="44"> </td>
<td> </td>
<td align="center"> </td>
<td align="center"><input type="submit" name="button" id="button"
value="Submit" /></td>
</tr>
</table>
</form></td>
</tr>
</table></td>
</tr>
</table>
So please help me with getting the correct solution for updating the table with the value 'approved' for only those rows that has been checked.
Looking forward for your valuable help ASAP
Ok, so there are a number of issues here, but I'm going to take a stab at the issue being with the lines of code that are generating your checkboxes. Specifically the following bits:
<input name="id" type="text" class="textinput" id="id" value="<?php echo mysql_result($result,$i,"id"); ?>" />
<input type="checkbox" name="approv[]" />
This just isn't a reliable way to create the information that you want, for a number of reasons.
$_POST['id']will only contain a single value. It will not create a map of ids to approval states that seems to be your intent.$_POST['approv']will be a zero-indexed array, leaving you no useful way to correlate a checkbox to anidwithout keeping a map ofidto loop counts. (But if you're going to do that, just use the constructs PHP gives you.)
Ok so, now we know we need to make some changes. First we'll try to get the checkboxes working. But we'll still have the issue of some of your code's structure. Especially since it looks like you don't understand how a request works at all with PHP.
First, we want a way to make sure we know the approval state for each id. That's actually easily remedied. We can just change:
<input type="checkbox" name="approv[]" />
to
<input type="checkbox" name="approv[]" value="<?= $id ?>" />
That way, when you loop over $_POST['approv'], you actually get an id, rather than just an offset. This will allow you to keep the same for loop at the bottom.
BUT THERE ARE STILL ISSUES
There is no reason to be looking at $_POST within your rendering loop (while($i<$num)). Use PHP constructs to your advantage. foreach($result_rows as $row) is much more idiomatic and readable than your code:
//Define a variable to get the rows of the table
$ann = mysql_fetch_array($result);
//Define a variable to get the no of rows
$num = mysql_num_rows($result);
$i=0;
while($i<$num) {?>
... // do work
... // Do something with $_POST. <-- wrong!!!
<? } ?>
Should be something more like:
if (empty($_POST)) {
// Only run the query when we need to display information
$result = mysql_query("SELECT * FROM announce ORDER by date DESC") or die(mysql_error());
//Define a variable to get the rows of the table
$result_rows = mysql_fetch_array($result);
foreach($result_rows as $row) { ?>
... // Display form
<? }
} else {
$success = array();
foreach($_POST['approv'] as $approved_id) {
// Ideally we would combine all of these updates into one statement
$stmt = mysqli_prepare($link, "UPDATE announce SET approv = 'approved' WHERE id = ?i");
mysqli_stmt_bind_params($stmt, $approved_id);
// You then can look over the $success array to see if any failed.
$success[$approved_id] = mysqli_stmt_execute($stmt);
}
}
这篇关于如何根据复选框输入更新MySQL列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
