使用ID单击按钮上的SQL查询

Question

所以我对php非常陌生，但我想创建一个基于mysql数据库的票证系统。

所以我现在的问题是，我想在我按下丁顿键的时候关闭一张票。我的票显示在一个表中，我不知道如何在我的更新功能中指定数据集，一旦我按下按钮。

到目前为止，我得到的是：

$con = mysqli_connect($ip, $user, $pw, $db);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if($show_updates == 'true') {
    echo file_get_contents('http://zombieland.eu/api/sht/update-1.2.php');    
}

$page_query = mysqli_query($con, "SELECT * FROM `SHT_Tickets` WHERE `status`='OPEN' ORDER BY `id` DESC");
$page_nums = mysqli_num_rows($page_query);    

echo "<div style=\"margin: 1% 2%;\">";
echo "<p>Tickets open: ". $page_nums ."</p>";

if ($page_nums >= 1) {
    echo "<table class=\"ui celled striped table\">";
    echo "<tr>";
    echo "    <td class='tbr tbt'><b>Beschreibung</b></td>";
    echo "    <td class='tbr tbt'><b>Datum</b></td>";
    echo "    <td class='tbr tbt'><b>Besitzer</b></td>";
    echo "    <td class='tbr tbt'><b>Welt</b></td>";
    echo "    <td class='tbr tbt'><b>Admin-Antwort</b></td>";
    echo "    <td class='tbr tbt'><b>User-Antwort</b></td>";
    echo "    <td class='tbr tbt'><b>Status</b></td>";
    echo "    <td class='tbr tbt'><b>Admin</b></td>";
    echo "    <td class='tbr tbt'><b>Antworten</b></td>";
    echo "    <td class='tbr tbt'><b>Schliessen</b></td>";
    echo "</tr>";

    while ($obj = mysqli_fetch_object($page_query)) {
        echo "<tr>";
        echo "<td class='tbr'>" . $obj->description . "</td>";
        echo "<td class='tbr'>" . $obj->date . "</td>";
        echo "<td class='tbr'>" . $obj->owner . "</td>";
        echo "<td class='tbr'>" . $obj->world . "</td>";
        echo "<td class='tbr'>" . $obj->adminreply . "</td>";
        echo "<td class='tbr'>" . $obj->userreply . "</td>";
        echo "<td class='tbr'>" . $obj->status . "</td>";
        echo "<td class='tbr'>" . $obj->admin . "</td>";
    echo "<td><form action='' method='POST'><button class='tbr' type='submit' value='. $obj->date .'>Schliessen</button></form></td>";
        echo "</tr>";
    }
}
echo "</table>";
echo "</div>";


if(isset($_POST['submit'])){

        $con = mysqli_connect($ip, $user, $pw, $db);
        if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();

    $page_query = mysqli_query($con, "UPDATE `SHT_Tickets` SET `status`='CLOSED' WHERE 'date'=buttonObject.value   ");
        $page_nums = mysqli_num_rows($page_query);
}
}

Answer 1

我的系统运行得很好。屏幕截图这里，但是下面的代码删除了调试信息。根据问题下的评论，我将名为date的列作为datetime数据类型。

请注意关于更新行区域的评论。这应该转化为准备的语句，因为注入风险，并思考这样一个事实:理论上没有什么能阻止某人关闭系统中的每个票证。他们可以制作一个循环，只需在他们自己的疯狂代码中做文章。因此，您需要查看其他会话信息来重新处理这个问题。

首先打开错误报告(前2行)。

此外，我还为要更新的票证日期添加了一个隐藏字段。这应该是一个从数据库中出来的auto_increment id，而不是一个日期时间，用于唯一性。我倒计时日期列名和分号关闭.将UPDATE块移动到顶部，以便在其下方进行刷新。

模式：

create table SHT_Tickets
(   id int auto_increment primary key,
    description varchar(100) not null,
    date datetime not null,
    owner varchar(100) not null,
    world varchar(100) not null,
    adminreply varchar(100) not null,
    userreply varchar(100) not null,
    status varchar(100) not null,
    admin varchar(100) not null
);

truncate table SHT_Tickets;
insert SHT_Tickets (description,date,owner,world,adminreply,userreply,status,admin) values
('fenster','2015-09-01 11:00:00','own','w','ar','der Himmel noch blaut','open','admin111'),
('trout','2015-09-02 11:00:00','own','w','ar','zwei','open','admin111'),
('fish','2015-09-03 11:00:00','own','w','ar','drei','closed','admin111'),
('mustard','2015-09-04 11:00:00','own','w','ar','haben Sie etwas?','open','admin111');

this.php：

    error_reporting(E_ALL);
    ini_set("display_errors", 1);

    $con = mysqli_connect('localhost', 'xxx', 'yyy', 'dbname');
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit();
    }

    // I am scrared, so I remmed this out
    //if($show_updates == 'true') {
    //    echo file_get_contents('http://zombieland.eu/api/sht/update-1.2.php');    
    //}

    if(isset($_POST['delMe'])){
        // Warning, it was db data to begin with
        // but the poster could fake the data and inject harmful code
        $theDate=$_POST['delMe'];
        $sql="UPDATE `SHT_Tickets` SET `status`='CLOSED' WHERE `date`='$theDate'";

        $page_query = mysqli_query($con, $sql);
        $page_nums = mysqli_num_rows($page_query); // residue left here of no use
    }

    $page_query = mysqli_query($con, "SELECT * FROM `SHT_Tickets` WHERE `status`='OPEN' ORDER BY `id` DESC");
    $page_nums = mysqli_num_rows($page_query);    

    echo "<div style=\"margin: 1% 2%;\">";
    echo "<p>Tickets open: ". $page_nums ."</p>";

    if ($page_nums >= 1) {
        echo "<table class=\"ui celled striped table\">";
        echo "<tr>";
        echo "    <td class='tbr tbt'><b>Beschreibung</b></td>";
        echo "    <td class='tbr tbt'><b>Datum</b></td>";
        echo "    <td class='tbr tbt'><b>Besitzer</b></td>";
        echo "    <td class='tbr tbt'><b>Welt</b></td>";
        echo "    <td class='tbr tbt'><b>Admin-Antwort</b></td>";
        echo "    <td class='tbr tbt'><b>User-Antwort</b></td>";
        echo "    <td class='tbr tbt'><b>Status</b></td>";
        echo "    <td class='tbr tbt'><b>Admin</b></td>";
        echo "    <td class='tbr tbt'><b>Antworten</b></td>";
        echo "    <td class='tbr tbt'><b>Schliessen</b></td>";
        echo "</tr>";

        while ($obj = mysqli_fetch_object($page_query)) {
            echo "<tr>";
            echo "<td class='tbr'>" . $obj->description . "</td>";
            echo "<td class='tbr'>" . $obj->date . "</td>";
            echo "<td class='tbr'>" . $obj->owner . "</td>";
            echo "<td class='tbr'>" . $obj->world . "</td>";
            echo "<td class='tbr'>" . $obj->adminreply . "</td>";
            echo "<td class='tbr'>" . $obj->userreply . "</td>";
            echo "<td class='tbr'>" . $obj->status . "</td>";
            echo "<td class='tbr'>" . $obj->admin . "</td>";
            echo '<td><form action="this.php" method="POST"><input type="hidden" name="delMe" value="' . $obj->date . '" /><input type="submit" value="Delete" /></form></td>';
            echo "</tr>";
        }
    }
    echo "</table>";
    echo "</div>";

    mysqli_close($con); // do not forget me