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

How to view/edit/delete records from a checklist table in php/mysql(如何从 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>';
}
?>

报告函数.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 等等……未知！

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} />