如何从php/mysql中的清单表查看/编辑/删除记录 [英] How to view/edit/delete records from a checklist table in php/mysql
问题描述
我已经在php中建立了一个清单表，数据被保存在mysql数据库表中.
I've built a checklist table in php, the data gets saved in a mysql database table.
使用以下代码，数据将保存在数据库中，现在我希望能够编辑和删除记录. 数据库表有两列-自动增量ID和ColRow，其中具有复选框的值.从数据库的不同表中获取值.标题和第一列是从两个表中提取的，这些表将根据用户选中的框保存在报告表中.
With the below code, the data gets saved in the database, now I want to be able to edit and delete the records. The database table has two columns - auto-increment Id and ColRow which has the values of the checked boxes. The values are fetched from the different tables of the database. The header and the first columns are fetched from two tables which gets saved in the report table depending on the boxes being checked by the user.
代码如下:
report.php
<?php
require_once 'pages/header.php';
require_once './functions/schema-functions.php';
$course = Schema::getCourse();
$objective = Schema::getObjective();
?>
<form id="addReport" action ='./functions/report-functions.php' method="post">
<table id="table1" class="table">
<?php
echo '<tr><th>Objectives</th>';
for ($i = 0; $i < count($course); $i++) {
echo '<th id = "rotate1">'. $course[$i]->commonName . '</th>';
}
echo '</tr>';
for ($y = 0; $y < count($objective); $y++) {
echo '<tr><th class=row-header>'.$objective[$y]->objective.'</th>';
for ($x = 0; $x < count($course); $x++) {
echo "<td><input name='check[]' type=checkbox value=c".$course[$x]->courseId."-o".$objective[$y]->objectiveId." id=checked></td>";
}
echo '</tr>';
}
?>
report-functions.php
report-functions.php
<?php
if( isset( $_POST['submit'], $_POST['check'] ) ){
try{
require_once 'db-connect.php';
$conn = DatabaseConnection::getConnection();
$sql='insert into `report` ( `colrow` ) values ( :value )';
$stmt = $conn->prepare( $sql );
if( $stmt ){
$conn->beginTransaction();
foreach( $_POST['check'] as $index => $value ) {
$result = $stmt->execute( [ ':value' => $value ] );
if( !$result ) {
throw new Exception( sprintf( 'Failed to execute query %d for %s', $index, $value ) );
}
}
$conn->commit();
exit();
}
}catch( Exception $e ){
$conn->rollback();
exit( $e->getMessage() );
}
}
?>
我希望在用户首次提交数据之后，页面应该加载一个带有先前选中框的新表，并且用户应该能够进行所需的更改并重新提交.数据库应进行相应的更新.
I want that after the user submits the data for the first time, the page should load a new table with the previously checked boxes and the user should be able to make the required changed and re-submit it. The database should be updated accordingly.
推荐答案
给出的标准似乎有点微不足道，并且由于没有给出真正的方向，但寻求了帮助，我将一些半伪代码拼凑在一起，也许，帮助您达到最终目标(或无论如何还是其中一些目标)
The criteria given seem a little tenuous and, as there is no real direction given but help was sought, I cobbled together some semi-pseudo code to, perhaps, help you on the way to the end goal ( or some of them anyway )
要编辑/删除记录，您需要某种方式来访问它们.最简单的方法是通过指向同一页面或专用处理程序脚本的querystring.以下选择专用的处理程序.
To edit/delete records you need some manner by which to access them. The simplest would be via querystring pointing to either the same page or a dedicated handler script. The following opts for a dedicated handler.
[假定已经包含上一页includes.这里的重要更改是添加了指向新脚本report-edit.php]
[ Assume the previous page includes are already included. The important changes here are the addition of the hyperlinks pointing to a new script report-edit.php ]
<form id="addReport" action ='./functions/report-functions.php' method="post">
<table id="table1" class="table">
<?php
echo '<tr><th>Objectives</th>';
for ( $i = 0; $i < count( $course ); $i++ ) {
$name=$course[$i]->commonName;
echo "<th>{$name}</th>";
}
echo '</tr>';
for( $y = 0; $y < count( $objective ); $y++ ) {
$objective_title=$objective[$y]->objective;
echo "<tr>
<th class=row-header>{$objective_title}</th>";
for ( $x = 0; $x < count( $course ); $x++ ) {
$cseid=$course[$x]->courseId;
$objid=$objective[$y]->objectiveId;
echo "<td>
<input name='check[]' type='checkbox' value='c{$cseid}-o{$objid}' />
<!-- EDIT LINKS EXAMPLE -->
<a href='./functions/report-edit.php?task=edit&course={$cseid}&objective={$objid}'>Edit</a>
<a href='./functions/report-edit.php?task=delete&course={$cseid}&objective={$objid}'>Delete</a>
</td>";
}
echo '</tr>';
}
?>
</table>
</form>
然后，查看report-edit.php〜半伪代码.
Then, looking at report-edit.php ~ semi-pseudo code.
<?php
/* report-edit.php */
$tasks=array('edit','delete');
$actions=array('commit-edit','commit-delete');
/* include database connections */
# require 'db-connect.php';
if( $_SERVER['REQUEST_METHOD']=='POST' && !empty( $_POST['action'] ) && in_array( $_POST['action'], $actions ) ){
$course=filter_input( INPUT_POST, 'course', FILTER_SANITIZE_STRING );
$objective=filter_input( INPUT_POST, 'objective', FILTER_SANITIZE_STRING );
$value=filter_input( INPUT_POST, 'check', FILTER_SANITIZE_STRING );
switch( $_POST['action'] ){
case 'commit-edit':
/* process form submission */
$sql='update `report` set `colrow`=:value where `course-id`=:cseid and `objective-id`=:objid';
/*
$args=array(
':value' => $value,
':cseid' => $course,
':objid' => $objective
);
$stmt=$db->prepare( $sql );
$result = $stmt->execute( $args );
exit( header( sprintf( 'Location: report.php?action=%s&status=%s', $_POST['action'], $result ) ) );
*/
break;
case 'commit-delete':
/* process form submission */
$sql='delete from `report` where `course-id`=:cseid and `objective-id`=:objid';
/*
$args=array(
':cseid' => $course,
':objid' => $objective
);
$stmt=$db->prepare( $sql );
$stmt->execute( $args );
exit( header( sprintf( 'Location: report.php?action=%s&status=%s', $_POST['action'], $result ) ) );
*/
break;
default:
exit('error');
break;
}
exit( $sql );
}
if( $_SERVER['REQUEST_METHOD']=='GET' && !empty( $_GET['task'] ) && in_array( $_GET['task'], $tasks ) ){
$course=filter_input( INPUT_GET, 'course', FILTER_SANITIZE_STRING );
$objective=filter_input( INPUT_GET, 'objective', FILTER_SANITIZE_STRING );
$head="
<!DOCTYPE html>
<html>
<head>
<title>{$_GET['task']}</title>
</head>
<body>";
$foot="
</body>
</html>";
switch( $_GET['task'] ){
case 'edit':
case 'delete':
/* render a form */
printf("
%s
<form method='post'>
<h1>%s record</h1>
<input type='text' name='course' value='%s' />
<input type='text' name='objective' value='%s' />
<input type='hidden' name='action' value='commit-%s' />
<input type='submit' />
</form>
%s",
$head,
ucfirst( $_GET['task'] ),
$course,
$objective,
$_GET['task'],
$foot
);
break;
default:
exit('error');
break;
}
} else {
http_response_code(404);
}
?>
如果不知道report.php中返回的模式或数据，几乎不可能给出关于如何重新检查复选框是否值为X的明确答案〜没有人会发现自己知道的值-它可能是字符串，整数或位等，因此...的每个复选框的值可能是cbanana-oharvest或c23-o44等...这是未知的！
Without knowing the schema or the data returned in report.php it is nigh on impossible to give a definitive answer as to how you re-check checkboxes if their value is X ~ nobody barr yourself knows the values held - it could be strings or integers or bits etc etc... so the value of each checkbox might be cbanana-oharvest or c23-o44 etc ... it is unknown!
还不知道为什么for( $i=0....语法用于数组迭代而不是foreach吗?在我看来，使用for( $i=0; $i... etc可能会出现索引等错误.但是，在重新选中复选框时，您需要测试数据库/array中的当前行是否等于其他内容-尽管请参见有关未知值的先前注释.因此，伪明智的做法:
It is also unknown why the for( $i=0.... syntax for array iteration rather than foreach? In my mind using for( $i=0; $i... etc opens up possibilities for errors with indices etc. However when it comes to re-checking the checkboxes you need to test if the current row in the database /array equals something else - see previous comment about unknown values though. So, pseudo wise:
if( $row['col-A-value' ]=="A value" && $row['col-B-value']=='B value' ){ $checked=' checked=true'; } else { $checked=''; }
<input name='check[]' type='checkbox' value='c{$cseid}-o{$objid}' {$checked} />
以上所有内容均未通过基本测试，并且如上所述.我相信，您对这个问题的投入越多，得到的回应就越多，但是如果您创建一个新脚本report-edit.php并添加下面的代码并对report.php进行更改，您应该有一个更好的理解如何进行，如果没有的话，我会在酒吧里！
None of the above has been tested more than a rudimentary test and it is, as stated, more pseudo than definitive. The more you put into the question the more you'll get in response I believe for the most part but if you create a new script report-edit.php and add the code below and make changes to report.php you ought to have a better understanding of how to proceed, if not I'll be in the pub!
这篇关于如何从php/mysql中的清单表查看/编辑/删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
