Pagination - Page validation

[npulis]

Hi,

I have got a question when making the pagination using PHP and MySQL. It works fine but how can I calculate the total number of pages so that if a user enter viewposts?page=10000000000 it will stop him?

Thanks. The below code is my function of the pagination.

function getposts($offset, $itemsperpage)
{
// Connect to database
include ('../dbconn.php');
// Retreive posts Statement
$getpostsstatement = ("
SELECT p.post_id, p.post_title
FROM tk_post AS p
JOIN tk_posttype AS t ON (p.post_type = t.type_id)
JOIN tk_user AS u ON (p.post_author = u.user_id)
ORDER BY post_date DESC
LIMIT $offset, $itemsperpage
");
// Run the query to retreive the posts according to the specified page
$pageresult = mysql_query($getpostsstatement) or die('Error, query failed');
// Prepare the data to return to page
$postcounter = 0;
while($post = mysql_fetch_array($pageresult))
{
$posts[$postcounter][id] = $post[post_id];
$posts[$postcounter][title] = $post[post_title];
$posts[$postcounter][date] = date("F j, Y, g:i a", $post[post_date]);
$posts[$postcounter][type] = $post[type_name];
$posts[$postcounter][author] = $post[user_name]." ".$post[user_surname];
$posts[$postcounter][status] = $post[post_status];
$posts[$postcounter][views] = $post[post_views];
$postcounter += 1;
}
// Return results
return ($posts);
}

[Readie]

I had a problem like this the other day, so I will post the solution I was given. Just check to make sure that the ID exists within the database, and run an if statement through it, like so:

$page = mysql_real_escape_string($_GET['page']);
$sql = 'SELECT * FROM d_news WHERE page=' . $page;
$pagevalid = mysql_query($sql);
 
if(($result = mysql_fetch_array($pagevalid)) && ($page != "")) {
 echo 'Content etc...';
} else {
 header('HTTP/1.1 404 Not Found');
 echo 'Error';
}

[Readie]

However, this will expose some MySQL errors on the page, a way to hide these errors (but unfortunatley slightly increase page load time / the load on the server) is to wrap 2 if statements:

$page = mysql_real_escape_string($_GET['page']); 
 
if($page != "") {
$sql = 'SELECT * FROM d_news WHERE page=' . $page; 
$pagevalid = mysql_query($sql); 
 
if(($result = mysql_fetch_array($pagevalid)) { 
echo 'Content etc...'; 
} else { 
header('HTTP/1.1 404 Not Found'); 
echo 'Error'; 
}
} else {
echo 'List of links? Error message?';
}

[rocknbil]

how can I calculate the total number of pages so that if a user enter viewposts?page=10000000000 it will stop him?

Do a count first. You could do mysql_num_rows on the current select, but doing a count first will reduce the overhead a little, and your number of rows will always be equal to the limit clause.

True, if there are records, this will require two selects, but it's a trade off.

Something like

$count_select = "SELECT count(distinct p.post_id) FROM tk_post JOIN tk_posttype AS t ON (p.post_type = t.type_id) JOIN tk_user AS u ON (p.post_author = u.user_id)";

Get the total record count, then this. Note you should always test for a value first.

if (isset($_GET['page']) and ($_GET['page'] > 0)) { 
 // Note that the $count_select goes here 
 // Also note you test > 0, is_numeric returns true for zero 
 $page = mysql_real_escape_string($_GET['page']); 
 if (($count > 0) and ($page <= $count)) { 
 // do your query 
 } 
 else { echo "<p>No records found with that query.</p>"; exit; } 
} 
else { echo "<p>No page request.</p>"; exit; } 

[npulis]

Thanks guys, great response and great support. :)

[npulis]

Agreed with Rocknbil, Although it is another SQL Statement hence increasing the overhead... still it is more esthetically nicer. Since if you don't do the count before and a user does a page.php?page=10000 it will take some considerate time and the explorer will return a timeout.

Worked like a charm. Thanks.

[npulis]

Basically I did the following... and worked fine.

// Retreive the post count for verification
$postcountstatement = ("
SELECT count(DISTINCT post_id) AS count
FROM tk_post
");
// Run the query to return the results
$pagecountquery = mysql_query($postcountstatement);
$postcountresults = mysql_fetch_array($pagecountquery);
$postcount = $postcountresults[count];

// Check if the pagination will return results
if ($offset >= $postcount)
{
$posts[error]= 1;
$posts[postcount] = $postcount;
}
else

{
// Get the information needed
}

[rocknbil]

Be mindful of this,

if (isset($_GET['page']) and ($_GET['page'] > 0)) {
}
else {
}

That simple "if" is a protection against injection via "page". Any attempt to inject malicious data via XSS or malformed queries on 'page' will evaluate to zero, and won't work.

[npulis]

Thanks mate, will keep an eye on that :)

[Readie]

Agreed with Rocknbil

Wise, since I've only been doing PhP for a few months :)