Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jan 2006
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Pagination of MySQL records

    Attempting to set up pagination.

    Data is being passed to page and being displayed in a dynamic table (Adjusts based upon the number of records to display.).

    Table row colors alternate; gray - white - gray.

    In addition, the <thead> is set up to allow for ordering of records.

    Now I want to add pagination; limiting the number of displayed rows to 20.

    Needing a good tutorial.

    Here's what I have so far:

    Code:
    <?php
    session_start;
    error_reporting(E_ALL | 'E_STRICT');
    $db = mysql_connect("localhost", "root", "");
                mysql_select_db("newcourse",$db);
    $result = mysql_query("SELECT * FROM proposals ORDER by Records DESC",$db);
    //let's get the number of rows in our result so we can use it in a for loop
    $numofrows = mysql_num_rows($result);
    
    //Table formatting and sorting buttons
    echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>Courses you have submitted.</b>
    <TABLE BORDER=0 width='631' cellpadding=4 cellspacing=0 bgcolor='#FFFFFF'>
    <thead bgcolor='lightyellow'>
    <TR><TH><a title=\"Organize by course name\" href=\"\" onclick=\"this.blur(); return sortTable('offTblBdy', 0, true);\"><B>Course title</B></a></TH>
    <TH><a title=\"Organize by course number\" href=\"\" onclick=\"this.blur(); return sortTable('offTblBdy', 1, true);\"><B>Course number</B></a></TH><TH></TH></TR>
    </thead>
    <TFOOT>
    <TR bgcolor='lightyellow'>
    <Td><img src='spacer.jpg'></Td>
    <Td><img src='spacer.jpg'></Td>
    <Td><img src='spacer.jpg'></Td>
    </Tr>
    </TFOOT>";
    
    // coloring every other row
    echo "<tbody id=\"offTblBdy\">";
    for($c = 0; $c < $numofrows; $c++) {
    $myrow = mysql_fetch_array($result); //get a row from our result set
    if($c % 2) { 
    //this means if there is a remainder
    echo "<TR class=\"alternateRow\">";
    } else { 
    //if there isn't a remainder we will do the else
    echo "<TR>";
    }
    echo "<TD>".$myrow['CourseTitle']."</TD>
            <TD><center>".$myrow['CourseNumber']."</center></TD>";
    echo "<TD width='170'>
    <a title='Edit record' style='text-decoration: none' href=\"addedit.php?Records=".$myrow['Records']."\">Edit</a> |
    <a title='Create PDF' style='text-decoration: none' href=\"PDFgen.php?Records=".$myrow['Records']."\">PDF </a> |
    <a title='Submit for review' style='text-decoration: none' href=\"EmailComment.php?Records=".$myrow['Records']."\">Submit </a> |
    <a title='Delete record' style='text-decoration: none' href=\"delete.php?Records=".$myrow['Records']."\"> Delete</a>";
    //<a onmousedown='textSWAP()' id='textSubmitted' style='text-decoration: none' href=\"javascript: textSWAP('textSubmitted','Submitted');\">Submit </a> |
    echo "</TD>";
    echo "</TR>";
    }
    echo "</tbody>";
    //now let's close the table and be done with it
    echo "</TABLE>\n";
    // Close DB connection
    mysql_close($db);
    $Binary="0";
    ?>

  • #2
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    Here's the base code of what you need:
    PHP Code:
    <?php
    session_start
    ;
    error_reporting(E_ALL 'E_STRICT');
    $db mysql_connect("localhost""root""");
                
    mysql_select_db("newcourse",$db);
                
    //Detect which row to Start on for mysql rows
                
    if(empty($_GET['start']) || $_GET['start'] == "0"){
                
    $limit "0,20"//no $_GET['start'] set, start at record 0
                
    }else{
                
    $limit $_GET['start'].",20"//$_GET['start'] is set, start at record $_GET['start']
                
    }
            
    $result mysql_query("SELECT * FROM proposals ORDER by Records DESC LIMIT $limit",$db);
    //let's get the number of rows in our result so we can use it in a for loop
    $numofrows mysql_num_rows($result);

    //Detect # of pages
    $pages ceil($numofrows 20);

    //Table formatting and sorting buttons
    echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>Courses you have submitted.</b>
    <TABLE BORDER=0 width='631' cellpadding=4 cellspacing=0 bgcolor='#FFFFFF'>
    <thead bgcolor='lightyellow'>
    <TR><TH><a title=\"Organize by course name\" href=\"\" onclick=\"this.blur(); return sortTable('offTblBdy', 0, true);\"><B>Course title</B></a></TH>
    <TH><a title=\"Organize by course number\" href=\"\" onclick=\"this.blur(); return sortTable('offTblBdy', 1, true);\"><B>Course number</B></a></TH><TH></TH></TR>
    </thead>
    <TFOOT>
    <TR bgcolor='lightyellow'>
    <Td><img src='spacer.jpg'></Td>
    <Td><img src='spacer.jpg'></Td>
    <Td><img src='spacer.jpg'></Td>
    </Tr>
    </TFOOT>"
    ;

    // coloring every other row
    echo "<tbody id=\"offTblBdy\">";
    for(
    $c 0$c $numofrows$c++) {
    $myrow mysql_fetch_array($result); //get a row from our result set
    if($c 2) { 
    //this means if there is a remainder
    echo "<TR class=\"alternateRow\">";
    } else { 
    //if there isn't a remainder we will do the else
    echo "<TR>";
    }
    echo 
    "<TD>".$myrow['CourseTitle']."</TD>
            <TD><center>"
    .$myrow['CourseNumber']."</center></TD>";
    echo 
    "<TD width='170'>
    <a title='Edit record' style='text-decoration: none' href=\"addedit.php?Records="
    .$myrow['Records']."\">Edit</a> |
    <a title='Create PDF' style='text-decoration: none' href=\"PDFgen.php?Records="
    .$myrow['Records']."\">PDF </a> |
    <a title='Submit for review' style='text-decoration: none' href=\"EmailComment.php?Records="
    .$myrow['Records']."\">Submit </a> |
    <a title='Delete record' style='text-decoration: none' href=\"delete.php?Records="
    .$myrow['Records']."\"> Delete</a>";
    //<a onmousedown='textSWAP()' id='textSubmitted' style='text-decoration: none' href=\"javascript: textSWAP('textSubmitted','Submitted');\">Submit </a> |
    echo "</TD>";
    echo 
    "</TR>";
    }
    echo 
    "</tbody>";
    //now let's close the table and be done with it
    echo "</TABLE>\n";
    // Close DB connection
    mysql_close($db);
    $Binary="0";
    ?>
    BUT.... Add this wherever you want the Pages to show up:
    If you want the pages as a dropdown:
    PHP Code:
    <form name='pageselector' action='<?=$_SERVER['PHP_SELF'];?>' method='get'>Page: 
    <select name='start' id='start' onChange='this.form.submit();'>
    <?php
    for($i=0;$i<$pages;$i++){
        
    $num $i 20;
        echo 
    "<option value='$num'>$i</option>\n";
    }
    ?>
    </select>
    </form>
    If you want the pages as a link list:
    PHP Code:
    <?php
    for($i=0;$i<$pages;$i++){
        
    $num $i 20;
        echo 
    "<a href='{$_SERVER['PHP_SELF']}?start=$num'>$i</a>&nbsp;\n";
    }
    ?>
    EDITED 5:05 PM EST
    Last edited by boeing747fp; 05-31-2006 at 10:05 PM.

  • #3
    Regular Coder
    Join Date
    Jan 2006
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question

    boeing747fp,

    I appreciate your support, thank you for the tutoring on pagination.

    Couple of questions:
    When the page is initially opened, All available records are being presented; page links are shown too.
    Clicking on the page link reveals the expected number of records.

    EXAMPLE using 5 records and the display set to 3 records:
    Initial loading of page shows all five records. Clicking the link presents the next page displaying the last two records.

    1. I don't understand why the initial loading of the page isn't adhering to the three records per page maximum.

    I adjusted the code to the following. Now the initial loaded page does show the first three records but drop the link to the next page.
    Code:
    //Detect which row to Start on for mysql rows
    if(empty($_GET['start']) || $_GET['start'] == "0"){
    $limit = "0,3"; //now $_GET['start'] set, start at record 0
    }else{
    $limit = $_GET['start'].",6"; //$_GET['start'] is set, start at record $_GET['start']
    }
    $result = mysql_query("SELECT * FROM proposals ORDER by Records DESC LIMIT $limit",$db);
    //let's get the number of rows in our result so we can use it in a for loop
    $numofrows = mysql_num_rows($result);

    2. The rows have stopped highlighting.


  •  

    Posting Permissions

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