如何通过分页从我的mysql数据库表中每页仅显示5条记录？

Question

如何通过分页从我的mysql数据库表中每页仅显示5条记录？

3

我希望通过分页（mysql、php、html、css）每页显示五条记录，直到所有记录都被显示出来。页面导航应该是这样的：第1页 2 3 4 5 6 7 8... 最后一页。以下是查看emp_master表中所有记录的代码。我是PHP新手，请编写易于理解的分页代码。我已经看过一些例子，但它们不起作用。

<?php
$con=mysqli_connect("localhost","user","password","dataplus");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM emp_master");

echo "<table border='1'>";

$i = 0;
while($row = $result->fetch_assoc())
{
if ($i == 0) {
  $i++;
  echo "<tr>";
  foreach ($row as $key => $value) {
    echo "<th>" . $key . "</th>";
  }
  echo "</tr>";
 }
 echo "<tr>";
 foreach ($row as $value) {
  echo "<td>" . $value . "</td>";
 }
 echo "</tr>";
 }
 echo "</table>";

  mysqli_close($con);

我希望能够通过分页每页显示五条记录，直到所有记录都被显示出来。页面导航应该是这样的：“第1页 2 3 4 5 6 7 8... 最后一页”。请注意，不要删除HTML标签。

下面的代码无法工作：

     $dbhost="localhost";
     $dbuser="10053"; 
     $dbpass="n6867242"; 
 $database="0368";

     $rec_limit = 10;
     $conn = mysql_connect($dbhost, $dbuser, $dbpass);

     if(! $conn ) {
        die('Could not connect: ' . mysql_error());
     }
     mysql_select_db('1005368');

     /* Get total number of records */
     $sql = "SELECT count(emp_id) FROM emp_master ";
     $retval = mysql_query( $sql, $conn );

     if(! $retval ) {
        die('Could not get data: ' . mysql_error());
     }
     $row = mysql_fetch_array($retval, MYSQL_NUM );
     $rec_count = $row[0];

     if( isset($_GET{'page'} ) ) {
        $page = $_GET{'page'} + 1;
        $offset = $rec_limit * $page ;
     }else {
        $page = 0;
        $offset = 0;
     }

     $left_rec = $rec_count - ($page * $rec_limit);
     $sql = "SELECT emp_id, emp_name, e_mail ". 
        "FROM emp_master ".
        "LIMIT $offset, $rec_limit";

     $retval = mysql_query( $sql, $conn );

     if(! $retval ) {
        die('Could not get data: ' . mysql_error());
     }

     while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
        echo "EMP ID :{$row['emp_id']}  <br> ".
           "EMP NAME : {$row['emp_name']} <br> ".
           "EMP MAIL : {$row['e_mail']} <br> ".
           "--------------------------------<br>";
     }

     if( $page > 0 ) {
        $last = $page - 2;
        echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Records</a> |";
        echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Records</a>";
     }else if( $page == 0 ) {
        echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Records</a>";
     }else if( $left_rec < $rec_limit ) {
        $last = $page - 2;
        echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Records</a>";
     }

     mysql_close($conn);

- Mathew Coder

你刚刚发布了你的数据库用户名和密码！！而且它们还在你在评论中链接的文本文件中可用！ - Alireza

谢谢您的评论，阿里莫扎。那些不正确，我只是发布了随机用户名、密码和数据库名称。 - Mathew Coder

6个回答

0

你只需要删除 href 中的空格，并且在页面为0时使 $page = $page + 1

if( $page > 0 ) {
    $last = $page - 2;
    echo "<a href = \"$_PHP_SELF?page=$last\">Last 10 Records</a> |";
    echo "<a href = \"$_PHP_SELF?page=$page\">Next 10 Records</a>";
 }else if( $page == 0 ) {
    $page = $page + 1;
    echo "<a href = \"$_PHP_SELF?page=$page\">Next 10 Records</a>";
 }else if( $left_rec < $rec_limit ) {
    $last = $page - 2;
    echo "<a href = \"$_PHP_SELF?page=$last\">Last 10 Records</a>";
 }

- Alireza

0

这里有一个演示链接，点击体验一下吧

这是我运行成功的代码，只需更改您的数据库名称、用户名和密码，并进行分页处理。您可以将 $rec_limit 值更改为每页所需记录数。

<?php 
  $host="localhost";
  $username="68"; 
  $password="67242"; 
  $database="68";

   $rec_limit = 5;
    $conn = mysql_connect($localhost,$username,$password);

    if(! $conn ) {
    die('Could not connect: ' . mysql_error());
 }
 mysql_select_db('10');

 /* Get total number of records */
 $sql = "SELECT count(emp_id) FROM emp_master ";
 $retval = mysql_query( $sql, $conn );

 if(! $retval ) {
    die('Could not get data: ' . mysql_error());
 }
 $row = mysql_fetch_array($retval, MYSQL_NUM );
 $rec_count = $row[0];

 if( isset($_GET{'page'} ) ) {
    $page = $_GET{'page'} + 1;
    $offset = $rec_limit * $page ;
 }else {
    $page = 0;
    $offset = 0;
 }

 $left_rec = $rec_count - ($page * $rec_limit);
 $sql = "SELECT eid,ename, email, quali, gender, contactno, birthdate, joiningdate,CURDATE(), TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) ) AS age ". 
    "FROM emp_master ".
    "LIMIT $offset, $rec_limit";

 $retval = mysql_query( $sql, $conn );

 if(! $retval ) {
    die('Could not get data: ' . mysql_error());
 }


  echo "<table border='1'>";
  $i = 0;
  while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {

   if ($i == 0) {
        $i++;
        echo "<tr>";
        foreach ($row as $key => $value) {
          echo "<th>" . $key . "</th>";
        }
        echo "</tr>";
        }
        echo "<tr>";
        foreach ($row as $value) {
        echo "<td>" . $value . "</td>";
       }
      echo "</tr>";

     }
    echo "</table>";

if( $page > 0 ) {
$last = $page - 2;
echo "<a href = \"$_PHP_SELF?page=$last\">Last 5 Records</a> | ";
echo "<a href = \"$_PHP_SELF?page=$page\">Next 5 Records</a>";

}else if( $page == 0 ) {
$page = $page + 0;
echo "<a href = \"$_PHP_SELF?page=$page\">Next 5 Records</a>";

}else if( $left_rec < $rec_limit ) {

$last = $page - 2;
echo "<a href = \"$_PHP_SELF?page=$last\">Last 5 Records</a>";

}

     mysql_close($conn);
php?>

- Mathew Coder

演示链接失效。 - Yunfei Chen

0

如果你正在使用mysqli，代码如下所示

 $conn=mysqli_connect("localhost","root","","ui");


 $start=0;
  $limit=5;

  $t=mysqli_query($conn,"select * from form_table");
  $total=mysqli_num_rows($t);



   if(isset($_GET['id']))
   {
        $id=$_GET['id'] ; 
                        $start=($id-1)*$limit;

                          }
            else
            {
        $id=1;
   }
   $page=ceil($total/$limit);

   $query=mysqli_query($conn,"select * from form_table limit                                        $start, $limit");
 ?>
 <!DOCTYPE html>
 <html>
 <head>
 <meta name="viewport" content="width=device-width, initial-scale=1">
 <link rel="stylesheet"           href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
 <script s             src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js">           </script>
     <script                           src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js">                 </script>
     </head>         
 <body>

<div class="container">
 <h2>Table</h2>
    <table class="table table-bordered">
    <thead>
      <tr>
       <th>Id</th>
         <th>Name</th>
       <th>Gender</th>


<th>Hobbies</th>
<th>Course</th>
 </tr>
</thead>
<tbody>

<?php
  while($ft=mysqli_fetch_array($query))
 {?>
 <tr>
<td><?= $ft['0']?></td>
<td><?= $ft['1']?></td>
<td><?= $ft['2']?></td>
<td><?= $ft['3']?></td>
<td><?= $ft['4']?></td>
  </tr>   
<?php
 }

?>


 </tbody>
  </table>
 <ul class="pagination">
  <?php if($id > 1) {?> <li><a href="?id=<?php echo ($id-1) ?       >">Previous</a></li><?php }?>
  <?php
  for($i=1;$i <= $page;$i++){
   ?>
  <li><a href="?id=<?php echo $i ?>"><?php echo $i;?></a></li>
   <?php
   }
  ?>
   <?php if($id!=$page)

   {?>

- Mathew Coder

0

i Give you an example of my project of pagination. Just you have to put your values in the code

$sql="SELECT * FROM tblname  LIMIT  $next,5";
$results = mysqli_query($conn,$sql);
    }
        else if (isset($_POST['prev']))
        {
            $prev1=$_POST['prev'];
            $next=$_POST['prev'];
            $prev=$prev1;
            $sql="SELECT * FROM tablename  LIMIT  $prev,5";
            $prev=$prev1;
            $results = mysqli_query($conn,"SELECT * FROM tablename LIMIT  $prev,10");   
                if($prev==0)
                {
                    $prev = 0;
                }
                else
                {
                    $prev=$next-10;   
                }
        }
        else
        {
            $next=0;
            $prev=0;
            $results = mysqli_query($conn,"SELECT * FROM tablename LIMIT  $next,10");
        }

- Hacker

0

你可以使用一个叫做dataTables的库，它会自动分页数据，并且你可以轻松地自定义它的外观和每页显示多少条记录来满足你的需求。它还有一个搜索框，使用户可以在不需要额外代码的情况下搜索你的数据。

你只需要包含一些CDN和以下代码，一切都能正常工作。

$(document).ready(function(){
    $('#myTable').DataTable();
});

这里有一些例子在这里

- Douglas Hosea

我是一个初学者，如果你真的知道或想帮忙，请逐步解释。 - Mathew Coder

请问您能否给我一个链接，以便查看如何包含这些CDN以及在哪里包含此脚本（$（document）.ready（function（）{ $('#myTable').DataTable(); }）;）。 - Mathew Coder

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jonats · Accepted Answer

如果你只想要PHP MySQL代码，我通常使用以下类似的代码。

$page=max(intval($_GET['page']),1); // assuming there is a parameter 'page'
$itemsperpage = 5;
$total=100; // total results if you know it already otherwise use another query
$totalpages = max(ceil($total/$itemsperpage),1);
$query = "SELECT * FROM emp_master LIMIT ".(($page-1)*$itemsperpage).",".$itemsperpage; // this will return 5 items based on the page