SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems with Pagination

    Hi all

    I have created a gift list for a friends wedding and have the table displaying fine and it pulls the data from the database no problem. I am trying to use pagination so I can limit the amount of results displaying on each page and so the user can click between the sets of results.

    I followed a tutorial online, please see link:
    http://php.about.com/od/phpwithmysql...pagination.htm

    Its pretty good, well I am still new to php and could understand it. The only problem is when I click on next it will not display the next set of records. Can anyone help please?

    See code below

    Many thanks

    Rob


    PHP Code:
                <table class="datatable">

                <?php
                
    //Create a connection to mysql
                    
    $con mysql_connect("localhost","******","******");
                    if (!
    $con)
                      {
                      die(
    'Could not connect: ' mysql_error());
                      }
                      
                    
    //This checks to see if there is a page number. If not, it will set it to page 1
                    
    if (!(isset($pagenum))) 
                        { 
                        
    $pagenum 1
                        } 
                      
                      
                
    //Create a connection to database                
                    
    mysql_select_db("*******"$con);
                
    //Query the database
                    
    $result mysql_query("SELECT * FROM giftList WHERE Archive = 'N'");
                    
                    
    $rows mysql_num_rows($result);
                    
                    
    //This is the number of results displayed per page 
                    
    $page_rows 5;                 
                    
                    
    //This tells us the page number of our last page 
                    
    $last ceil($rows/$page_rows); 
                    
                    
    //this makes sure the page number isn't below one, or more than our maximum pages 
                        
    if ($pagenum 1
                        { 
                        
    $pagenum 1
                        } 
                        elseif (
    $pagenum $last
                        { 
                        
    $pagenum $last
                        } 
                        
                        
    //This sets the range to display in our query 
                         
    $max 'limit ' .($pagenum 1) * $page_rows .',' .$page_rows;
                         
    $result_p mysql_query("SELECT * FROM giftList WHERE Archive = 'N' $max");
                    
                
    ?>
                
                
                
                <tr>
                    <th>Gift No.</th>
                    <th>Gift name</th>
                    <th>Gift brand</th>
                    <th>View gift</th>
                    <th>Choose a gift</th>                        
                    </tr>
                    
                    <form method="get" action="gift.php">
                
                <?php 
                    
                    
                    
    /*Use the mysql_fetch_array() function to return the first row from the recordset as an array.
                    Each call to mysql_fetch_array() returns the next row in the recordset.
                    The while loop loops through all the records in the recordset. 
                    To print the value of each row, we use the PHP $row variable ($row['FirstName'] and $row['LastName']). 
                    */
                    
    while($info mysql_fetch_array($result_p))
                      {
                           
                          echo 
    "<tr>";
                          echo 
    "<td>" $info["ID"] . "</td>";
                          echo 
    "<td>" $info['ProductName'] . "</td>";
                          echo 
    "<td>" $info['BrandName'] . "</td>";
                          echo 
    '<td><a href="http://' $info['ProductLink'] . '" target="_blank">'$info['ProductLink'] . "</a></td>";
                          echo 
    '<td><a href="giftchoice.php?id='$info["ID"] .'">Choose</a>';
                          echo 
    "</tr>";

                      }
                      
                        echo 
    "<p>";
                        
    // This shows the user what page they are on, and the total number of pages
                        
    echo " --Page $pagenum of $last-- <p>";                      

                      
                        
    // First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
                        
    if ($pagenum == 1
                        {
                        } 
                        else 
                        {
                        echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
                        echo 
    " ";
                        
    $previous $pagenum-1;
                        echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
                        } 
                        
                        
                        
                        
    //This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
                        
    if ($pagenum == $last
                        {
                        } 
                        else {
                        
    $next $pagenum+1;
                        echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
                        echo 
    " ";
                        echo 
    " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
                        } 
              
                      
                      
                      
                    
                    
    //Close the database connection
                    
    mysql_close($con);
                    
                    
    ?>
                    
                    </form>
                    
                    </table>

  2. #2
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works.

    PHP Code:
    $recordsPerPage 8// Entries per page.
    // Get records and calculate max pages.
    $totalTecords mysql_query("SELECT COUNT(id) FROM koirat");
    $totalTecords mysql_fetch_row($totalTecords);
    $totalTecords $totalTecords[0];
    $pages intval(ceil($totalTecords/$recordsPerPage));

    // Check which page we are on and that page is not under 1 and over max pages.
    if (!isset($_GET['page']) || $_GET['page'] > $pages || $_GET['page'] < 1)
    {
        
    $currentPage 1;
    }
    else
    {
        
    $currentPage intval($_GET['page']);
    }

    // Calculate limit offset.
    $offset = ($currentPage $recordsPerPage) - $recordsPerPage;

    $sql "SELECT * FROM koirat LIMIT $offset$recordsPerPage";
    $results mysql_query($sql);
    echo 
    '<table border="1"><tr><th>title1</th><th>title2</th></tr>';
    while (
    $row mysql_fetch_object($results))
    {
        echo 
    '<tr><td>'$row->nimi .'</td><td>'$row->rekisteri .'</td><tr>';
    }
    echo 
    '</table>';

    // Create paging.
    $output '';
    if (
    $currentPage === 1)
    {
        
    $output .= 'PREV&nbsp;' $currentPage '&nbsp;';
        
    $i $currentPage+1;
        while(
    $i <= $pages)
        {
            
    $output .= '<a href="'basename($_SERVER['SCRIPT_NAME']) .'?page='$i .'">'$i .'</a>&nbsp;';
            
    $i++;
        }
        
    $output .= '<a href="'basename($_SERVER['SCRIPT_NAME']) .'?page='. ($currentPage+1) .'">NEXT</a>';
    }
    else if (
    $currentPage === $pages)
    {
        
    $output .= '<a href="'basename($_SERVER['SCRIPT_NAME']) .'?page='. ($currentPage-1) .'">PREV</a>&nbsp;';
        
    $i 1;
        while(
    $i <= $pages)
        {
            if (
    $i === $pages)
            {
                
    $output .= $i '&nbsp;NEXT&nbsp;';
            }
            else
            {
                
    $output .= '<a href="'basename($_SERVER['SCRIPT_NAME']) .'?page='$i .'">'$i .'</a>&nbsp;';
            }
            
    $i++;
        }
    }
    else
    {
        
    $output .= '<a href="'basename($_SERVER['SCRIPT_NAME']) .'?page='. ($currentPage-1) .'">PREV</a>&nbsp;';
        for (
    $i 1$i <= $pages$i++)
        {
            if (
    $i === $currentPage)
            {
                
    $output .= $i '&nbsp;';
            }
            else
            {
                
    $output .=  '<a href="'basename($_SERVER['SCRIPT_NAME']) .'?page='$i .'">'$i .'</a>&nbsp;';
            }
        }
        
    $output .= '<a href="'basename($_SERVER['SCRIPT_NAME']) .'?page='. ($currentPage+1) .'">NEXT</a>';
    }
    echo 
    $output
    edit: re-read your message and you said next / prev is not working so I added them to my code..
    Last edited by TeNDoLLA; Jul 2, 2009 at 15:07.

  3. #3
    SitePoint Addict amitjoshi's Avatar
    Join Date
    Jul 2005
    Location
    Oklahoma, USA
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    change this code:
    PHP Code:
     //This checks to see if there is a page number. If not, it will set it to page 1

                    
    if (!(isset($pagenum))) 

                        { 

                        
    $pagenum 1

                        } 
    to this:
    PHP Code:
     //This checks to see if there is a page number. If not, it will set it to page 1

                   
    if (!isset($_GET['pagenum']) || !is_numeric($_GET['pagenum'])) 
                  {
                      
    $pagenum 1
                  }
                   else {
                       
    $pagenum $_GET['pagenum'];
                   } 
    The links should work now.

  4. #4
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Btw. using PHP_SELF makes you vulnerable to XSS attacks. Use $_SERVER['SCRIPT_NAME'] instead.

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent thanks everyone for your replies, I will give that a try tonight when I am home.

    Many thanks again

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To TeNDoLLA and amitjoshi

    Thanks so much for your help I have now got it to work. I have only just had the chance to have a go and it work.

    Many thanks

    Rob


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
  •