SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Paginating data

  1. #1
    SitePoint Member
    Join Date
    Aug 2001
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Paginating data

    I have a page which reads a db table client_list. It then formats a table row using info from each row in the table. This repeats down the page for every row in the table.

    As the table has grown obviously the page length is getting longer and longer. Can anybody point me to a method of breaking this page up (say 5 rows per page)into 5 pages if there are 25 records in table.

    I assume that it would depend on using mysql_num_rows then using the return from this somehow.This is probably a simple question - there might even be a simple answer or a well known tutorial on this.

    Any help/advice/pointres would be much appreciated. The present code is pasted below. Thanks


    // formulate the SQL query
    $query = "select * from client_list" or die("Error in query");

    // run the query on the database
    $result = mysql_db_query($db, $query, $connection) or
    die("Error in query");

    // display the result
    echo "<table width=570 border=0 cellspacing=0
    cellpadding=0>";



    // loop iterates as many times as there are records

    while($myrow = mysql_fetch_array($result))
    {
    $clientimage = $myrow["clientimage"];
    $clienttext = $myrow["clienttext"];
    $clienturl = $myrow["clienturl"];
    $clientname = $myrow["user"];

    echo "<tr><td width=200 align=center valign=top><image src='images/$clientimage'></td><td width=370
    align=left valign=top><font face='Georgia, Times New Roman, Times, serif' size='-1' color= 'navy'>
    <A HREF='$clienturl'><B>$clientname</B></a><BR><BR>
    $clienttext</font></td></tr><tr><td colspan=2><HR></td></tr>";

    }

    // memory flush
    mysql_free_result($result);

  2. #2
    SitePoint Enthusiast allusion's Avatar
    Join Date
    Aug 2001
    Location
    San Francisco, CA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, add this sort of statement to the end of your query:
    limit $offset,$max_hits

    $max_hits = how many results you want per page
    $offset = where you want the results to start

    Add this somewhere:
    $new_offset = $offset + 5 (or whatever # you want)

    Put a link at the bottom (or whereever) of the page with something like this:
    <a href="longpage.php?offset=$new_offset">Next 5 records</a>

    Hope that made sense.

  3. #3
    SitePoint Addict jough's Avatar
    Join Date
    Sep 2001
    Location
    You have moved into a dark place. It is pitch blac
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Allusion, that will work, but only for the next page. When they click on the "next" button again they'll get the same page, since you're not changing the offset in the limit.

    There are some tutorials on this subject, although I haven't found a good one yet.

    You just need to update the offset variable so that the new page is, well, new, when someone clicks on the link.

    And it may be helpful to offer a "previous" page button, too.

    -- Jough
    My Dynamic Site in Action:
    Poetry X Poetry Archive

  4. #4
    SitePoint Enthusiast allusion's Avatar
    Join Date
    Aug 2001
    Location
    San Francisco, CA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On the next page the $offset would be 5, and adding 5 to that would start the next page at 10...

    For a previous button the same principals could be used.

  5. #5
    SitePoint Addict jough's Avatar
    Join Date
    Sep 2001
    Location
    You have moved into a dark place. It is pitch blac
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, the offset doesn't change in your example, allusion. If you're limiting the query by the offset, but passing the variable $new_offset to the script to make a new page, then $offset will still be the same the next time the script runs.

    In other words:

    Code:
    $offset = 5;
    $new_offset = $offset + 5;
    echo $offset;
    would print "5" to the screen.

    So you'd be able to get to the next five rows by clicking on the "next page" link, but then when you got there the "next page" link would be the same thing - $offset + 5, and since offset is 5, and doesn't change, you'd be displaying the same page every time you pressed "next".

    Anyway, CFS, you'll need to change $offset for each page based on the $new_offset var. Maybe put this at the top of the page:

    Code:
    // New Offest is set, and you need to update the offset and the new offset
    if ($new_offset)
    {
         $offset = $new_offset;
    }
    // Otherwise, this is the first page and you need to initialise the vars
    else
    {
         $offset = 5;
    }
    
    // Either way, you need to update $new_offset to make the proper "next page" link
    
    $new_offset = $offset + 5;
    
    // And you can make an easy previous page var and use it the same way
    $previous_link = $offset - 5;
    The rest of allusion's example should work, then.

    -- Jough
    My Dynamic Site in Action:
    Poetry X Poetry Archive

  6. #6
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Um actually it would work...writing out his example, first his statement:
    Sure, add this sort of statement to the end of your query:
    limit $offset,$max_hits

    $max_hits = how many results you want per page
    $offset = where you want the results to start

    Add this somewhere:
    $new_offset = $offset + 5 (or whatever # you want)

    Put a link at the bottom (or whereever) of the page with something like this:
    <a href="longpage.php?offset=$new_offset">Next 5 records</a>
    Then taking that and writing it out:
    Code:
    $results = mysql_query("SELECT blah FROM blah LIMIT $offset,$max_hits");
    
    $new_offset = $offset + 5;
    
    echo "blah blah data blah";
    
    echo "<a href='longpage.php?offset=$new_offset'>Next 5 records</a>";
    That works perfectly for me...
    Kevin

  7. #7
    SitePoint Member
    Join Date
    Aug 2001
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again one and all for pointing me in the right direction - much appreciated. With your ideas/comments
    I managed to track down the following which I am posting here for others who might have the same problem I did.

    A Tutorial http://www.phpbuilder.com/columns/rod20000221.php3

    A code snippet
    http://www.phpbuilder.com/snippet/de...=snippet&id=21

    Many thanks

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tubedogg,

    I think what jough is saying is in allusion's code he assigns $offset a value near the top, if the page assigns $offset a value everytime the page loads it will be the same value and therefore the next results will never change.

    Allusion's code

    PHP Code:
    Sureadd this sort of statement to the end of your query
    limit $offset,$max_hits 

    $max_hits 
    how many results you want per page 
    $offset 
    where you want the results to start 

    Add this somewhere

    $new_offset $offset (or whatever # you want) 

    Put a link at the bottom (or whereeverof the page with something like this
    <
    a href="longpage.php?offset=$new_offset">Next 5 records</a
    Now let's give it a value which is what I assumed allusion was doing

    PHP Code:
    Sureadd this sort of statement to the end of your query
    limit $offset,$max_hits 

    $max_hits 
    5;
    $offset 0;

    Add this somewhere
    $new_offset $offset (or whatever # you want) 

    Put a link at the bottom (or whereeverof the page with something like this
    <
    a href="longpage.php?offset=$new_offset">Next 5 records</a

    So now with the code like that $offset will always get a value of 0 no matter what is passed in the query string and the user will see the the same records over and over again. That is what jough is saying, at least I think. You could fix it easily by using

    PHP Code:
    Sureadd this sort of statement to the end of your query
    limit $offset,$max_hits 

    $max_hits 
    5;
    $offset = ($offset) ? $offset 0;

    Add this somewhere
    $new_offset $offset (or whatever # you want) 

    Put a link at the bottom (or whereeverof the page with something like this
    <
    a href="longpage.php?offset=$new_offset">Next 5 records</a
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Addict jough's Avatar
    Join Date
    Sep 2001
    Location
    You have moved into a dark place. It is pitch blac
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, that's what I was saying, Freddydoesphp, and your solution is a lot easier than mine was.

    Tubedogg, I don't know how this code would work for you. I'll re-post exactly what you said "worked."

    $results = mysql_query("SELECT blah FROM blah LIMIT $offset,$max_hits");

    $new_offset = $offset + 5;

    echo "blah blah data blah";

    echo "<a href='longpage.php?offset=$new_offset'>Next 5 records</a>";
    .

    Not to be picky, but your query would fail. You forgot to set a value for both $offset and $max_hits, so your query string's limit would be "LIMIT , " which would create a mysql error.

    So I don't understand how that would work for you.

    Maybe you're updating the value of $offset on each page when you try it on your server, but that probably won't help CFS if you don't post your code.

    CFS - I think freedydoesphp's example will work fine for you.

    -- Jough
    My Dynamic Site in Action:
    Poetry X Poetry Archive

  10. #10
    SitePoint Enthusiast allusion's Avatar
    Join Date
    Aug 2001
    Location
    San Francisco, CA
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $db_name 
    "whatever";
    $connection mysql_connect("localhost""cliecent_matt""matt");
    $db mysql_select_db($db_name$connection); //everything normal stuff here
    $sql "SELECT * FROM news LIMIT $offset,5"//make 5 however many results you want displayed
    $result mysql_query($sql,$connection);
    while (
    $row mysql_fetch_array($result)) {
          
    $whatever $row['whatever']; //put your varibles you want assigned here
          
    $display_news .= "[insert html/varibles you want echoed (sp?) for each result]";
                    }
    $new_offset $offset // offset is currently zero, this adds 5 to it

    ?>
    [insert page stuff here]
    <?php echo "$display_news"?>
    [more page stuff]
    <?php echo "<a href=\"news.php?offset=$new_offset\">Next five news items</a>"?> // an offset of 5 is passed to the next page
    I'm not trying to insult anyone's intelligence by writing everything out, it's as much for me as anyone. Would this code work? Am I very confused?

    Ohhhh! Now i see my earlier mistake: i had $offset in the beginning. Oh well, I'll leave the above code just in case it helps anyone out.

  11. #11
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I took this
    Sure, add this sort of statement to the end of your query:
    limit $offset,$max_hits
    $max_hits = how many results you want per page
    $offset = where you want the results to start
    as explaining what the variables in the limit clause indicated. I didn't take it as him additionally setting those values right there. Yes, if you were to put this in a page exactly as it's written right there, it would not work. But I didn't think that was what he was saying.

    In addition, I was assuming that since he was getting results from a database, he would be passing the $offset and $max_hits from the previous (search?) page.
    Kevin

  12. #12
    SitePoint Addict jough's Avatar
    Join Date
    Sep 2001
    Location
    You have moved into a dark place. It is pitch blac
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured you'd assume that, tubedogg - I was just pointing out that a newbie (for a lack of better phrase) would have trouble following your example, I think. Maybe I underestimate newbies.

    -- Jough
    My Dynamic Site in Action:
    Poetry X Poetry Archive

  13. #13
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tubedogg

    $offset = where you want the results to start
    So even following your last post, you are still not showing them how to check for the existence of $offset and if it doesn't exist set it to 0 or else leave it alone. All you do is tell him to assign a value to $offset. So yes that would cause the same results to appear time after time since you would be reassigning the value of $offset each time the page loads. All we are saying is you should have pointed out how to check the $offset variable instead of just putting a comment next to a var name.

    In addition, I was assuming that since he was getting results from a database, he would be passing the $offset and $max_hits from the previous (search?) page.
    Additionally it still would work because in your example you reset $offset to 0 no matter what gets passed in so, the query would just produce the same set of records each time. Which I assume is what he was alluding to.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •