更新使用GET URL ID创建的页面上的MySQL记录 [英] Update MySQL record on a page created with GET url ID
本文介绍了更新使用GET URL ID创建的页面上的MySQL记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我创建了一个简单的报告系统，我希望从数据库中生成菜单和页面。
我在YouTube上看到了这个视频，并设法创建菜单中添加以下代码。
我有一个称为Reports和列的数据库表，名为rep_id，rep_date，rep_ledit_date，rep_by，department，position，report和rep_to。另一个表称为用户名为id，用户名，密码，名字，姓氏，部门，位置和密码栏的用户。
我设法为报表选择了一条添加的记录，但是，我遇到以下问题。
1. rep_to不预先选择已经选择的选项
2.记录不能用php更新注意：未定义索引：rep_id在C：\ wamp \www\cme\edit -this-report.php在232行上，并且数据库没有更新。这行是选择报表的位置
请参阅下面的php代码。
<？php
if（isset（$ _ SESSION ['users']））{
$ uname = $ _SESSION ['users'];
$ fname = $ _SESSION ['firstname'];
$ lname = $ _SESSION ['lastname'];
$ dep = $ _SESSION ['depart'];
$ pos = $ _SESSION ['position'];
$ query = mysqli_query（$ con，SELECT * FROM users WHERE username = $ uname）;
while（$ row = mysqli_fetch_assoc（$ query））{
$ id = $ row ['id'];
$ fname = $ row ['first_name'];
$ lname = $ row ['last_name'];
$ dep = $ row ['department'];
$ pos = $ row ['position'];
$ repby = $ row ['first_name']。 。 $行[姓氏];
$ repdep = $ row ['department'];
$ reppos = $ row ['position'];
}
}
mysqli_select_db（$ con，$ db_name）;
$ edit =SELECT * FROM reports WHERE rep_id ='{$ _GET ['rep_id']}';
$ result = mysqli_query（$ con，$ edit）或die（mysqli_error（$ con））;
$ row2 = mysqli_fetch_array（$ result）;
if（isset（$ _ POST ['update']））{
$ _GET ['rep_id'] = $ row2 ['rep_id'];
$ reptype = $ _POST ['reporttype'];
$ report = $ _POST ['report'];
$ repto = $ _POST ['reportedto'];
$ update =（mysqli_select_db（$ con，$ db_name））;
if（！$ update）{
die（'Could not connect：'。mysql_error（$ con））;
}
else {
$ sql =更新报告SET rep_type ='$ reptype'，report ='$ report'，rep_to ='$ repto'，
rep_ledit_date = NOW （）WHERE rep_id ='{$ _ GET ['rep_id']}';
$ retval = mysqli_query（$ con，$ sql）;
if（！$ retval）{
$ errorMessage ='无法更新数据：'。 mysqli_error（$ CON）;
}
else {
$ success =更新数据成功\;
header（location：edit-this-report.php）;
mysqli_close（$ con）;
}
}
}
？>
以及表单代码：
< form name =editoraction =edit-this-report.phpmethod =post>
< p class =inline>
< span>
< label for =mem>报告< / label>
< input type =textname =reportedbymaxlength =20disabled value =<？php print $ fname。。$ lname;？> />
< / span>
< / p>
< p class =inline>
< span>
< label for =mem>部门名称< / label>< input type =textname =repdepartdisabled size =100maxlength =100value =< ;? php print $ dep;？>>
< / span>
< / p>
< p class =inline>
< span>
< label for =mem>位置< / label><输入禁用类型=textname =reppositionsize =100value =<？php print $ pos;？ >中>
< / span>
< / p>
< p>
< span>
< label for =mem>报告类型< / label>
< select name =reporttype>
< option value =<？php if（$ row2 ['rep_type'] ==='Daily Report'）echo'selected =selected'; ？>>每日报告< / option>
< option value =<？php if（$ row2 ['rep_type'] ==='Weekly Report'）echo'selected =selected'; ？>>每周报告< / option>
< option value =<？php if（$ row2 ['rep_type'] ==='Monthly Report'）echo'selected =selected'; ？>>月度报表< / option>
< option value =<？php if（$ row2 ['rep_type'] ==='Quarterly Report'）echo'selected =selected'; ？>>季度报告< / option>
< option value =<？php if（$ row2 ['rep_type'] ==='Annual Report'）echo'selected =selected'; ？>>年度报告< / option>
< option value =<？php if（$ row2 ['rep_type'] ==='Terminal Report'）echo'selected =selected'; >>>终端报告< / option>
< / select>
< span>
< / p>
< p>
< span>
< label for =mem>报告< / label>
< textarea name =reportid =reportrows =23cols =auto><？php echo $ row2 ['report'];？>< / textarea>
< span>
< / p>
< p>
< span>
< label for =mem>已报告给< / label>
< select name =reportedto>
<？php
require（includes / db.php）;
$ q2 =SELECT * FROM用户WHERE部门喜欢'％$ repdep％';
$ result3 = mysqli_query（$ con，$ q2）或死（mysqli_error（$ con））; （$ getuser = mysqli_fetch_array（$ result3））{
$ repto = $ getuser ['first_name']
。 。 $的getUser [姓氏];
？>
< option value =<？php echo $ repto;？>><？php echo $ repto; ？>< /选项取代;
<？php
}
？>
< / select>
< / span>
< / p>
< span>
< input name =updatetype =submitclass =btn btn-large btn-primaryid =report_buttonvalue =Submit Report>
< input name =canceltype =resetclass =btn btn-large btn-secondaryid =report_buttonvalue =取消所有更改>
< / span>
< / p>
< / form>
请在此帮助我。
谢谢！
解决方案
我没有看到 rep_id 变量在你的表单中设置，所以你在提交表单时没有回到那个值。您依靠前一个查询中的$ _GET ['rep_id']为您的UPDATE提供rep_id，但是我看不到您的表单中提供的$ _GET变量。 （也许不是最好的做法来混合POST和GET，更好地添加一个隐藏的表单var并将其设置为 rep_id ，并将其捕获为POST var。）
无论如何，我认为使代码工作的最简单方法是将rep_id附加到表单action属性：
< form name =editoraction =edit-this-report.php？rep_id =<？php echo $ _GET ['rep_id'];？> method =post>
如果您还有其他错误，请运行并报告。
在 SELECT * FROM users 查询中，另一个问题（尽管可能不是展示限制），但未引用$ uname var。尽管在SQL语句中使用不安全的，用户提供的或黑客可操纵的变量，但是将来会出现更大的问题，从而使数据库对SQL注入攻击持开放态度。
更新：
考虑这样的事情：
<？php
session_start（）;
require（'includes / db.php'）; //提供$ con，选择数据库
//获取登录信息
if（！isset（$ _ SESSION ['username']））{header（'Location：logon.php'） ; } //登录并设置会话变量
else {
list（$ uname，$ repby，$ dep，$ pos）=
array（$ _ SESSION ['username']，$ _SESSION [ 'repby']，$ _SESSION ['department']，$ _SESSION ['position']）;
}
//获取报告ID如果GET'd
if（isset（$ _ GET ['rep_id']））{
$ rep_id = $ _GET [ 'rep_id'];
$ b $ //更新报告如果POST'd
else if（isset（$ _ POST ['update']））{
$ rep_id = $ _POST [ 'rep_id'];
$ reptype = $ _POST ['reporttype'];
$ report = $ _POST ['report'];
$ repto = $ _POST ['reportedto'];
if（mysqli_stmt_prepare（$ stmt，'UPDATE reports SET rep_type =？，report =？，rep_to =？，rep_ledit_date =？WHERE rep_id =？'））{
mysqli_stmt_bind_param（$ stmt ，'sssi'，$ reptype，$ report，$ repto，NOW（），$ rep_id）;
mysqli_stmt_execute（$ stmt）;
mysqli_stmt_close（$ stmt）;
}
else {$ errorMessage ='无法更新报告数据：'。 mysqli_error（$ CON）; }
}
else {die（'no report id'）; }
//获取/验证报告信息（如果在post'd后保存数据库调用，可以移动到get'd）
list（$ rep_type，$ report）= array（ ''，''）;
$ stmt = mysqli_stmt_init（$ con）;
if（mysqli_stmt_prepare（$ stmt，'SELECT rep_id，rep_type，report FROM Reports WHERE rep_id =？'））{
mysqli_stmt_bind_param（$ stmt，'i'，$ rep_id）;
mysqli_stmt_execute（$ stmt）;
mysqli_stmt_bind_result（$ stmt，$ rep_id，$ rep_type，$ report）;
mysqli_stmt_fetch（$ stmt）;
mysqli_stmt_close（$ stmt）;
}
else {$ errorMessage ='无法选择报告数据：'。 mysqli_error（$ CON）; }
？>
< input type =hiddenname =rep_idvalue =<？= $ rep_id？>>
< p class =inline>
< span>
< label for =mem>报告< / label>
< input type =textname =reportedbymaxlength =20disabled value =<？= $ repby？> />
< / span>
< / p>
< p class =inline>
< span>
< label for =mem>部门名称< / label>< input type =textname =repdepartdisabled size =100maxlength =100value =< ;? = $ DEP>>
< / span>
< / p>
< p class =inline>
< span>
< label for =mem>位置< / label><输入禁用类型=textname =reppositionsize =100value =<？= $ pos？> >
< / span>
< / p>
< p>
< span>
< label for =mem>报告类型< / label>
< select name =reporttype>
$ list（$ rep_type_da，$ rep_type_we，$ rep_type_mo，$ rep_type_qu，$ rep_type_an，$ rep_type_te）= array（''，''，' '，''，''，''）;
switch（$ rep_type）{
case'Daily Report'：$ rep_type_da ='selected';打破;
case'Daily Report'：$ rep_type_we ='selected';打破;
case'Daily Report'：$ rep_type_mo ='selected';打破;
case'Daily Report'：$ rep_type_qu ='selected';打破;
case'Daily Report'：$ rep_type_an ='selected';打破;
case'Daily Report'：$ rep_type_te ='selected';打破;
}
？>
< option value =每日报告<？= $ rep_type_da？>>每日报告< / option>
< option value =每周报告<？= $ rep_type_we？>>每周报告< / option>
< option value =月度报告<？= $ rep_type_mo？>>月度报告< / option>
< option value =Quarterly Report<？= $ rep_type_qu？>>> Quarterly Report< / option>
< option value =年度报告<？= $ rep_type_an？>>年度报告< / option>
< option value =终端报告<？= $ rep_type_te？>>终端报告< / option>
< / select>
< span>
< / p>
< p>
< span>
< label for =mem>报告< / label>
< textarea name =reportid =reportrows =23cols =auto><？= $ report？>< / textarea>
< span>
< / p>
< p>
< span>
< label for =mem>已报告给< / label>
< select name =reportedto>
< option value =>< / option>
<？php
if（mysqli_stmt_prepare（$ stmt，'SELECT CONCAT（first_name，last_name）AS repto FROM users WHERE department LIKE？'））{
mysqli_stmt_bind_param（$ stmt，'s'，％$ dep％）;
mysqli_stmt_execute（$ stmt）;
mysqli_stmt_bind_result（$ stmt，$ repto）;
while（mysqli_stmt_fetch（$ stmt））{
echo'< option value =''。$ repto。'>'。 $ repto。 < /选项> \\\
;
}
mysqli_stmt_close（$ stmt）;
}
else {$ errorMessage ='无法选择dep用户数据：'。 mysqli_error（$ CON）; }
？>
< / select>
< / span>
< / p>
< span>
< input name =updatetype =submitclass =btn btn-large btn-primaryid =report_buttonvalue =Submit Report>
< input name =canceltype =resetclass =btn btn-large btn-secondaryid =report_buttonvalue =取消所有更改>
< / span>
< / p>
< / form>
我没有设置数据库，所以我没有测试过它。如果您运行此操作并获取错误，请将其发布到评论中。
I am creating a simple reporting system and I want the menues and pages to be generated from the database.
I saw this video on YouTube and managed to create a menu with the following code.
I have a database table called Reports and columns called rep_id, rep_date, rep_ledit_date, rep_by, department, position, report, and rep_to. And another table called users with columns called id, username, password, first_name, last_name, department, postion, and passphrase.
I managed to select the an added record for the reports table, however, I have the following problems. 1. The rep_to doesn't preselect the already chosen option 2. The record cannot be updated with php Notice: Undefined index: rep_id in C:\wamp\www\cme\edit-this-report.php on line 232 and no update on the database. This line is where report table is selected Please see the php code below.
<?php
if(isset($_SESSION['users'])) {
$uname = $_SESSION['users'];
$fname = $_SESSION['firstname'];
$lname = $_SESSION['lastname'];
$dep = $_SESSION['depart'];
$pos = $_SESSION['position'];
$query = mysqli_query($con, "SELECT * FROM users WHERE username = $uname");
while($row = mysqli_fetch_assoc($query)) {
$id=$row['id'];
$fname=$row['first_name'];
$lname=$row['last_name'];
$dep = $row['department'];
$pos = $row['position'];
$repby = $row['first_name'] . " " . $row['last_name'];
$repdep = $row['department'];
$reppos = $row['position'];
}
}
mysqli_select_db($con, $db_name);
$edit= "SELECT * FROM reports WHERE rep_id = '{$_GET['rep_id']}'";
$result = mysqli_query($con, $edit) or die(mysqli_error($con));
$row2 = mysqli_fetch_array($result);
if(isset($_POST['update'])) {
$_GET['rep_id']=$row2['rep_id'];
$reptype = $_POST['reporttype'];
$report = $_POST['report'];
$repto = $_POST['reportedto'];
$update=(mysqli_select_db($con, $db_name));
if(!$update) {
die('Could not connect: ' . mysql_error($con));
}
else {
$sql = "UPDATE reports SET rep_type='$reptype', report='$report', rep_to='$repto',
rep_ledit_date=NOW() WHERE rep_id='{$_GET['rep_id']}'";
$retval = mysqli_query($con, $sql);
if(!$retval ) {
$errorMessage='Could not update data: ' . mysqli_error($con);
}
else {
$success="Updated data successfully\n";
header("location:edit-this-report.php");
mysqli_close($con);
}
}
}
?>
And the form code:
<form name="editor" action="edit-this-report.php" method="post" >
<p class="inline">
<span>
<label for="mem">Reported by</label>
<input type="text" name="reportedby" maxlength="20" disabled value="<?php print $fname . " " . $lname; ?>" />
</span>
</p>
<p class="inline">
<span>
<label for="mem">Department Name</label><input type="text" name="repdepart" disabled size="100" maxlength="100" value="<?php print $dep; ?>">
</span>
</p>
<p class="inline">
<span>
<label for="mem">Position</label><input disabled type="text" name="repposition" size="100" value="<?php print $pos; ?>">
</span>
</p>
<p>
<span>
<label for="mem">Report Type</label>
<select name="reporttype">
<option value=""<?php if ($row2['rep_type'] === 'Daily Report') echo ' selected="selected"'; ?>>Daily Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Weekly Report') echo ' selected="selected"'; ?>>Weekly Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Monthly Report') echo ' selected="selected"'; ?>>Monthly Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Quarterly Report') echo ' selected="selected"'; ?>>Quarterly Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Annual Report') echo ' selected="selected"'; ?>>Annual Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Terminal Report') echo ' selected="selected"'; ?>>Terminal Report</option>
</select>
<span>
</p>
<p>
<span>
<label for="mem">Report</label>
<textarea name="report" id="report" rows="23" cols="auto" ><?php echo $row2['report'];?></textarea>
<span>
</p>
<p>
<span>
<label for="mem">Reported to</label>
<select name="reportedto">
<?php
require ("includes/db.php");
$q2= "SELECT * FROM users WHERE department like '%$repdep%'";
$result3=mysqli_query($con, $q2) or die(mysqli_error($con));
while ($getuser=mysqli_fetch_array($result3)){
$repto=$getuser['first_name'] . " " . $getuser['last_name'];
?>
<option value="<?php echo $repto; ?>"><?php echo $repto; ?></option>;
<?php
}
?>
</select>
</span>
</p>
<span>
<input name="update" type="submit" class="btn btn-large btn-primary" id="report_button" value="Submit Report" >
<input name="cancel" type="reset" class="btn btn-large btn-secondary" id="report_button" value="Cancel All Changes" >
</span>
</p>
</form>
Please help me on this.
Thanks!
解决方案
I don't see a rep_id variable set in your form, so you're not getting back that value when submitting the form. You're relying on $_GET['rep_id'] in the previous query to provide the rep_id for your UPDATE, but I see no $_GET vars provided in your form. (Maybe not best practice to mix POST and GET, better yet add a hidden form var and set it to rep_id, and capture that as a POST var.)
None the less, the easiest way I can think of to make your code work is to append the rep_id to the form action attribute:
<form name="editor" action="edit-this-report.php?rep_id=<?php echo $_GET['rep_id']; ?>" method="post" >
Run that and report back if you get any more errors.
Another problem, though maybe not a show-stopper, before that in the SELECT * FROM users query, the $uname var is not quoted. Bigger, future problem though is using insecure, user-provided or hacker-manipulable variables in your SQL statements leaves your database open to sql-injection attacks.
UPDATE:
Consider something like this:
<?php
session_start();
require ('includes/db.php'); // provides $con, select database
// get logon info
if ( !isset($_SESSION['username']) ) { header('Location: logon.php'); } // logon and set session vars
else {
list($uname, $repby, $dep, $pos) =
array($_SESSION['username'], $_SESSION['repby'], $_SESSION['department'], $_SESSION['position']);
}
// get report id if GET'd
if ( isset($_GET['rep_id']) ) {
$rep_id = $_GET['rep_id'];
}
// update report if POST'd
else if ( isset($_POST['update'] ) ) {
$rep_id = $_POST['rep_id'];
$reptype = $_POST['reporttype'];
$report = $_POST['report'];
$repto = $_POST['reportedto'];
if ( mysqli_stmt_prepare($stmt, 'UPDATE reports SET rep_type= ?, report= ?, rep_to= ?, rep_ledit_date= ? WHERE rep_id= ?') ) {
mysqli_stmt_bind_param($stmt, 'sssi', $reptype, $report, $repto, NOW(), $rep_id);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
else { $errorMessage='Could not update report data: ' . mysqli_error($con); }
}
else { die('no report id'); }
// get/verify report info (can be moved to get'd if to save a db call when post'd)
list($rep_type, $report) = array('', '');
$stmt = mysqli_stmt_init($con);
if ( mysqli_stmt_prepare($stmt, 'SELECT rep_id, rep_type, report FROM Reports WHERE rep_id = ?') ) {
mysqli_stmt_bind_param($stmt, 'i', $rep_id);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $rep_id, $rep_type, $report);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);
}
else { $errorMessage='Could not select report data: ' . mysqli_error($con); }
?>
<form name="editor" action="edit-this-report.php" method="post" >
<input type="hidden" name="rep_id" value="<?=$rep_id?>">
<p class="inline">
<span>
<label for="mem">Reported by</label>
<input type="text" name="reportedby" maxlength="20" disabled value="<?=$repby?>" />
</span>
</p>
<p class="inline">
<span>
<label for="mem">Department Name</label><input type="text" name="repdepart" disabled size="100" maxlength="100" value="<?=$dep?>">
</span>
</p>
<p class="inline">
<span>
<label for="mem">Position</label><input disabled type="text" name="repposition" size="100" value="<?=$pos?>">
</span>
</p>
<p>
<span>
<label for="mem">Report Type</label>
<select name="reporttype">
<?php
list($rep_type_da, $rep_type_we, $rep_type_mo, $rep_type_qu, $rep_type_an, $rep_type_te) = array('', '', '', '', '', '');
switch ( $rep_type ) {
case 'Daily Report': $rep_type_da = ' selected'; break;
case 'Daily Report': $rep_type_we = ' selected'; break;
case 'Daily Report': $rep_type_mo = ' selected'; break;
case 'Daily Report': $rep_type_qu = ' selected'; break;
case 'Daily Report': $rep_type_an = ' selected'; break;
case 'Daily Report': $rep_type_te = ' selected'; break;
}
?>
<option value="Daily Report" <?=$rep_type_da?>>Daily Report</option>
<option value="Weekly Report" <?=$rep_type_we?>>Weekly Report</option>
<option value="Monthly Report" <?=$rep_type_mo?>>Monthly Report</option>
<option value="Quarterly Report"<?=$rep_type_qu?>>Quarterly Report</option>
<option value="Annual Report" <?=$rep_type_an?>>Annual Report</option>
<option value="Terminal Report" <?=$rep_type_te?>>Terminal Report</option>
</select>
<span>
</p>
<p>
<span>
<label for="mem">Report</label>
<textarea name="report" id="report" rows="23" cols="auto" ><?=$report?></textarea>
<span>
</p>
<p>
<span>
<label for="mem">Reported to</label>
<select name="reportedto">
<option value=""></option>
<?php
if ( mysqli_stmt_prepare($stmt, 'SELECT CONCAT(first_name, last_name) AS repto FROM users WHERE department LIKE ?') ) {
mysqli_stmt_bind_param($stmt, 's', "%$dep%");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $repto);
while ( mysqli_stmt_fetch($stmt) ) {
echo '<option value="' . $repto . '">' . $repto . "</option>\n";
}
mysqli_stmt_close($stmt);
}
else { $errorMessage='Could not select dep user data: ' . mysqli_error($con); }
?>
</select>
</span>
</p>
<span>
<input name="update" type="submit" class="btn btn-large btn-primary" id="report_button" value="Submit Report" >
<input name="cancel" type="reset" class="btn btn-large btn-secondary" id="report_button" value="Cancel All Changes" >
</span>
</p>
</form>
I've not set up the database so I've not tested it. If you run this and get errors, post them in the comments.
这篇关于更新使用GET URL ID创建的页面上的MySQL记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
