duananyu9231
2017-03-24 12:26
浏览 1.1k
已采纳

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

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);
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

6条回答 默认 最新

  • dpaal28266 2017-04-10 12:08
    已采纳

    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)
    
       {?> 
    
    点赞 打赏 评论
  • drqyxkzbs21968684 2017-03-24 12:57

    You can use a library called dataTables that will auto paginate the data and you can easily customize it to suit your needs with the look and how many records to show per page. It also has a search field which enables users to search your data without any extra code.

    All you have to do is include a couple of cdn's and the following code and everything works fine

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

    Some examples here

    点赞 打赏 评论
  • dongshan1959 2017-03-26 10:57

    If you want just PHP MySQL code, I usually use something like the following.

    $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
    
    点赞 打赏 评论
  • douping1581 2017-03-28 06:42

    you just should remove spaces in href and also make $page = $page + 1 when page is 0

    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>";
     }
    
    点赞 打赏 评论
  • dongyun3897 2017-03-28 11:49

    Link is here for demo, click here to experience the result

    Here is the code working for me, just change your db name, username and password and get pagination done. You can change $rec_limit value to your desired no. of records per page.

    <?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?>
    
    点赞 打赏 评论
  • doumei9589 2017-04-10 12:32
    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");
            }
    
    点赞 打赏 评论

相关推荐 更多相似问题