SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Pagination with PHP

    ok I've got this database table named data containing six rows!
    what i want paginate the data & display only two columns from the table! I'm giving my code, it displays the table Header row only with no data inside.

    Code PHP:
    <?php
     
    $connection = mysql_connect("localhost","root","");
    if(!$connection){
    ('Could not connect: ' . mysql_error());
    }
    mysql_select_db("mydata",$connection);
     
     
    if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
    $start_from = ($page-1) * 5; 
    $sql = "SELECT * FROM data ORDER BY roll ASC LIMIT $start_from, 5"; 
    $rs_result = mysql_query ($sql,$connection); 
    ?> 
    <table border=2 bgcolor="lightblue" font-family="tahoma">
    <tr><td>year</td><td>roll</td></tr>
    <?php 
    while ($row = mysql_fetch_assoc($rs_result)) { 
    ?> 
                <tr>
                <td><? echo $row["year"]; ?></td>
                <td><? echo $row["roll"]; ?></td>
                </tr>
    <?php 
    }; 
    ?> 
    </table>
    <?php 
    $sql = "SELECT COUNT(roll) FROM data"; 
    $rs_result = mysql_query($sql,$connection); 
    $row = mysql_fetch_row($rs_result); 
    $total_records = $row[0]; 
    $total_pages = ceil($total_records / 5); 
     
    for ($i=1; $i<=$total_pages; $i++) { 
                echo "<a href='index.php?page=".$i."'>".$i."</a> "; 
    }; 
    ?>

  2. #2
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Shropshire
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are on the first page then you are selecting zero results. Without $_GET['page'], you set $page to equal 1, then in your $start_from variable you are deducting 1 from the number stored in the $page variable and then multiplying by 5. so, on your first page $page = 1, you then deduct 1 which will give you $page = 0 and then multiply by 5 which will give you $start_from = 0 (5 * 0 = 0).

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chilli11 View Post
    If you are on the first page then you are selecting zero results. Without $_GET['page'], you set $page to equal 1, then in your $start_from variable you are deducting 1 from the number stored in the $page variable and then multiplying by 5. so, on your first page $page = 1, you then deduct 1 which will give you $page = 0 and then multiply by 5 which will give you $start_from = 0 (5 * 0 = 0).
    is this one alright?

    if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
    $start_from = ($page);
    $sql = "SELECT * FROM data ORDER BY roll ASC LIMIT $start_from, 1";
    $rs_result = mysql_query ($sql,$connection);

  4. #4
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Shropshire
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think so based on what you were trying to do. If you want 5 records per page:
    PHP Code:
    if (isset($_GET["page"])) { $page  $_GET["page"];
    $start_from = ($page 5) - 4;  
    }else { 
    $start_from=0
    }
    $sql "SELECT * FROM data ORDER BY roll ASC LIMIT $start_from, 5"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chilli11 View Post
    I don't think so based on what you were trying to do. If you want 5 records per page:
    PHP Code:
    if (isset($_GET["page"])) { $page  $_GET["page"];
    $start_from = ($page 5) - 4;  
    }else { 
    $start_from=0
    }
    $sql "SELECT * FROM data ORDER BY roll ASC LIMIT $start_from, 5"
    Did that! The table increases in size as if it shows 1 data, but actually is blank now! nothing shows up in that row! Code again!

    Code PHP:
    <?php
     
    $connection = mysql_connect("localhost","root","");
    if(!$connection){
    ('Could not connect: ' . mysql_error());
    }
    mysql_select_db("mydata",$connection);
     
     
     
     
    if (isset($_GET["page"])) { $page  = $_GET["page"];
    $start_from = ($page * 5) - 4;  
    }else { 
    $start_from=0; 
    }
    $sql = "SELECT * FROM data ORDER BY roll ASC LIMIT $start_from, 5";  
    $rs_result = mysql_query ($sql,$connection); 
    ?> 
    <table border=2 bgcolor="lightblue" font-family="tahoma">
    <tr><td>year</td><td>roll</td></tr>
    <?php 
    while ($row = mysql_fetch_assoc($rs_result)) { 
    ?> 
                <tr>
                <td><? echo $row["year"]; ?></td>
                <td><? echo $row["roll"]; ?></td>
                </tr>
    <?php 
    }; 
    ?> 
    </table>
    <?php 
    $sql = "SELECT COUNT(roll) FROM data"; 
    $rs_result = mysql_query($sql,$connection); 
    $row = mysql_fetch_row($rs_result); 
    $total_records = $row[0]; 
    $total_pages = ceil($total_records / 5); 
     
    for ($i=1; $i<=$total_pages; $i++) { 
                echo "<a href='index.php?page=".$i."'>".$i."</a> "; 
    }; 
    ?>

    Or if u have ur own code for PHP TABLE PAGINATION WITH MYSQL, please share!

  6. #6
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Shropshire
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry a little confused - are you saying the table has the right number of rows but no data? If that is a case, please use proper PHP opening tags:

    <?php

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chilli11 View Post
    Sorry a little confused - are you saying the table has the right number of rows but no data? If that is a case, please use proper PHP opening tags:

    <?php
    No, not the right number of rows, the table sizes increases just a little, as if just showing 1 data only!! Do u have any or know any links for any php pagination tutorial please?

  8. #8
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Shropshire
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I meant your database table, not the html table. What results (or errors) do you get if you run the query in phpMyAdmin?

    Sorry, don't know of any tutorials off the top of my head without searching but you could try the articles and tutorials on this website.


Bookmarks

Posting Permissions

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