#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    2

    How to create multiple pages with Database info


    Good afternoon fellow programmers,

    I've been reading my reference book but I can't figure out to create more than one page for my list.

    I want to create a simple list from my DB.

    Then once I get to 100 titles I have a link that says NEXT

    Then the next page has the next 100 titles

    If you need to see my code I will send it

    Thanks for your advice
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    Originally Posted by phpnewbie34
    If you need to see my code I will send it
    No, don't send it, post it here (wrapped in [ PHP ] tags).

    Apart from that, your question is one of the most frequently asked questions of all times. If you Google for "php pagination", you'll find millions of explanations and examples.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,186
    Rep Power
    9398
    The magic word is "pagination".

    First, make a page that shows everything. Then we can adjust it to only show a certain number per page.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    2

    Re:


    This is my code.

    Code:
     <?php
    $con=mysqli_connect("*******","****","******","****");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    $result = mysqli_query($con,"SELECT * FROM pixs ORDER BY date DESC");
    //Keep variable outside while loop// 
    $last_date = null;
    while($row = mysqli_fetch_array($result))//Retrieve info from DB and store it in $ROW
      {
    $time = date("l jS F Y", $row['date']);//Retrieve info from DB, convert it day, month, year
    if ($time != $last_date){//$last date is set to null. Will work first time. $last_date is reset each time.
        
    echo "<p>" . $time . "</p>";
      echo "- ";
      echo '<a class="left" href="DetailPage.php?ID=' . $row["ID"];
      echo '">' . " " . $row['title']  .  '</a>';
      echo " $ " .$row['Price'] . " (" .$row['location'] . ")" ."<br />";
     $last_date=$time;//reset date to current DB time
             }
      
             elseif($last_date ==$time){//Test to see if Date is changed
      //Work second time so $time echoing is not repeated 
        
      echo "- ";
      echo '<a class="left" href="DetailPage.php?ID=' . $row["ID"];
      echo '">' . " " . $row['title']  .  '</a>';
      echo " $ " .$row['Price'] . " (" .$row['location'] . ")" ."<br />";
     $last_date=$time;//reset date
             }
             
      }
    This shows everything. Once I get to say 10 entries, how I generate a link for the next page?
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Let me Google that for you.

    To cut it short: When you have n items per page, and you're on page p, you need to skip (p - 1) * n items and then fetch the next n items. Both is done with the LIMIT clause in the query.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    2

    Re:


    Google was not very helpful.

    Here's my code:

    Code:
     // Connects to your Database 
     mysqli_connect("t", "", "", "") or die(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; 
     } 
     //Here we count the number of results 
    
     //Edit $data to be your query 
    
     $data = mysql_query("SELECT * FROM pixs") or die(mysql_error()); 
    
     $rows = mysqli_num_rows($data); 
    
     
    
     //This is the number of results displayed per page 
    
     $page_rows = 4; 
    
     
    
     //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; 
     
     //This is your query again, the same one... the only difference is we add $max into it
    
     $data_p = mysql_query("SELECT * FROM pixs $max") or die(mysql_error()); 
    
    
     //This is where you display your query results
    
     while($info = mysql_fetch_array( $data_p )) 
    
     { 
    
     Print $info['Name']; 
    
     echo "<br>";
    
     } 
    
     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> ";
    
     } 
    
    
     //just a spacer
    
     echo " ---- ";
    
    
     //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> ";
    
     } 
    
     ?> 
    	<?php
            $last_date = null;
    		while($row = mysqli_fetch_array($result)){
    		//Retrieve info from DB and store it in $ROW
      
    $time = date("l jS F Y", $row['date']);//Retrieve info from DB, convert it day, month, year
    if ($time != $last_date)//$last date is set to null. Will work first time. $last_date is reset each time.
       { 
    echo "<p>" . $time . "</p>";
      echo "- ";
      echo '<a class="left" href="DetailPage.php?ID=' . $row["ID"];
      echo '">' . " " . $row['title']  .  '</a>';
      echo " $ " .$row['Price'] . " (" .$row['location'] . ")" ."<br />";
     $last_date=$time;//reset date to current DB time
             }
      
             elseif($last_date ==$time)//Test to see if Date is changed
      //Work second time so $time echoing is not repeated 
        {
      echo "- ";
      echo '<a class="left" href="DetailPage.php?ID=' . $row["ID"];
      echo '">' . " " . $row['title']  .  '</a>';
      echo " $ " .$row['Price'] . " (" .$row['location'] . ")" ."<br />";
     $last_date=$time;//reset date
             }
             
      }
    mysqli_close($con);	
    ?>
    Any hints? Requinix?
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Um, nobody said that you should copy and paste the next best code snippet you find somewhere on the internet -- especially not when the author of that script says it's broken and asks for help herself.

    The reason I referred to Google is that pagination is a very, very common feature. You're not the first to come up with this idea. So all you need to do is pick a solution that looks credible, understand it and apply it to your own code. Have you never done something like that?

    I even posted the concrete "formula" and gave you the SQL clause to look up.

    So why not make a simple test script and play with it? The first step would be to pass the page number as a URL parameter and select all items belonging to this page from the database. When that works, you can continue with the "NEXT" links. Obviously you need the total number of rows to check if there even is a "NEXT" page. If it is, then it points to the current page + 1.

    I mean, we can help you with concrete questions. But don't expect us (or anybody else) to post some magic code for you to copy and paste.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,008
    Rep Power
    376
    basic thing for pagination:

    1. $_GET variables
    2. Mysql query needs start, limit variables that you get from (point 1)
    3. You use these variables to create next/prev links.

IMN logo majestic logo threadwatch logo seochat tools logo