SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Catch 22 - Pagination

    I willing to pay someone who can help me with pagination of my CMS.

    I follow all the guidelines how to create CMS from Chapter-6 of "Build yoor own Database Driven Website" book.

    I've created the CMS and came to the point that I in need of pagination for it.

    I couldn't find any guidelines how to do pagination for that particular example of CMS for that chapter.

    For example: I have four tables: Joke, Author, Category, JokeCategory -> my search works fine unless I try to paginate my script.

    If Pagination works - Search doesn't. If to take the pagination out of the script - Search start working. Crazy!!!

    I've read many, many threats over the Internet and couldn't find any solution. Every single post and article that I've read says that it's very difficult to deal with "Where" - clause and paginate your results from database.

    Will Pay - not joking, just frustrated!

  2. #2
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    paste your script, pretty sure it will be fixed within 10 minutes
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Like wheeler said, give us some code, tell us what went wrong, and we'll try to help. Don't offer money - we're here to help eachother. Plus, offering money makes the thread a "Looking To Hire" thread, and those cost $10 to list on SitePoint

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php

    $aid 
    $_POST['aid'];
    $aid mysql_real_escape_string($aid);
    if (
    $aid != '') { // A author is selected
      
    $where .= " AND author_id='$aid'";
    }

    $cid $_POST['cid'];
    $cid mysql_real_escape_string($cid);
    if (
    $cid != '') { // A category is selected
      
    $from  .= ', jokecategory';
      
    $where .= " AND joke.id=joke_id AND category_id='$cid'";
    }

    $searchtext $_POST['searchtext'];
    $searchtext mysql_real_escape_string($searchtext);
    if (
    $searchtext != '') { // Some search text was specified
      
    $where .= " AND joke_description LIKE '%$searchtext%'";
    }
    ?>

    <table>
    <tr><th align="left">Author</th><th align="left">Jokes Description</th><th align="left">Joke Link</th></tr>

    <?php while ($row $rs->fetchArray()): ?>
            <td align="left"><?=$row['author_logo']?></td>
            <td align="left"><b><?=$row['joke_description']?></b></td>        
            <td align="left"><?=$row['joke_link']?></td>            
        </tr>

    <?php endwhile; ?>
    </table>
    <p><?=$rs->getPageNav("aid=$aid")?></p>
    Last edited by metrosky; Apr 27, 2007 at 07:14.

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php

    class MySQLPagedResultSet
    {

      var 
    $results;
      var 
    $pageSize;
      var 
    $page;
      var 
    $row;
      
      function 
    MySQLPagedResultSet($query,$pageSize,$dbh)
      {
        
    $resultpage $_GET['resultpage'];
        
        
    $this->results = @mysql_query($query,$dbh);
        
    $this->pageSize $pageSize;
        if ((int)
    $resultpage <= 0$resultpage 1;
        if (
    $resultpage $this->getNumPages())
          
    $resultpage $this->getNumPages();
        
    $this->setPageNum($resultpage);
      }
      
      function 
    getNumPages()
      {
        if (!
    $this->results) return FALSE;
        
        return 
    ceil(mysql_num_rows($this->results) /
                    (float)
    $this->pageSize);
      }
      
      function 
    setPageNum($pageNum)
      {
        if (
    $pageNum $this->getNumPages() or
            
    $pageNum <= 0) return FALSE;
      
        
    $this->page $pageNum;
        
    $this->row 0;
        
    mysql_data_seek($this->results,($pageNum-1) * $this->pageSize);
      }
      
      function 
    getPageNum()
      {
        return 
    $this->page;
      }
      
      function 
    isLastPage()
      {
        return (
    $this->page >= $this->getNumPages());
      }
      
      function 
    isFirstPage()
      {
        return (
    $this->page <= 1);
      }
      
      function 
    fetchArray()
      {
        if (!
    $this->results) return FALSE;
        if (
    $this->row >= $this->pageSize) return FALSE;
        
    $this->row++;
        return 
    mysql_fetch_array($this->results);
      }
      
      function 
    getPageNav($queryvars '')
      {
        
    $nav '';
        if (!
    $this->isFirstPage())
        {
          
    $nav .= "<a href=\"?resultpage=".
                  (
    $this->getPageNum()-1).'&'.$queryvars.'">Prev</a> ';
        }
        if (
    $this->getNumPages() > 1)
          for (
    $i=1$i<=$this->getNumPages(); $i++)
          {
            if (
    $i==$this->page)
              
    $nav .= "$i ";
            else
              
    $nav .= "<a href=\"?resultpage={$i}&".
                      
    $queryvars."\">{$i}</a> ";
          }
        if (!
    $this->isLastPage())
        {
          
    $nav .= "<a href=\"?resultpage=".
                  (
    $this->getPageNum()+1).'&'.$queryvars.'">Next</a> ';
        }
        
        return 
    $nav;
      }
    }

    ?>
    Last edited by metrosky; Apr 27, 2007 at 07:16.

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guy for you attention. The first script itself, the second is paginating script.

  7. #7
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when pasting, use [ php ] without the spaces and [ /php ] at the end, its alot easier to read that way.

    Your code is probably overkill and hard to debug. A simple pagination looks like this.
    PHP Code:
    <?php 

    // connect to database first 

    if (isset($_GET['page'])) {
        
    $page = (int)$_GET['page'];
        
    $page_set true;
    } else { 
        
    $page 1;
        
    $page_set false;
    }

    $max_results 5;

    $from = (($page $max_results) - $max_results); 

    $total_results mysql_result(mysql_query("SELECT COUNT(*) FROM numbers"),0);

    // Figure out the total number of pages. Always round up using ceil()
    $total_pages ceil($total_results $max_results);

    // Build Previous Link
    if($page 1){
        
    $prev = ($page 1);
    } else {
        
    $prev 1;
    }

    // Build Next Link
    if($page $total_pages){
        
    $next = ($page 1); 
    }

    $query mysql_query("SELECT * FROM numbers LIMIT $from,$max_results");

    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>

    <body>

    <?php while ($row mysql_fetch_array($query)) { 
    $number $row['number'];
    echo 
    "$number <br />";
    }

    ?>
    <p><a href="?page=<?php echo $prev?>">previous</a> <a href="?page=<?php echo $next?>">next</a></p>
    </body>
    </html>
    Just modify the query to suite your needs, and be sure to change the SELECT COUNT query as well, they must match!
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My Problem "WHERE" clause, not the paginating script. The paginating script I use works fine if I pull data from ONE table, but not together with code of search where in my case data suppose to be pulled from four tables.

    And by the way, the code is written in Chapter-6, that what I mentioned in the first post. How come it could be overkill and hard to debug?

    It a real HEADACH!!!

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by metrosky View Post
    My Problem "WHERE" clause, not the paginating script. The paginating script I use works fine if I pull data from ONE table, but not together with code of search where in my case data suppose to be pulled from four tables.

    And by the way, the code is written in Chapter-6, that what I mentioned in the first post. How come it could be overkill and hard to debug?

    It a real HEADACH!!!
    Because the book is years old (although revised a few times) and is about teaching, not best practices. If the script works without pagination as you said it did, then the problem is pagination. And the code you gave is indeed overkill and inefficient for pagination. Why pull every result from the database and print 5 rows when you can ask for just 5?

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank You Guys for your involvement.

    5 rows is for test only, before the database will be filled with hundreds or thousands of entries.

    The paginating script is provided by SitePoint as well. Check the link:
    http://www.sitepoint.com/article/php-paging-result-sets

    Just wasted my time on something that doesn't work.

  11. #11
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by metrosky View Post
    Thank You Guys for your involvement.

    5 rows is for test only, before the database will be filled with hundreds or thousands of entries.

    The paginating script is provided by SitePoint as well. Check the link:
    http://www.sitepoint.com/article/php-paging-result-sets

    Just wasted my time on something that doesn't work.
    That article is also 5 years old, and is also more about teaching a little OO PHP design than about pagination. The last page of the article, however, "A Note on Performance" basically tells you how the method the article just taught is horribly inefficient when you have more than a couple dozen rows in the tables. He then suggests using LIMIT and a COUNT(*) to retrieve only the rows you need to display instead of every row in the table.

  12. #12
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs down

    That's why it's "CATCH - 22"

  13. #13
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so did you try the method I outlined? you'll find its quite effective, COUNT(*) is faster than mysql_num_rows and FROM, LIMIT is of course reducing the workload dramatically.
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development


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
  •