dqdjfb2325
dqdjfb2325
2017-01-01 20:43

PHP简单分页

已采纳

the below code is getting some values from DB by "select option form" , i recently added Pagination snip to limit the results, when i run the code it fetch 5 recorders as defined,but didn't show the remaining number of pages. what im doing wrong here ?

<?php

$per_page = 5;
if (isset($_GET["page"])) {
    $page = $_GET["page"];
} else {
    $page = 1;
}

$start_from = ($page - 1) * $per_page;

if (!empty($_POST['form_val']) && isset($_POST['form_val'])) {
    $_POST['form_val'] = 0;

    $sql = "SELECT u.log_id , u.user_name, s.site, u.date ,u.comment , l.location, e.picture  FROM `pool` u, `location_all` l , `site_all` s JOIN db2.user e 
    where l.location_id = u.location and s.site_id = u.site and e.user_id = u.user_id";

    if (!empty($_POST['Location']) && isset($_POST['Location'])) {
        $sql = $sql . " AND location =" . $_POST['Location'];
    }
    $strtdate = $_POST['Sday'];
    $enddate  = $_POST['Eday'];
    if (!empty($_POST['Sday']) && isset($_POST['Sday']) && !empty($_POST['Eday']) && isset($_POST['Eday'])) {
        $sql = $sql . " AND date between '" . $strtdate . "' and '" . $enddate . "'";
    } elseif (!empty($_POST['Sday']) && isset($_POST['Sday'])) {
        $sql = $sql . " AND date>='" . $strtdate . "'";
    } elseif (!empty($_POST['Eday']) && isset($_POST['Eday'])) 
        $sql = $sql . " AND date<='" . $enddate . "'";
    }

    if (!empty($_POST['Site']) && isset($_POST['Site'])) {
        $sql = $sql . " AND u.site=" . $_POST['Site'];
    }

    $sql = $sql . " LIMIT $start_from, $per_page";

    if (mysqli_query($conn, $sql)) {
        $result = mysqli_query($conn, $sql);
        if (mysqli_num_rows($result) >= 1) {
            $rowcount = mysqli_num_rows($result);
            echo '<legend> ' . $rowcount . ' Records Found !!!</legend>';
            echo '<br><br>';
            echo "<table class='srchtable'>
                    <tr>
                    <th>Picture</th>
                    <th>Date</th>
                    <th>User Name</th>
                    <th>country</th>
                    <th>Location</th>
                    <th>Site</th>
                    <th>Comment</th>
                    </tr>";
            while ($row = mysqli_fetch_array($result)) {
                echo "<tr>";
                echo "<td> <img src='" . $row['picture'] . "' alt='' style='width:70%; height:auto; border-radius: 50%;'> </td>";
                echo "<td>" . $row['date'] . "</td>";
                echo "<td>" . $row['user_name'] . "</td>";
                echo "<td>" . $row['country'] . "</td>";
                echo "<td>" . $row['location'] . "</td>";
                echo "<td>" . $row['site'] . "</td>";
                echo "<td>" . $row['comment'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
            $total_pages = ceil($rowcount / $per_page);
            echo "<center><a href='?page=1'>" . 'First Page' . "</a> ";
            for ($i = 1; $i <= $total_pages; $i++) {

                echo "<a href='?page=" . $i . "'>" . $i . "</a> ";

            }
            echo "<a href='?page=$total_pages'>" . 'Last Page' . "</a></center> ";
        } else {
            echo '<p>No Results Found !!!</p>';
        }
    }
}
?>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douqiao5543 douqiao5543 4年前

    As I said in my comments, for displaying pagination links:

    • You're counting total number of rows but incorporating LIMIT and OFFSET clauses in your SELECT query, this won't give the correct number of row count. Your SELECT query should not contain this part, ... LIMIT $start_from, $per_page.
    • Since you're filtering the results based on several $_POST data, you should incorporate those conditions in your pagination links as well, otherwise when you visit a different page(through pagination link), you won't get the desired result, and that's because $_POST data will not be retained when you hop from page to page. Better that you change the method of your <form> from POST to GET, because in this way it'd be easier for you to catch and manipulate things when you hop from one page to another using pagination links.

    So based on the above points, your code should be like this:

    $per_page = 5;
    if (isset($_GET["page"])) {
        $page = $_GET["page"];
    } else {
    
        $page = 1;
    
    }
    
    $start_from = ($page - 1) * $per_page;
    if (!empty($_GET['form_val']) && isset($_GET['form_val'])) {
        $_GET['form_val'] = 0;
    
        $sql = "SELECT u.log_id , u.user_name, s.site, u.date ,u.comment , l.location, e.picture  FROM `pool` u, `location_all` l , `site_all` s JOIN db2.user e 
        where l.location_id = u.location and s.site_id = u.site and e.user_id = u.user_id";
    
        if (!empty($_GET['Location']) && isset($_GET['Location'])) {
    
            $sql = $sql . " AND location =" . $_GET['Location'];
    
        }
        $strtdate = $_GET['Sday'];
        $enddate  = $_GET['Eday'];
        if (!empty($_GET['Sday']) && isset($_GET['Sday']) && !empty($_GET['Eday']) && isset($_GET['Eday'])) {
            $sql = $sql . " AND date between '" . $strtdate . "' and '" . $enddate . "'";
        } elseif (!empty($_GET['Sday']) && isset($_GET['Sday'])) {
            $sql = $sql . " AND date>='" . $strtdate . "'";
        } elseif (!empty($_GET['Eday']) && isset($_GET['Eday'])) {
            $sql = $sql . " AND date<='" . $enddate . "'";
        }
        if (!empty($_GET['Site']) && isset($_GET['Site'])) {
            $sql = $sql . " AND u.site=" . $_GET['Site'];
        }
    
        $data_query = $sql . " LIMIT $start_from, $per_page";
    
        $result = mysqli_query($conn, $data_query);
        if (mysqli_num_rows($result) >= 1) {
            $rowcount = mysqli_num_rows($result);
            echo '<legend> ' . $rowcount . ' Records Found !!!</legend>';
            echo '<br><br>';
            echo "<table class='srchtable'>
            <tr>
            <th>Picture</th>
            <th>Date</th>
            <th>User Name</th>
            <th>country</th>
            <th>Location</th>
            <th>Site</th>
            <th>Comment</th>
            </tr>";
            while ($row = mysqli_fetch_array($result)) {
                echo "<tr>";
                echo "<td> <img src='" . $row['picture'] . "' alt='' style='width:70%; height:auto; border-radius: 50%;'> </td>";
                echo "<td>" . $row['date'] . "</td>";
                echo "<td>" . $row['user_name'] . "</td>";
                echo "<td>" . $row['country'] . "</td>";
                echo "<td>" . $row['location'] . "</td>";
                echo "<td>" . $row['site'] . "</td>";
                echo "<td>" . $row['comment'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
    
            $query_result = mysqli_query($conn, $sql);
            $total_rows = mysqli_num_rows($query_result);
            $total_pages = ceil($total_rows / $per_page);
    
            parse_str($_SERVER["QUERY_STRING"], $url_array);
            unset($url_array['page']);
            $url = http_build_query($url_array);
            ?>
                <center><a href="?page=1<?php echo isset($url) && !empty($url) ? "&" . $url : ""; ?>">First Page</a> 
            <?php
            for ($i = 1; $i <= $total_pages; $i++) {
                ?>
                <a href="?page=<?php echo $i; echo isset($url) && !empty($url) ? "&" . $url : ""; ?>"><?php echo $i; ?></a> 
                <?php
            }
            ?>
            <a href="?page=<?php echo $total_pages; echo isset($url) && !empty($url) ? "&" . $url : ""; ?>">Last Page</a></center> 
            <?php
        } else {
            echo '<p>No Results Found !!!</p>';
        }
    }
    
    点赞 评论 复制链接分享

相关推荐