Page 1 of 1

How to select limited data from mysql & paginate them

Posted: Wed Dec 12, 2012 9:10 am
by adsegzy
Hello, pls i need ur help. in my mysql, i have a table called "history" where all admin activities are stored and i echoed it out in 1 of the admin pages. below is the script i use to retrieve the data.

Code: Select all

$adjacents = 3;
$query = "SELECT COUNT(*) as num FROM history";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];
$targetpage = $_SESSION['PHP_SELF'];
$limit = 50;
$page = $_GET['page'];
if($page)
$start = ($page - 1) * $limit;
else
$start = 0;
/* Get data. */
$sql = mysql_query("SELECT * FROM history ORDER BY hoe desc LIMIT $start, $limit");
$result=mysql_num_rows($sql);
/* Setup page vars for display. */
if ($page == 0) $page = 1;
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total_pages/$limit);
$lpm1 = $lastpage - 1;
/*
Now we apply our rules and draw the pagination object.
We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<div class=\"pagination\">";
//previous button
if ($page > 1)
$pagination.= "<a href=\"$targetpage?page=$prev\">&laquo; previous</a>";
else
$pagination.= "<span class=\"disabled\">&laquo; previous</span>";
//pages
if ($lastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>"; }
}
elseif($lastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>"; }
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
$pagination.= "...";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>"; }
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//close to end; only hide early pages
else
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
$pagination.= "...";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}}}
//next button
if ($page < $counter - 1)
$pagination.= "<a href=\"$targetpage?page=$next\">next &raquo;</a>";
else
$pagination.= "<span class=\"disabled\">next &raquo;</span>";
$pagination.= "</div>\n";
}
the above line echo 50 result per page. what i want to do is that i want only the last 250 records to be echoed out at 50 records per page because the records in the table is now more than 2,000 and am having over 40 page, how do i echo just the latest 250 records which will be in 5 pages. or do you have a better script?

thanks

Re: How to select limited data from mysql & paginate them

Posted: Wed Dec 12, 2012 12:23 pm
by requinix
So what if there's 40 pages? Is there a problem with that? What if someone does want to see older records?

Re: How to select limited data from mysql & paginate them

Posted: Wed Dec 12, 2012 1:58 pm
by twinedev
Didn't have time to really go through the code, but one security issue did stand out. do not directly echo out the contents of $_SERVER['PHP_SELF'] as this is something that the visitor to the site can manipulate. See http://seancoates.com/blogs/xss-woes for an explanation. I was going to give examples here, but I have settings on my server that will not let me duplicate example XSS hacks (nice to know they worked), It is still not a good idea to trust that the server will catch it for you (what if one day you move the server)

-Greg

Re: How to select limited data from mysql & paginate them

Posted: Wed Dec 12, 2012 10:13 pm
by Christopher
1. Fix the security issues

2. Fix your variable naming. For example, $total_pages is actually $total_records, etc.

3. Try something like this:

Code: Select all

$lastpage = ceil($total_pages/$limit);
if($page) {
    $start = ($page - 1) * $limit;
} else {
    $start = ($lastpage - 4) * $limit;
    if ($start < 0) {
        $start = 0;
    }
}

Re: How to select limited data from mysql & paginate them

Posted: Fri Jan 04, 2013 3:33 am
by adsegzy
Thanks all, I really appreciate your contributions.