Pagination Populating Prev and Next

[mkmac]

Hi,
 

I'm trying to populate the previous and next links with an id from mysql.   The code below works but also displays ids that do not exist in the database. 

 

I want the code to only show me the rows that exist not the ones that do not exist.  For example, at this time I have 5 rows.  The ids of the rows are 1, 2, 3, 4, 6  when I get to say, id 6 and click on next it displays id 7 instead of going back to 1 or greying out next - meaning there's no more to view.  

 

Can someone help? 

<?php
include('connection.php');


if(isset($_GET['id'])){
$start = $_GET['id'];
}else{
$start = 0;
}
$sql = mysql_num_rows(mysql_query("SELECT * FROM thetable"));

    
$result = mysqli_query($con,$sql);

$rows = mysql_fetch_array($result);

echo $rows['thetable'];


if($start == 0){
echo "Previous «";
}else{
echo "<a href=\"./thepage.php?id=" . ($start - 1) . "\">« Previous </a>";
}

if($start == $sql-1){
echo "Next »";
}else{
echo "<a href=\"./thepage.php?id=" . ($start + 1) . "\">Next »</a>";
}
?>



<?php
// End while loop.


mysqli_close($con);

?>

[Psycho]

Lot's of things wrong in that code:

 

1. NEVER use data directly from the user (e.g. $_POST, $_GET, etc.) in a query. You are opening yourself up to SQL injection. Either verify the data is 'valid' or, better yet, use prepared statements.

 

2. Don't use mysql_ functions - they are deprecated. You are mixing those and mysqli_ functions.

 

3. Don't use "*" in your SELECT query unless you really need all the fields. Plus, don't query all the rows and then use num_rows. Instead run a query to return the number of rows.

 

4. That code would not work as you have it. You are running a query to get the number of rows, and then using the result as a new query. ???

 

Since each "page" is a single record, you can change your logic to use a more traditional pagination approach and just limit the page size to one record. The prev/next links do NOT need the ID of the records. That way you can change the records per page at will.

[Psycho]

Here is a rough outline of how you could redo the code. This is not tested and written "off the cuff" so there may be a few syntax errors. But, it should point you in the right direction.

<?php
 
$recordsPerPage = 1;
 
include('connection.php');
 
//Get the current page to display
$currentPage = isset($_GET['page']) ? intval($_GET['page']) : 1;
//Calculate the limit start value
$limitStart = ($currentPage-1) * $recordsPerPage;
 
//Get the current page records
## NOTE the SQL_CALC_FOUND_ROWS allows you to use a query to get the
## current page records and calculate the total records (w/o the limit)
$query = "SELECT SQL_CALC_FOUND_ROWS field1, field2
          FROM thetable
          ORDER BY field1
          LIMIT {$limitStart}, {$recordsPerPage}";
$result = mysqli_query($con, $query);
 
//Run second query to get the total records calculated from 1st query
$query = "SELECT FOUND_ROWS()";
$result_count = mysqli_query($con, $query);
$rowResult = mysqli_fetch_array($result_count);
$totalRecords = $rowResult[0];
 
//Calculate total pages
$totalPages = ceil($totalRecords / $recordsPerPage);
 
//Create pagination controls
$paginationControls = '';
//Previous page
if($currentPage > 1)
{
    $prevPage = $page - 1;
    $paginationControls .= "<a href=\"./thepage.php?page={$prevPage}\">« Previous </a>";
}
else
{
    $paginationControls .= "« Previous";
}
//Next page
if($currentPage < $totalPages)
{
    $nextPage = $page + 1;
    $paginationControls .= "<a href=\"./thepage.php?page{$nextPage}\">Next »</a>";
}
else
{
    $paginationControls .= "Next »";;
}
 
//ADD CODE TO DISPLAY THE RECORD(S)
 
mysqli_close($con);
 
?>

Edited July 14, 2014 by Psycho

[mkmac]

I'm aware of the sql injection. I was trying to get something working so I can understand it.  Then I go back and fix what needs to be fixed. 

 

 

I'll try this and let you know. 

 

Thank you, 

Edited July 14, 2014 by mkmac

[mkmac]

I added the prev and next links but not getting the id in the link like I was previously.  It only displays id 1 when clicking on next and -1 when clicking on prev.  

 

In my previous code, I was getting all the ids and displaying the records.   The issue was displaying records that wasn't there.  This was due to the code picking ids that didn't exist -  Maybe because I was using SELECT * ? 

 

Let me add I need all the records for the id that appears in the next and previous links.  For example,  the next link has id 1, I need everything in id 1 to display.  This is the reason I'm using the id and using get to display the id in my code above. 

<?php
 
$recordsPerPage = 1;

if(ctype_digit($_GET['id'])){
$the_id = $_GET['id'];
}  else  {
$the_id = 0;
}
 
include('connection.php');
 
//Get the current page to display
$currentPage = isset($_GET['page']) ? intval($_GET['page']) : 1;
//Calculate the limit start value
$limitStart = ($currentPage-1) * $recordsPerPage;
 
//Get the current page records
## NOTE the SQL_CALC_FOUND_ROWS allows you to use a query to get the
## current page records and calculate the total records (w/o the limit)
$query = "SELECT SQL_CALC_FOUND_ROWS the_id
          FROM thetable
          ORDER BY the_id
          LIMIT {$limitStart}, {$recordsPerPage}";
$result = mysqli_query($con, $query);
 
//Run second query to get the total records calculated from 1st query
$query = "SELECT FOUND_ROWS()";
$result_count = mysqli_query($con, $query);
$rowResult = mysqli_fetch_array($result_count);
$totalRecords = $rowResult[0];
 
//Calculate total pages
$totalPages = ceil($totalRecords / $recordsPerPage);
 
//Create pagination controls
$paginationControls = '';
//Previous page
if($currentPage > 1)
{
    $prevPage = $page - 1;
    $paginationControls .= "<a href=\"./details.php?page={$prevPage}\">« Previous </a>";
}
else
{
    $paginationControls .= "« Previous";
}
//Next page
if($currentPage < $totalPages)
{
    $nextPage = $page + 1;
    $paginationControls .= "<a href=\"./details.php?page{$nextPage}\">Next »</a>";
}
else
{
    $paginationControls .= "Next »";;
}
 



if($currentPage == 0){
    echo "Previous «";
}else{
    echo "<a href=\"./details.php?id=" . ($currentPage - 1) . "\">Previous «</a>";
}

echo "     ";
if($start == $query-1){
    echo "Next »";
}else{
    echo "<a href=\"./details.php?id=" . ($currentPage + 1) . "\">Next »</a>";
}  




 
mysqli_close($con);
 
?>

Edited July 14, 2014 by mkmac

[mkmac]

UPDATE:  I got it to work far as the id in the link.   But still having he issue of: 

 

1.  It's displaying rows that do not exist like for example id=7 that row doesn't exist.  

2.  What should happen is, after the last row id=6 instead of going to id=7 next should be grayed out. 

3.  The same for prev after no records are found then it should not be linked to go to a record that doesn't exist. 

[Psycho]

I guess you didn't read my first post. You are making this more difficult than it needs to be. Just follow the "normal" pagination logic and make the page size 1. You do not need to know the ID of the next record to make this work. If you know there is another "page" of data (in this case at least a single record), then pass the next page number to the script. Then it doesn't matter if there is a gap of one or multiple numbers between record IDs - it just works!

 

To reiterate: You should be passing the PAGE number - not the record ID. It will work - I promise.

 

Also, I created the pagination controls as a variable for a reason. It gets unwieldy to manage code when you are going in and out of PHP/HTML repeatedly. Put all of your PHP code at the top of your script and create variables to hold the respective output. Then put the HTML at the bottom of the script just using echo's to output the dynamical generated content.

Edited July 14, 2014 by Psycho

[mkmac]

I read it but that's how I'm displaying the data on the page with the ids.   I thought if I just pass those ids then it will display the content needed which it does.  

 

You mentioned "PAGE" number... I don't know what you're referring to.  I don't have page numbers.  I made one page which displays all the info and when clicked say on next then it will be the same page let's call it thepage.php?...   click next ... it goes to thepage.php?id=2.....  

 

If you could so I understand what you're saying, post what this would be then - 
//ADD CODE TO DISPLAY THE RECORD(S)  

 

If I have that maybe it will make more sense on what you're saying. 

Edited July 14, 2014 by mkmac

[mkmac]

Anyone else want to try? 

 

Important points. 

 

!. The page is one page .. for example .. thepage.php.  The page handles all the data.   On the previous page an image is clicked on passing a variable which is the id from the db and displays it as thepage.php?id=1.  The second image thepage.php?id=2 etc.  

2.  In my mind the pagination.. Prev and Next will need to be populated with thepage.php?id=1 or thepage.php?id=2 or thepage.php?id=3 depending on which page you're on. 

3. At this time I have 5 rows of data.   Instead of stopping on - thepage.php?id=5 (the last record) .. it keeps going to thepage.php?id=6, thepage.php?id=7, thepage.php?id=8 etc.  I want the next to disappear when on the the last record with only the prev displaying.  Then the same for Prev when on the first record (thepage.php?id=1) Prev should disappear. 

 

Let me know if someone can help with this.  Thanks

Edited July 14, 2014 by mkmac

[Psycho]

If you could so I understand what you're saying, post what this would be then - 

//ADD CODE TO DISPLAY THE RECORD(S)  

 

How can I provide that? You should already have code to display the data from the "selected" record. I've given you the solution, but apparently there is some confusion on that solution. I think you are to wrapped up in what you think you need to be doing that you are not understanding what I have provided.

 

The solution is to implement the "normal" pagination process that allows the use to navigate from 'page' to 'page'. In this case the number of records for each page will be 1 (as opposed to 20 or some other set number). You do NOT need to determine the ID of the prev or next pages. That is only making this harder than it needs to be.

 

EDIT: The ONLY reason you would need to use a different process is if you need to display "data" regarding the Prev & Next records on the current page. Please provide the code to output the complete page and I'll see if I can rewrite as needed.

Edited July 14, 2014 by Psycho

[mkmac]

To start fresh here's  the code where the pagination will go.  I have the pagination in HTML only at the bottom.  I want you to see where I'm coming from since I'm passing the ID to display the records on this page.  If you can do it without the ID more power to ya. For example, to get to the page below, I'm clicking on an image which passes an id in order for the content to display on the page below.   Let me know how you would construct this without using an id to pass the data since currently the page is constructed to display "all" results  based off an ID.  Thanks

<?php include("head.php"); ?>

<body class="">

<?php include("navigation.php"); ?>

<?php

if(ctype_digit($_GET['id'])){
$the_id = $_GET['id'];
}  else  {
$the_id = 0;
}

include('connection.php');


$sql="SELECT * FROM the-table WHERE the_id = $the_id";
	  
$result = mysqli_query($con,$sql) or die(mysqli_error($con));


$row = mysqli_fetch_array($result);
?>


    <section class="container">
         <div class="containerDetail">
            
   <h1>title <?php echo $row['the_title'] ?></h1>
     <ul>
     <li>subject: <?php echo $row['the_subject'] ?></li>
    <li> summary: <?php echo $row['summary'] ?> </li>
   <?php 
   if(isset($row['view']) && !empty($row['view'])){ 
   echo "<br><li><a href='$row[view]'>View</a></dd>"; 
  ?>
   <?php }else{
   } ?>
   
     </ul> </div>
   <img src="<?php echo $row['Image']?>" />
   
<div class="pagination">
    <div>
      <div id="paglinks"><a href="#">prev</a> | <a href="#">next</a></div>
      
    </div>
  </div>
       </section>

</body>
</html>

Edited July 14, 2014 by mkmac

[fastsol]

Well after seeing so many forum posts about this subject and the difficulties that people seem to have with it, I decided to build a Pagination Page Generator.  It will take your specs and generate code that is ready to use.  You can copy and modify the code to your needs to make it fit your design.

 

http://amecms.com/article/Pagination-Page-Generator

 

I just posted this a little bit ago, so let me know if you come across any real issues or suggestions.  I try to dummy proof anything I build and make it logical to use but sometimes things are missed.

[Psycho]

I think you are taking the wrong approach as this will require three queries instead of one, but here, give this a tryu

 

<?php
//Make DB connection
include('connection.php');
 
//Set the ID
$the_id = (isset($_GET['id']) && ctype_digit($_GET['id'])) ? $_GET['id'] : 0;
//Vars to hold the dynamic output
$output = '';
$pagination = '';
 
//Run query to get the selected record data
$query = "SELECT the_title, the_subject, summary, view, Image
          FROM the-table
          WHERE the_id = $the_id";
$result = mysqli_query($con, $query) or die(mysqli_error($con));
$record = mysqli_fetch_assoc($result);
 
//Check that there was a match
if(!$record)
{
    $output .= 'No record found';
    $pagination = '';
}
else
{
    //Create record output
    $output .= "<div class='containerDetail'>\n";
    $output .= "  <h1>title {$record['the_title']}</h1>\n";
    $output .= "  <ul>\n";
    $output .= "    <li>subject: {$record['the_subject']}</li>\n";
    $output .= "    <li>summary: {$record['summary']}</li>\n";
    if(!empty($record['view']))
    {
        $output .= "    <li><a href='{$record['view']}'>View</a></li>\n";
    }
    $output .= "  </ul>\n";
    $output .= "</div>\n";
    $output .= "<img src='{$record['Image']}' /> \n";
 
    //Run query to get the prev record id
    $query = "SELECT MAX(the_id) FROM the-table WHERE the_id < $the_id";
    $result = mysqli_query($con, $query) or die(mysqli_error($con));
    $prevID = mysqli_fetch_assoc($result);
 
    //Run query to get the next record id
    $query = "SELECT MIN(the_id) FROM the-table WHERE the_id > $the_id";
    $result = mysqli_query($con, $query) or die(mysqli_error($con));
    $nextID = mysqli_fetch_assoc($result);
 
    //Create the pagination output
    $pagination .= "<div class='pagination'>\n";
    $pagination .= "  <div>\n";
    $pagination .= "    <div id='paglinks'>\n";
    $pagination .= ($prevID) ? "<a href='./thepage.php?id={$prevID}'>« Previous </a>" : "« Previous";
    $pagination .= " | ";
    $pagination .= ($nextID) ? "<a href='./thepage.php?id={$nextID}'>Next »</a>" : "Next »";
    $pagination .= "    </div>\n";
    $pagination .= "  </div>\n";
    $pagination .= "</div>\n";
}
 
?>
<?php include("head.php"); ?>
 
<body class="">
 
<?php include("navigation.php"); ?>
 
<section class="container">
  <?php echo $output; ?>
  <?php echo $pagination; ?>
</section>
 
</body>
</html>