GoFuckYourself.com - Adult Webmaster Forum

GoFuckYourself.com - Adult Webmaster Forum (https://gfy.com/index.php)

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - PHP and MySQL Pagination (https://gfy.com/showthread.php?t=1043403)

eMonk

10-27-2011 10:15 AM

PHP and MySQL Pagination

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:

Code:

$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

10-27-2011 10:19 AM

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

Quote:

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

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

HomerSimpson

10-27-2011 10:30 AM

Quote:

Originally Posted by FlexxAeon (Post 18519261)

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

10-27-2011 10:45 AM

Quote:

Originally Posted by FlexxAeon (Post 18519261)

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...

Code:

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

nm_	10-27-2011 11:12 AM

your echo is wrong:

Code:

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:

Code:

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

should work

eMonk

10-27-2011 11:16 AM

Quote:

Originally Posted by nm_ (Post 18519443)

your echo is wrong:

Code:

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:

Code:

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_	10-27-2011 11:48 AM

Code:

 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

10-27-2011 12:16 PM

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

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

10-27-2011 12:21 PM

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_	10-27-2011 12:27 PM

Code:

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

10-27-2011 12:30 PM

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

Quote:

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

Quote:

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

10-27-2011 12:58 PM

Quote:

Originally Posted by nation-x (Post 18519627)

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

eMonk

10-27-2011 12:59 PM

Quote:

Originally Posted by nm_ (Post 18519645)

Code:

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

10-27-2011 01:00 PM

Quote:

Originally Posted by FlexxAeon (Post 18519651)

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

10-30-2011 11:19 AM

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:

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:

Code:

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

    unset($_SESSION['rand']);

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

Code:

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

Tempest

10-30-2011 03:26 PM

Quote:

Originally Posted by eMonk (Post 18525188)

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:

Code:

session_start();



$rand = $_SESSION['rand'];

if (empty($rand)) {

  srand((float)microtime()*1000000);

  $rand = "0.".rand();

  $_SESSION['rand'] = $rand;

}

Code:

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

    unset($_SESSION['rand']);

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

Code:

$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.

Code:

$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

11-02-2011 11:04 AM

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

eMonk

11-02-2011 11:31 AM

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

11-02-2011 11:57 AM

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

grumpy

11-02-2011 12:29 PM

www.stackoverflow.com is your best shot

Tempest

11-02-2011 01:47 PM

Quote:

Originally Posted by eMonk (Post 18532323)

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.

Code:

ini_set('display_errors', 1);

error_reporting(E_ALL);

BestXXXPorn

11-02-2011 03:56 PM

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

11-02-2011 04:22 PM

Quote:

Originally Posted by BestXXXPorn (Post 18533038)

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	11-02-2011 04:26 PM

Quote:

Originally Posted by BestXXXPorn (Post 18533038)

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 ?

PHP Code:


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

BestXXXPorn

11-02-2011 05:28 PM

Quote:

Originally Posted by KlenTelaris (Post 18533094)

You mean something like this ?

PHP Code:


		
			
$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

11-02-2011 07:08 PM

Quote:

Originally Posted by BestXXXPorn (Post 18533038)

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.

Code:

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

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

eMonk

11-02-2011 09:55 PM

See anything wrong with this code?

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

11-02-2011 10:35 PM

Quote:

Originally Posted by eMonk (Post 18533515)

See anything wrong with this code?

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");



?>

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	11-03-2011 07:01 AM

Quote:

Originally Posted by BestXXXPorn (Post 18533209)

Hmm tried to do sql inject with

PHP Code:


		
			
 '; '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

11-03-2011 07:57 AM

Quote:

Originally Posted by KlenTelaris (Post 18534093)

Hmm tried to do sql inject with

PHP Code:


		
			
 '; '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	11-03-2011 08:02 AM

Quote:

Originally Posted by potter (Post 18534216)

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

11-03-2011 08:05 AM

Quote:

Originally Posted by KlenTelaris (Post 18534224)

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

BestXXXPorn

11-03-2011 08:23 AM

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

11-03-2011 08:27 AM

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

12-12-2011 10:35 PM

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

old queries:

Code:

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



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

new queries:

Code:

$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:

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

Operator

12-12-2011 10:50 PM

pagination

eMonk

12-13-2011 10:01 AM

Any ideas?

grumpy

12-13-2011 11:28 AM

stackoverflow.com , give it a try

raymor

12-13-2011 11:42 AM

Quote:

Originally Posted by BestXXXPorn (Post 18534287)

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.

All times are GMT -7. The time now is 07:08 AM.