php - 在 PHP 中使用 MySQL 数据进行搜索和排序

Question

我正在学习 PHP，我创建了一个页面来显示 MySQL 中的数据表。我想对这些数据进行分页、搜索、排序。

我写了一些条件来搜索和排序。但这不起作用。我的分页似乎工作正常。

<?php
        echo "<form action = 'Activiti_Data_Table_Detail.php' method = 'post'>";
        echo "<input type = 'text' name = 'valueToSearch' placeholder = 'Value to search'><br><br>";
        echo "<input type = 'submit' name = 'search' value = 'Filter'><br><br>";
        echo "<input type = 'submit' name = 'ASC' value = 'Ascending'><br><br>";
        echo "<input type = 'submit' name = 'DESC' value = 'Descending'><br><br>";

        echo "<table id = 'datatable' border='1'>";
        echo "<thead>";
        echo "<tr>";    
        echo "<th>ID_</th>";   
        echo "<th>PROC_INST_ID_</th>";    
        echo "<th>BUSINESS_KEY_</th>";
        echo "<th>PROC_DEF_ID_</th>";
        echo "<th>START_TIME_</th>";
        echo "<th>END_TIME_</th>";
        echo "<th>DURATION_</th>";
        echo "<th>START_USER_ID_</th>";
        echo "<th>START_ACT_ID_</th>";
        echo "<th>END_ACT_ID_</th>";
        echo "<th>SUPER_PROCESS_INSTANCE_ID_</th>";
        echo "<th>DELETE_REASON_</th>";
        echo "<th>TENANT_ID_</th>";
        echo "<th>NAME_</th>";
        echo "</tr>";
        echo "</thead>";

        //connect to database
        $con=mysqli_connect("localhost","root","123456","activiti") or die(mysqli_connect_errno());

        //define how many results per page
        $result_per_page = 10;

        //number of results stored in database
        $sql = "SELECT * FROM act_hi_procinst";
        $result = mysqli_query($con, $sql);
        $num_of_results = mysqli_num_rows($result); //225

        //determine number of total pages available
        $number_of_pages = ceil($num_of_results/$result_per_page);

        //determine current page is on
        if (!isset($_GET['page'])) {
            $page  = 1;
        } else {
            $page = $_GET['page'];
        }

        //determine the SQL LIMIT starting number for the result on the displaying page
        $this_page_first_result = ($page - 1) * $result_per_page;

        if(isset($_POST['search']) & isset($_POST['ASC'])){
            $valueToSearch = $_POST['valueToSearch'];

            $sql = "SELECT * FROM act_hi_procinst WHERE concat(ID_, PROC_INST_ID_, PROC_DEF_ID_, START_TIME_, END_TIME_, DURATION_) LIKE '%".$valueToSearch."%'
                    ORDER BY END_TIME_ ASC LIMIT" .$this_page_first_result . ',' . $result_per_page;

        } elseif (isset($_POST['search']) & isset($_POST['DESC'])) {
            $valueToSearch = $_POST['valueToSearch'];

            $sql = "SELECT * FROM act_hi_procinst WHERE concat(ID_, PROC_INST_ID_, PROC_DEF_ID_, START_TIME_, END_TIME_, DURATION_) LIKE '%".$valueToSearch."%'
                    ORDER BY END_TIME_ DESC LIMIT" .$this_page_first_result . ',' . $result_per_page;

        } elseif (isset($_POST['search'])) {
            $valueToSearch = $_POST['valueToSearch'];

            $sql = "SELECT * FROM act_hi_procinst WHERE concat(ID_, PROC_INST_ID_, PROC_DEF_ID_, START_TIME_, END_TIME_, DURATION_) LIKE '%".$valueToSearch."%'
                    LIMIT" .$this_page_first_result . ',' . $result_per_page;

        } elseif (isset($_POST['ASC'])) {
            $sql = "SELECT * FROM act_hi_procinst  ORDER BY END_TIME_ ASC LIMIT" .$this_page_first_result . ',' . $result_per_page;

        } elseif (isset($_POST['DESC'])) {
            $sql = "SELECT * FROM act_hi_procinst  ORDER BY END_TIME_ DESC LIMIT" .$this_page_first_result . ',' . $result_per_page;

        } else {
            $sql = "SELECT * FROM act_hi_procinst LIMIT" .$this_page_first_result . ',' . $result_per_page;

        }

        $result = mysqli_query($con, $sql);

        while ($row = mysqli_fetch_array($result)) {
            echo "<tbody>";
            echo "<tr>";

            echo "<td><a href='Data_Table_Detail.php' target='_blank'>" . $row['ID_'] . "</a></td>";
            echo "<td>" . $row['PROC_INST_ID_'] . "</td>";
            echo "<td>" . $row['BUSINESS_KEY_'] . "</td>";
            echo "<td>" . $row['PROC_DEF_ID_'] . "</td>";
            echo "<td>" . $row['START_TIME_'] . "</td>";
            echo "<td>" . $row['END_TIME_'] . "</td>";
            echo "<td>" . $row['DURATION_'] . "</td>";
            echo "<td>" . $row['START_USER_ID_'] . "</td>";
            echo "<td>" . $row['START_ACT_ID_'] . "</td>";
            echo "<td>" . $row['END_ACT_ID_'] . "</td>";
            echo "<td>" . $row['SUPER_PROCESS_INSTANCE_ID_'] . "</td>";
            echo "<td>" . $row['DELETE_REASON_'] . "</td>";
            echo "<td>" . $row['TENANT_ID_'] . "</td>";
            echo "<td>" . $row['NAME_'] . "</td>";

            echo "</tr>";
        }
        echo "</tbody>";
        echo "</table>";
        echo "</form>";

        //display the links to the page
        for($page = 1; $page <= $number_of_pages; $page++) {
            echo '<a href="Activiti_Data_Table_Detail.php?page=' . $page . '">' . $page . '</a> ';
        }

        mysqli_close($con);           
    ?>

当我运行此命令时，它显示:警告:mysqli_fetch_array() 期望参数 1 为 mysqli_result。你能帮助我吗？谢谢!

Answer 1

尝试为此改变所有的条件:

  $valueToSearch  = isset($_POST['search']) ? $_POST['valueToSearch'] : '';
  $orderBy        = isset($_POST['ASC']) ? 'ORDER BY END_TIME_ ASC' : (isset($_POST['DESC']) ? 'ORDER BY END_TIME_ DESC' : '');

  $sql = "SELECT * FROM act_hi_procinst WHERE concat(ID_, PROC_INST_ID_, PROC_DEF_ID_, START_TIME_, END_TIME_, DURATION_) LIKE '%".$valueToSearch."%' " . $orderBy . " LIMIT " . $this_page_first_result . ',' . $result_per_page;