如何通过分页从我的mysql数据库表中只显示每页5条记录？ [英] How to display only 5 records per page from my mysql database table through pagination?

问题描述

我想通过分页显示每页五条记录（mysql，php，html，css），直到显示所有记录，导航到页面必须是 页面：1 2 3 4 5 6 7 7 8 ... Last 。

这是我的代码查看emp_master表中的所有记录。
我是PHP新手，所以请写一个易于理解的分页代码。

 <？php 
 $ con = mysqli_connect（localhost ， 用户， 密码， 的DataPlus）; 
 //检查连接
 if（mysqli_connect_errno（））
 {
 echo无法连接到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 ++; 
回显< tr>; 
 foreach（$ row as $ key => $ value）{
 echoth 。 $键。 < /第> 中; 
} 
 echo< / tr>; 
} 
 echo< tr>; 
 foreach（$ row为$ value）{
 echo< td> 。 $值。 < / TD> 中; 
} 
 echo< / tr>; 
} 
 echo< / table>; 
 
 mysqli_close（$ con）; 
  

我想通过分页显示每个页面的五条记录，直到显示所有记录，必须导航到页面以下代码无效：

  $ 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'）; 
 $ b $ / *获取记录总数* / 
 $ 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 =选择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））{
 echoEMP 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 \>最后10条记录< / 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）; 
  

解决方案

下面

  $ conn = mysqli_connect（localhost，root，，ui）; 
 
 
 $ start = 0; 
 $ limit = 5; 
 
 $ t = mysqli_query（$ conn，select * from form_table）; 
 $ total = mysqli_num_rows（$ t）; 
 
 
 $ b $ 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 =viewportcontent =width = device-width，initial-scale = 1> 
< link rel =stylesheethref =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 src =http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js> < /脚本> 
< / head> 
< body> 
 
< div class =container> 
< h2>表< / h2> 
< table class =table table-bordered> 
< thead> 
< tr> 
< th> Id< / th> 
< th>名称< / th> 
< th>性别< /> 
 
 
< th>爱好< / th> 
< th>课程< / 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）？>>上一个<？php}？> （$ i = 1; $ i <= $ page; $ i ++）{
？> 
<？php 
 
< li>< a href =？id =<？php echo $ i？>><？php echo $ i;？>< / a>< / li> ; 
<？php 
} 
？> 
<？php if（$ id！= $ page）
 
 {？> 
  

I want to display five record per page through pagination (mysql,php,html,css) until all the records are displayed, navigation to pages must be like, Page: 1 2 3 4 5 6 7 7 8... Last.

HERE IS MY CODE TO VIEW ALL THE RECORDS FROM emp_master table. I am new to PHP so please write an easily understandable code for pagination. I have seen few examples but they are not working.

<?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);

I want to display five record per page through pagination untill all the records are displayed, navigation to pages must be like, Page: 1 2 3 4 5 6 7 7 8... Last.

This code below is not working:

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

解决方案

IF your are using mysqli the code is below

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

   {?> 

这篇关于如何通过分页从我的mysql数据库表中只显示每页5条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！