www.webdeveloper.com
Results 1 to 3 of 3

Thread: paginization of mysql data

  1. #1
    Join Date
    Jan 2010
    Location
    IN
    Posts
    29

    paginization of mysql data

    hello forum,
    I m having problem in limiting the no. of mysql data displayed on a single page.May anyone plz tell me the effidient way to do that.
    Thanks

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,518
    PHP Code:
    $numRows 20;
    $offset = ($pageNbr 1) * $numRows;

    $sql "SELECT . . . LIMIT $offset$numRows";
    /*** OR ***/
    $sql "SELECT . . . LIMIT $numRows OFFSET $offset"
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Mar 2010
    Posts
    2,803
    maybe use this pagination script as a guide.

    I have included an sql script to create test data if you want to see how the pagination works.

    All you need to do is change the database connection setting at the top to suit your database.

    PHP Code:
    <?php
    session_start
    ();
    //----------------------------------------------------------------------------------------------------------------------
    //connect to the database
    $DBUserName "root";     //database user account name
    $DBPassword "";     //database user account password   
    $DBName  "test";   //name of database 
               
    @$conn    mysql_connect("localhost",$DBUserName,$DBPassword) or die('<br />1-Cannot connect to the database at the moment.<br /><br />Please try again later.<br />');  //connect to mysql  
    @$isDbSelected  mysql_select_db($DBName$conn) or die('<br />1-Cannot connect to the database at the moment.<br /><br />Please try again later.<br />');        //connect to the db
    //----------------------------------------------------------------------------------------------------------------------
     
    $linesPerPage 4//number of lines to print per page 
     
    //check if a page link was clicked
    if(isset($_GET['txtPgNum'])) 
    {
        
    $_SESSION['currPage'] = $_GET['txtPgNum']; //txtPgNum comes from the page links
        
    unset($_GET['txtPgNum']);


    //check if either 'previous' or 'next' buttons were clicked
    else if(isset($_GET['dir']))
    {
        
    $direction $_GET['dir'];  // -1 = prev page   1 = next page 
     
    unset($_GET['dir']);  
     
    $_SESSION['currPage'] = $_SESSION['currPage'] + $direction;
        if(
    $_SESSION['currPage'] < 1$_SESSION['currPage'] = 1;
        if(
    $_SESSION['currPage'] > $_SESSION['totPages']) $_SESSION['currPage'] = $_SESSION['totPages'];   
    }
    else 
    //neither a page link or previous or next buttons were clicked.  This must then be the first call to this page
    {
        
    $_SESSION['currPage']  = 1//set current page = 1
        
        //select all the records to work out max number of rows and number of pages needed 
        
    $query   'select * from tblperson';
        
    $rs   mysql_query($query,$conn) or die("<p>3-Server is busy.<br />Please try again later.</p>"); 
        
    $totRows mysql_num_rows($rs); //total number of rows to display
        
    if($totRows%$linesPerPage == 0)
         
    $totPages $totRows/$linesPerPage;
        else
         
    $totPages round(($totRows/$linesPerPage)+0.5);  //total number of pages required
                
        
    mysql_free_result($rs); 
      
     
    $_SESSION['totRows']  = $totRows;
     
    $_SESSION['totPages'] = $totPages
    }   
     
    //calculate the first record number to retrieve from the DB for this page
    $_SESSION['offset'] = ($_SESSION['currPage']*$linesPerPage)-$linesPerPage;  
    if(
    $_SESSION['offset'] < 0)   $_SESSION['offset'] = 0;
    if(
    $_SESSION['offset'] > $_SESSION['totRows'])  $_SESSION['offset'] = $_SESSION['totRows'];
     
    //------------------------------------------------------------
    //Code to retrieve the rows to display on the current page
    //------------------------------------------------------------

    $query  'select * from tblperson limit '.$_SESSION['offset'].','.$linesPerPage;
    $rs  mysql_query($query,$conn) or die("<p>3-Server is busy.<br />Please try again later.</p>");
    ?>
    <html>
    <head>
    <title>Pagination</title>
    <style type="text/css">
    <!--
    body {
    font-size: 10pt;
    font-family: tahoma, arial, sans serif}
    #page-links-container {
    margin: 0px 0px 0px 0px;
    padding: 0px 0px 0px 0px}
    #page-links-container ul {
    list-style-type: none}
    #page-links-container ul li {
    display: inline;
    color: rgb(0,0,205);
    padding: 3px 4px 3px 4px;
    margin: 0px 0px 0px 6px}
    #page-links-container ul li a {
    text-decoration: none;
    font-size: 10pt}
    #page-links-container ul li a:hover {
    text-decoration: underline}
    #page-links-container ul li a:visited {
    color: rgb(0,0,205);}
    -->
    </style>

    </head>
    <body>
    <!-- Start of page links container -->
    <div id="page-links-container">
     <ul id="page-links">
         <li id="liPrevPage"><a href="pagination.php?dir=-1" title="Click to view previous page">Previous</a></li>
      <?php
          
    for($i=1$i<=$_SESSION['totPages']; $i=$i+1) {
                    echo 
    '<li id="liPg'.$i.'"><a href="pagination.php?txtPgNum='.$i.'" title="Go to page '.$i.'">'.$i.'</a></li>';
          }
      
    ?>
         <li id="liNextPage"><a href="pagination.php?dir=1" title="Click to view next page">Next</a></li>
        </ul>
    </div>  
    <!-- end of page-links-container div -->
    <!-- Code block to display the DB data from row $_SESSION['offset'] to row $_SESSION['offset']+$linesPerPage -->
    <table>
    <?php
     
    while($row=mysql_fetch_assoc($rs))
        {
      echo 
    '<tr><td>'.$row['fldPersonID'].'</td><td>'.$row['fldGivenName'].'</td><td>'.$row['fldFamilyName'].'</td></tr>';
        }
        
    mysql_free_result($rs);
        
    mysql_close($conn);
    ?>
    </table>
    <!-- End of code block to display the DB data from row $_SESSION['offset'] to row $_SESSION['offset']+$linesPerPage -->
    <?php
    //hide or display the 'previous' and 'next' buttons as required
    if($_SESSION['totPages'] == 1)
     echo 
    '<script type="text/javascript">document.getElementById("page-links-container").style.display="none";</script>';
    if(
    $_SESSION['currPage'] == 1)
     echo 
    '<script type="text/javascript">document.getElementById("liPrevPage").style.visibility="hidden";</script>';
    else
     echo 
    '<script type="text/javascript">document.getElementById("liPrevPage").style.visibility="visible";</script>';
        
    if(
    $_SESSION['currPage'] == $_SESSION['totPages'])
     echo 
    '<script type="text/javascript">document.getElementById("liNextPage").style.visibility="hidden";</script>';
    else
     echo 
    '<script type="text/javascript">document.getElementById("liNextPage").style.visibility="visible";</script>';
        
    //highlight the current page's page link
    echo '<script type="text/javascript">document.getElementById("liPg'.$_SESSION['currPage'].'").style.backgroundColor="rgb(200,200,200)";</script>';
    echo 
    '<script type="text/javascript">document.getElementById("liPg'.$_SESSION['currPage'].'").style.border="1px solid rgb(0,0,0)";</script>';
    ?>
    </body>
    </html>
    and the sql code

    Code:
     
    CREATE TABLE `tblperson` (
      `fldPersonID` int(11) NOT NULL AUTO_INCREMENT,
      `fldFamilyName` varchar(20) DEFAULT NULL,
      `fldGivenName` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`fldPersonID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
    /*Data for the table `tblperson` */
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (1,'Sui','Steven');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (2,'Student','Mary');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (3,'Student','Sam');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (4,'Malik','Moore');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (5,'Soo','Malinda');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (6,'flinstone','fred');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (7,'rubble','barney');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (8,'flinstone','wilma');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (9,'flinstone','pebbles');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (10,'smart','maxwell');
    insert  into `tblperson`(`fldPersonID`,`fldFamilyName`,`fldGivenName`) values (11,'Soo','Malinda');

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles