[SOLVED] A little different take on the pagination problem

[Daen]

Hey guys, 

I'm the new web master for a forum site, and have had a new feature requested:  Some of the forum members want to be able to see all of a member's posts, ordered by when posted.  Here's the issue:  Some people as you can imagine have several hundred posts (or more), and that's just not doable to put them on one page. 

There is one table that holds all the members' posts, has a thread id and a message id, as well as the poster's user id.  I can select out just the posts by a user just fine, but I need a way to be able to break them up into pages.  The problem comes up because you could have other users post in between another's two posts, and it throws the counting out of whack.  Let me see if I can give an example to better illustrate:

User A posts something on thread 1.  It's inserted into the messages table with message id 1
User B then posts something on thread 1, it's inserted into the messages table with id 2
User A posts something again on thread 1, inserted into messages table with id 3
User C posts something on thread 2, inserted into messages table with id 4
User B posts something on thread 2, inserted into messages table with id 5
User A posts something on thread 3, inserted into messages table with id 6.

So, I need to pull out all of user A's posts and be able to paginate them.  Now say that users A, B, and C have roughly 500 posts each, with the message id numbers all interspersed like that.  :-\

Unfortunately, all the tutorials I've been able to find out on the 'net (including the ones here at phpfreaks) deal with the concept that all the stuff you want to paginate is in a nice little order that's easy to count.  You just get 1-10, say, then page number * 10 to that + 10, and so on.  Unless I'm missing something, I don't see how this could work for my situation.

Has anyone else run into a similar situation and/or have any ideas?

[bljepp69]

A properly formatted SQL query will pull all of the data and order it the way you want.  Something like:

[code]SELECT * FROM posts WHERE userid='$userID' ORDER BY messageid ASC LIMIT $start,$per_page[/code]

That should select only the posts by a given user, order them in ascending order of when they were put into the db, and then only pull the amount of posts you want for that page.

[Daen]

Well, that's what I'm doing for the first page, and it works just great.  The problem comes up when I need to try and calculate what the $start is going to be.  I can do a next page link just fine, but when trying to calculate a previous it causes problems.  I also want to be able to have a string of links that shows something like "start prev 1 2 3 4 next end".  Since the numbers aren't all consecutive, it's not as simple as most of the examples I've found.

(Oh, and I forgot to mention, I'm running MySQL 4.0.14, so I can't use subqueries-- which would have solved my problem as well... *sigh*)

Any ideas?

[craygo]

here is a sample. Change the query and tables to reflect your mysql table.
[code]<?php
// Connect to mysql below


// Set how many rows to display on each page
$limit = 200;
// Query the database to get the total number of rows
$query_count = "SELECT * FROM tablename";
  $result_count = mysql_query($query_count) or die (mysql_error());
    $totalrows = mysql_num_rows($result_count);
    if(isset($_GET['page'])){    // Checks if the $page variable is empty (not set)
        $page = $_GET['page'];      // If it is empty, we're on page 1
    } else {
        $page = 1;
    }
// Set the start value
    $startvalue = $page * $limit - ($limit);
// Query the database and set the start row and limit
$sql = "SELECT * FROM tablename LIMIT $startvalue, $limit";
  $res = mysql_query($sql) or die (mysql_error());
    echo "<table border=1 width=450 align=center>
            <tr>
              <td width=100>field1</td>
              <td width=175>Field2</td>
              <td width=175>Field3</td>
            </tr>";
// Do a quick check to see if there are any records to display
    if(mysql_num_rows($res) == 0){
        echo "<tr>
                <td colspan=3 align=center>No Records found!!</td>
              </tr>";
    }
// Start loop through records
while ($r = mysql_fetch_array($res)){
// Echo out the records
echo "<tr>\n";
echo "<td width=100>" . wordwrap($r['field1'], 2, " ", 1)."</td>\n";
echo "<td width=175>" . wordwrap ($r['field2'], 10, " ", 1)."</td>\n";
echo "<td width=175>" . wordwrap($r['field3'], 10, " ", 1)."</td>\n";
echo "</tr>\n";
}
// Close the table
echo "</table>";
// Start links for pages
echo "<p align=center>";

// Sets link for previous 25 and return to page 1
    if($page != 1){
        $pageprev = ($page - 1);
        echo "<a href=\"".$_SERVER['PHP_SELF']."?page=1\"><<</a>&nbsp;&nbsp;";
        echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$pageprev\">PREV&nbsp;</a> ";
    }else{
        echo "PREV&nbsp;";
    }
// Find out the total number of pages depending on the limit set
    $numofpages = $totalrows / $limit;
    $totalpages = round($numofpages);
// Loop thru all the pages and echo out the links
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $page){
            echo "[".$i."] ";
        }else{
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
        }
    }

// Check for straglers after the limit blocks
    if(($totalrows % $limit) != 0){
        if($i == $page){
            echo "[".$i."] ";
        }else{
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
        }
    }
// Print out the Next 25 and Goto Last page links
    if(($totalrows - ($limit * $page)) > 0){
        $pagenext = $page++;
          echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$pagenext\">NEXT&nbsp;</a>&nbsp;&nbsp;";
          echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$totalpages\">>></a>&nbsp;&nbsp;";
    }else{
        echo("NEXT");
    }
echo "</p>";
// Free results
mysql_free_result($res);
// Close mysql connection
mysql_close($mysql_conn);
?>[/code]

Ray

[Daen]

Ok, I've talked to a friend and he explained the LIMIT part of the statement a little more-- I had misunderstood it.  Both of your ideas will work then.  Thanks for the help, I appreciate it!