I have been making my own pagination in PHP with SQL and PDO. However, after a certain number of pages the results start to be ordered back to front. Meaning the bottom record should be at the top.
Here is my SQL (Ordered by primary id in the table):
$prepareStatement = $db_connection->prepare("SELECT * FROM " . $databaseTableName . " ORDER BY " . $databaseOrderBy . " DESC LIMIT ?, ?");
Then I use PHP PDO to create an array like so:
$dataRows = $prepareStatement->fetchAll(PDO::FETCH_ASSOC);
Then I output the data like this:
for($i = 0; $i <= $rowCount - 1; $i++)
{
echo $i . " ";
//Output the data with HTML...
echo '
<div class="messageDisplayBoxReportError">
<p class="reportErrorTime">' . $dataRows[$i]["time"] . '</p>
<P class="reportErrorDate">' . $dataRows[$i]["date"] . '</p>
<p><b>Message From: ' . $dataRows[$i]["name"] . ' | Email: ' . $dataRows[$i]["email"] . '</b></p>
<p>' . $dataRows[$i]["message"] . '</p>
</div>';
}
An example of my problem is that I have 47 records in my table, and I have set it to display 4 records per page, this comes to 12 pages with the last page only having 3 records instead of 4 as there are only 3 left. This all works but when I get to page 6 and onward the records start to display back to front.
The data outputs like this and the bottom record should be at the top:
This happens from page 6 and on wards. I cannot understand why this is happening as it doesn't happen until I get to page 6. All pages before page 6 display in order correctly. I have my SQL statement set to order them using DESC and cannot see why this is not working.
Thanks if you can help me!
EDIT, My navigation link script: The following code is a little bit of a mess as I followed a guide and had to fix some areas myself as they were not working fully. So sorry if some of the code is a mess or bad...
//The default paging output is set to 1.
$pageFrom = 1;
//If the total number of news post goes past the total number of records per page...
if($totalRowCount > $numberOfRecordsPerPage)
{
//Set the default number of pages to 10.
//Meaning we will output up to page 10 for the paging links...
$totalPages = 10;
//If the total pages is less than the default total pages of 10 then update the total pages variable so that we dont output more pages than we need.
if($totalPagesCount < $totalPages)
{
//When the total number of pages is below the default update the total number of pages to the correct value.
//This is done so that we don't output more pages than we need too.
$totalPages = $totalPagesCount;
}
//When the $page number is more than 5 we can start making the range for the paging links.
if($page > 5 && $totalPagesCount > 10)
{
//If the current page selected was less than the last page the user was on...
//It means we must go back 10 pages in our paging ranges as the user selected a lower page.
if($page < $_SESSION[$URLPageName])
{
//As the user has selected a lower page than before, we want to select a range which will be a lower range of - 10.
$pageFrom = $page - 8;
$totalPages = $page + 1;
//While we were selecting the range if the starting page goes below 1 it means we are back at the start.
//So we need to set the start page and the end page to 1 - 10.
if($pageFrom < 1)
{
$pageFrom = 1;
$totalPages = 10;
}
//While the total pages is more than the total number of page count.
//This means that the total pages we wish to display is bigger than the actual number of pages that exist.
//So take away 1 each time until we get to the last page of the post news.
while($totalPages > $totalPagesCount)
{
$totalPages--;
$pageFrom--;
}
}
else //Else it means the page selected was either the same page or higher than the last page the user was on. So we go higher in our paging range.
{
//Because the user would have selected a higher page than before, we now want to create a range which is 10 more than the last.
//Doing this allows us to show 10 more pages if there is any.
$pageFrom = $page - 1;
$totalPages = $page + 8;
//While we were selecting the range if the starting page goes below 1 it means we are back at the start.
//So we need to set the start page and the end page to 1 - 10.
if($pageFrom < 1)
{
$pageFrom = 1;
$totalPages = 10;
}
//While the total pages is more than the total number of page count.
//This means that the total pages we wish to display is bigger than the actual number of pages that exist.
//So take away 1 each time until we get to the last page of the post news.
while($totalPages > $totalPagesCount)
{
$totalPages--;
$pageFrom--;
}
}
}
//Update the last page session variable to the current page the user was on after deciding the page range.
$_SESSION[$URLPageName] = $page;
//Output the total number of pages.
echo "<p><b>Total Pages:</b> " . ceil($totalRowCount / $numberOfRecordsPerPage) . "</p>";
//When the total number of pages is more than 10, display the first page button.
if($totalPagesCount > $numberOfRecordsPerPage && $page != 1)
{
echo '<a class="pageLinkStyle" href="' . $this->cleanForOutput($_SERVER["PHP_SELF"]) . '?' . $URLPageName . '=1' . $redirectToHTMLDiv . '">First Page</a>';
}
//We already have the data for this for loop setup so now we just need to run it and output the HTML for the paging.
//This is the for loop used to output the paging results.
for($i = $pageFrom; $i <= $totalPages; $i++)
{
//However if the current number int he for loop is == tot he current selected page then output a different paging link that shows it as the selcted page.
if($i == $page)
{
//Normal HTML output which does not show which page the user is not.
echo '<a class="pageLinkStyle pagingSelected" href="' . $this->cleanForOutput($_SERVER["PHP_SELF"]) . '?' . $URLPageName . '=' . $i . $redirectToHTMLDiv . '">' . $i . '</a>';
}
else
{
//This HTML has an extra class for when the selcted page result is output, showing the user which page they are on.
echo '<a class="pageLinkStyle" href="' . $this->cleanForOutput($_SERVER["PHP_SELF"]) . '?' . $URLPageName . '=' . $i . $redirectToHTMLDiv . '">' . $i . '</a>';
}
}
//If there is more pages hidden because of the paging going on, show a ... to indicate there is more results to show when the user selects a higher page number.
//This is done by checking if the total number of rows is more than 10 and if the current page is not equal to the last page and if the total pages is not more than the total number of pages count.
if($totalRowCount > $numberOfRecordsPerPage && $page != $totalPagesCount && !($totalPages >= $totalPagesCount))
{
echo "<p class='pagingDots'><b>...</b></p>";
}
//When the total number of pages is more than 10 display the last page button.
if($totalPagesCount > $numberOfRecordsPerPage && $page != $totalPagesCount)
{
echo '<a class="pageLinkStyle" href="' . $this->cleanForOutput($_SERVER["PHP_SELF"]) . '?' . $URLPageName . '=' . $totalPagesCount . $redirectToHTMLDiv . '">Last Page</a>';
}
Aucun commentaire:
Enregistrer un commentaire