如何从 php/mysql 中的清单表中查看/编辑/删除记录

2022-01-17 00:00:00 sql-update php html sql-delete

我在 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>';
}
?>

报告函数.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 )

要编辑/删除记录，您需要某种方式来访问它们.最简单的方法是通过指向同一页面或专用处理程序脚本的查询字符串.以下选择专用处理程序.

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~半伪代码.

<?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!

相关文章