在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

尝试为此更改所有的ifs：

  $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;