PHP and MySQL Pagination

[eMonk]

I've been searching the web for basic php/mysql pagination tutorials to learn from since my book didn't cover this and having some problems.

Here's one that I'd like to learn from but it doesn't explain how to display the results on each page. After reading other pagination tutorials, I added a while loop and another query. Here's the link with source code, however I had to make some changes to make it work with mysqli:

http://www.tyleringram.com/blog/basi...ation-tutorial

Here's the while loop I added before the pages get echoed:

$max = 'limit ' .($page - 1) * $LIMIT .',' .$LIMIT;
$data_p = "SELECT * FROM model $max";
$result_2 = $db->query($data_p);

while ($list = $result_2->fetch_assoc()) {
   // echo data
   echo $list['id'] . " : " . $list['name'] . "<br />";
}

This works and displays the first 5 results on page 1 but once you click on page 2, I get a 404 error because the page doesn't exist. How can I view the results on page 2 and 3, etc?

The url output appears like this:

http://www.domain.com/2
http://www.domain.com/3
http://www.domain.com/4
etc.....

[FlexxAeon]

need to let it know which page you're on:

If (isset($_GET[‘page’]))

so url needs to be like domain.com/?page=2

[HomerSimpson]

need to let it know which page you're on:



so url needs to be like domain.com/?page=2

he could use site.com/3
but he must use mod_rewrite to get it...

better idea is something like site.com/page/3 or site.com/page-3
both must include mor_rewrite rule

if need more help hit me up
http://www.awmzone.com/services

[eMonk]

need to let it know which page you're on:

so url needs to be like domain.com/?page=2

It doesn't appear to be working.. here's my url echo...

echo "<a href=\"".$_SERVER['PHP_SELF']."/?page={$i}\">{$i}</a>";

[nm_]

your echo is wrong:

echo "<a href=\"".$_SERVER['PHP_SELF']."/?page={$i}\">{$i}</a>";

produces <a href="page.php/?page=0"></a>

you don't want the / in there

try:

echo "<a href=\"".$_SERVER['PHP_SELF']."?page={$i}\">{$i}</a>";

should work

[eMonk]

your echo is wrong:

echo "<a href=\"".$_SERVER['PHP_SELF']."/?page={$i}\">{$i}</a>";

produces <a href="page.php/?page=0"></a>

you don't want the / in there

try:

echo "<a href=\"".$_SERVER['PHP_SELF']."?page={$i}\">{$i}</a>";

should work

Thanks... I tried without the / before but it didn't work. Anymore ideas?

Right now when I clicked on page 2 or 3 I see the url link in the address bar but the results don't change.. still displays the query results from page 1.

[nm_]

 if (!$_GET['page'] && !ctype_digit($_GET['page'])) {
	
		throw new Exception('page number must be digit');
	
	}
	
	$page = $_GET['page'];
	$limit = 5;
	
	$max = 'limit ' .($page - 1) * $limit .',' .$limit;
	$data_p = "SELECT * FROM testtable $max";
	$result_2 = mysql_query($data_p);
	
	

	while ($list = mysql_fetch_assoc($result_2)) {
	   // echo data
	   echo $list['random'] . "<br />";
	}

i don't have pdo setup correctly on my test server, but this code worked for me. just replace the table name / row name in this code.

[eMonk]

That's basically what I have but it's still not working. Here's my complete code:

<?php

if (!$_GET['page'] && !ctype_digit($_GET['page'])) {
	
		throw new Exception('page number must be digit');
	
	}

// 5 Entries Per Page
$LIMIT = 5;

if (isset($_GET['page'])) {
  // Get Current page from URL
  $page = $_GET['page'];
}
  
if ($page <= 0) {
    // Page is less than 0 then set it to 1
    $page = 1;
} else {
  // URL does not show the page set it to 1
  $page = 1;
}

// Create MySQL Query String
include("../includes/connect.php");
// This is for your MySQL Query to limit the entries per page
$LimitValue = $page * $LIMIT - ($LIMIT);
$strqry = "SELECT id, name from model";
$result = $db->query($strqry);
// $query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />", mysql_error());
// Get number of rows returned
$TOTALROWS = $result->num_rows;
// Figure out how many pages there should be based on your $LIMIT
$NumOfPages = $TOTALROWS / $LIMIT;
// This is for your MySQL Query to limit the entries per page
// $LimitValue = $page * $LIMIT - ($LIMIT);

$max = 'limit ' .($page - 1) * $LIMIT .',' .$LIMIT;
$data_p = "SELECT * FROM model $max";
$result_2 = $db->query($data_p);

while ($list = $result_2->fetch_assoc()) {
   // echo data
   echo $list['id'] . " : " . $list['name'] . "<br />";
}

echo "<div id=\"paginating\" align=\"left\">Pages:";

// Check to make sure we’re not on page 1 or Total number of pages is not 1
if ($page == ceil($NumOfPages) && $page != 1) {
  for($i = 1; $i <= ceil($NumOfPages)-1; $i++) {
    // Loop through the number of total pages
    if($i > 0) {
      // if $i greater than 0 display it as a hyperlink
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page={$i}\">{$i}</a>";
      }
    }
}
if ($page == ceil($NumOfPages) ) {
  $startPage = $page;
} else {
  $startPage = 1;
}
for ($i = $startPage; $i <= $page+6; $i++) {
  // Display first 7 pages
  if ($i <= ceil($NumOfPages)) {
    // $page is not the last page
    if($i == $page) {
      // $page is current page
      echo " [{$i}] ";
    } else {
      // Not the current page Hyperlink them
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page={$i}\">{$i}</a> ";
    }
  }
}
echo "</div>";
echo "<p>Number of results found: ".$TOTALROWS."</p>";

?>

[nation-x]

Lots of examples...
http://www.catchmyfame.com/2007/07/2...ination-class/
http://www.phpsnaps.com/snaps/view/p...ination-class/
http://www.sitepoint.com/perfect-php-pagination/
http://phpsense.com/2007/php-pagination-script/

[nm_]

if ($page <= 0) {
    // Page is less than 0 then set it to 1
    $page = 1;
} else {
  // URL does not show the page set it to 1
  $page = 1;
}

this is the problem, remove the else statement. you're setting $page to 1 no matter what you enter into ?page="X"

[FlexxAeon]

i didn't read through the rest of the code past this part:

if (isset($_GET['page'])) {
// Get Current page from URL
$page = $_GET['page'];
}

if ($page <= 0) {
// Page is less than 0 then set it to 1
$page = 1;
} else {
// URL does not show the page set it to 1
$page = 1;
}

that's gonna set $page to "1" no matter what. so even if $page = 2 on the if clause before it, it gets set back to one

if (isset($_GET['page'])) {
// Get Current page from URL
$page = $_GET['page'];
}

if ($page <= 0 || $page == '' || is_null($page)) {
// Page is less than or equal to 0, or isn't set, so set it to 1
$page = 1;
}

[eMonk]

Lots of examples...
http://www.catchmyfame.com/2007/07/2...ination-class/
http://www.phpsnaps.com/snaps/view/p...ination-class/
http://www.sitepoint.com/perfect-php-pagination/
http://phpsense.com/2007/php-pagination-script/

I've seen some of those examples but was looking for an easier one to learn from. Thanks just as much though.

[eMonk]

if ($page <= 0) {
    // Page is less than 0 then set it to 1
    $page = 1;
} else {
  // URL does not show the page set it to 1
  $page = 1;
}

this is the problem, remove the else statement. you're setting $page to 1 no matter what you enter into ?page="X"

That worked, thanks!!!

[eMonk]

i didn't read through the rest of the code past this part:



that's gonna set $page to "1" no matter what. so even if $page = 2 on the if clause before it, it gets set back to one

I'll look into that part of the code soon, thanks again Flexx!!

[eMonk]

Sorry one more question about this....

I'm trying to display to results by RAND() and have added the following to the top of the code:

session_start();

$rand = $_SESSION['rand'];
if (empty($rand)) {
  srand((float)microtime()*1000000);
  $rand = "0.".rand();
  $_SESSION['rand'] = $rand;
}

Which works but when you click on page 2, etc it gets randomized again.

How can I have it so when you refresh the page or revisit it randomizes the results but doesn't again when you click on page 2, page 3, etc?

I tried adding in the following in a few places but doesn't seem to do anything:

if(isset($_SESSION['rand']))
    unset($_SESSION['rand']);

Also updated the $data_p query and now it looks like this:

$data_p = "SELECT * FROM model WHERE status = 'Active' ORDER BY RAND($rand) $max";

[Tempest]

Sorry one more question about this....

I'm trying to display to results by RAND() and have added the following to the top of the code:

session_start();

$rand = $_SESSION['rand'];
if (empty($rand)) {
  srand((float)microtime()*1000000);
  $rand = "0.".rand();
  $_SESSION['rand'] = $rand;
}

Which works but when you click on page 2, etc it gets randomized again.

How can I have it so when you refresh the page or revisit it randomizes the results but doesn't again when you click on page 2, page 3, etc?

I tried adding in the following in a few places but doesn't seem to do anything:

if(isset($_SESSION['rand']))
    unset($_SESSION['rand']);

Also updated the $data_p query and now it looks like this:

$data_p = "SELECT * FROM model WHERE status = 'Active' ORDER BY RAND($rand) $max";

Not sure why that's not working.. Sessions can be a pain in the ass sometimes... Keep in mind that session stuff has to occur before anything else gets output since it uses cookies.... Cleaned things up a little and this "should" work.

$page = (isset($_REQUEST['page']) ? intval($_REQUEST['page']) : 1);

$page = ($page < 1 ? 1 : $page);

session_start();

$rand = (isset($_SESSION['rand']) ? intval($_SESSION['rand']) : 0);

if( $rand < 1 || $page < 2 ){

	$rand = mt_rand(100000, 999999);

	$_SESSION['rand'] = $rand;
}

session_write_close();

Without using sessions, you could just pass the rand parameter in the url along with the page number. At least that way you could then know for sure it's there or not.

[eMonk]

That didn't seem to work Tempest. Anymore ideas?

[eMonk]

By the way, I'm using php includes and it may be the problem.

File1 has the php session and include codes.
File2 has the pagination code.
File3 is a template file.

[mpahlca]

http://lmgtfy.com/?q=how+to+do+pagination

[grumpy]

www.stackoverflow.com is your best shot

[Tempest]

That didn't seem to work Tempest. Anymore ideas?

I just tested the code I posted and it works perfectly.. i.e. page <= 1 generates a new random number and any other page uses the number that's stored in the session.

So the only thing I can think of is that your session isn't getting set properly. Make sure there is NO output before the session is opened, variables saved etc. Check the source of the page and see if there's any blank lines at the top for example. Turn on error reporting to see if any errors are getting thrown. Put this right at the very start of the script.

ini_set('display_errors', 1);
error_reporting(E_ALL);

[BestXXXPorn]

All these people offering advice and nobody points out to you that you have a giant gaping massive security hole... never, Never, NEVER use GET or POST variables right in a fucking SQL statement...

[Tempest]

All these people offering advice and nobody points out to you that you have a giant gaping massive security hole... never, Never, NEVER use GET or POST variables right in a fucking SQL statement...

Really? Care to point out where that is.. Unless I missed it in one of the posts, in almost all cases the only GET/POST variable used (page) is qualified in some way ahead of time.

[Klen]

All these people offering advice and nobody points out to you that you have a giant gaping massive security hole... never, Never, NEVER use GET or POST variables right in a fucking SQL statement...

You mean something like this ?

$sql = "UPDATE table SET column='$_POST[bla]'"; 

[BestXXXPorn]

You mean something like this ?

$sql = "UPDATE table SET column='$_POST[bla]'"; 

Yes, do not ever do that :P Imagine if the value of $_POST['bla'] was something like...

'; DROP DATABASE 'XXXXX

Byebye data... SQL injection FTL.

Check out http://us.php.net/manual/en/mysqli.r...ape-string.php

[potter]

All these people offering advice and nobody points out to you that you have a giant gaping massive security hole... never, Never, NEVER use GET or POST variables right in a fucking SQL statement...

Yeah, I was pretty shocked too.

$page = mysql_escape_string($_GET['page']);

In fact, I'd probably even set it as an INT as well.

[eMonk]

See anything wrong with this code?

<?php

session_start();

$rand = (isset($_SESSION['rand']) ? intval($_SESSION['rand']) : 0);

if( $rand < 1 || $page < 2 ){

	$rand = mt_rand(100000, 999999);

	$_SESSION['rand'] = $rand;
}

session_write_close();

$side_a = "includes/side-a.php";
$content = "includes/content.php";
include("template.php");

?>

content.php contains the pagination code. I added:

echo "rand = ". $_SESSION['rand'];

and each page has a different $rand value so it's not being saved... is there something wrong with the way I include files? I didn't find any blank lines in the files or output.

[Tempest]

See anything wrong with this code?

<?php

session_start();

$rand = (isset($_SESSION['rand']) ? intval($_SESSION['rand']) : 0);

if( $rand < 1 || $page < 2 ){

	$rand = mt_rand(100000, 999999);

	$_SESSION['rand'] = $rand;
}

session_write_close();

$side_a = "includes/side-a.php";
$content = "includes/content.php";
include("template.php");

?>

content.php contains the pagination code. I added:

echo "rand = ". $_SESSION['rand'];

and each page has a different $rand value so it's not being saved... is there something wrong with the way I include files? I didn't find any blank lines in the files or output.

Have you checked that sessions are setup and working properly on your system cause it's not looking like it is. Or perhaps something else is blowing it away?

[Klen]

Yes, do not ever do that :P Imagine if the value of $_POST['bla'] was something like...

'; DROP DATABASE 'XXXXX

Byebye data... SQL injection FTL.

Check out http://us.php.net/manual/en/mysqli.r...ape-string.php

Hmm tried to do sql inject with

 '; 'CREATE TABLE hax
(
hack1 varchar(15),
hack2 varchar(15),
)' 

and with other combinations of ' and ;
and it doesn't work no matter what.
Only what i noticed is how this causing query not to execute.

[potter]

Hmm tried to do sql inject with

 '; 'CREATE TABLE hax
(
hack1 varchar(15),
hack2 varchar(15),
)' 

and with other combinations of ' and ;
and it doesn't work no matter what.
Only what i noticed is how this causing query not to execute.

Wait wait wait... Are you saying you don't think SQL injections are possible with uncleaned GET/POST values?

[Klen]

Wait wait wait... Are you saying you don't think SQL injections are possible with uncleaned GET/POST values?

No i saying how i was not able to find proper combination to execute sql injection and i am sure how there is proper combination which will do the job.I still agree how leaving unprotected GET/POST value is huge security risk and should be protected no matter what.

[potter]

No i saying how i was not able to find proper combination to execute sql injection and i am sure how there is proper combination which will do the job.I still agree how leaving unprotected GET/POST value is huge security risk and should be protected no matter what.

http://www.google.com/search?q=mysql+injection+with+GET

[BestXXXPorn]

Don't close the session if you want access to the $_SESSION vars... You actually don't ever need to close the session for any normal logic, it happens automatically. The only time you really need to close it manually is if you need to have multiple requests from the same user modify the session data...

Let's say you're doing a large file upload and you want to track progress. Whatever PHP process is being held open by the file upload, you would want to close out the session in that request so that additional requests (checking the progress) would have access to the updated session values...

[BestXXXPorn]

In regards to SQL injection...

PHP now (as a safety measure) will not run multiple queries in the same SQL request.

That doesn't mean you can't modify a single query to do other things though...

[eMonk]

I just changed $strqry and $data_p and now when I click on page 2 it's showing 0 results.

old queries:

$strqry = "SELECT id from model WHERE status = 'Active' ";

$data_p = "SELECT * FROM model WHERE status = 'Active' ORDER BY RAND($rand) $max";

new queries:

$strqry = "SELECT DISTINCT model.id from model
           INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
           INNER JOIN city ON (city.city_id = model_in_city.city_id)
           INNER JOIN province on (city.province_id = province.id)
           WHERE province.name = '$region'
           AND model.status = 'Active' ";

$data_p = "SELECT DISTINCT(model.id), model.thumbnail, model.name, model.location FROM model
           INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
           INNER JOIN city ON (city.city_id = model_in_city.city_id)
           INNER JOIN province on (city.province_id = province.id)
           WHERE province.name = '$region'
           AND model.status = 'Active'
	   ORDER BY RAND($rand) $max";

It's showing 5 results out of 6 results like it should on the first page but when I click on page 2 it displays 0 results. Any ideas why?

Post #8 has the complete code:

http://gfy.com/showpost.php?p=18519612&postcount=8

[Operator]

pagination

[eMonk]

Any ideas?

[grumpy]

stackoverflow.com , give it a try

[raymor]

In regards to SQL injection...

PHP now (as a safety measure) will not run multiple queries in the same SQL request.

That doesn't mean you can't modify a single query to do other things though...

Because of the S in SQL, you can run two statements in one. Just inject a subquery, in other words put your malicious code in parentheses.