Pagination issue in PHP

Question

I have a simple pagination script for my search, but I'm facing some trouble.

When I change the page, the query returns to the initial definition. Something like:

Definition: Select * from a;

Search: Select * from a where a.id = 1;

And then when I go to the 2nd page the query becames that: Select * from a; again.

The code: I have a form with some fields for the search and then the script:

 $sqlCount_pre = "SELECT COUNT('*') FROM a ";
 $sqlCount_pre .= " INNER JOIN b ON a.id = b.id ";
 if( sizeof( $where ) ){
    $sqlCount_pre .= ' WHERE '.implode( ' AND ',$where );
 }

 $max = 2;
 $sqlCount = mysql_query($sqlCount_pre);
 $sqlResult = ceil(mysql_result($sqlCount, 0) / $max);
 $pg = (isset($_GET["pg"])) ? (int)$_GET["pg"] : 1 ;
 $start = ($pg - 1) * $max;
 $sqlQuery = "SELECT a.* FROM a ";
 $sqlQuery .= " INNER JOIN b ON a.id = b.id ";

 if( sizeof( $where ) ){
    $sqlQuery .= ' WHERE '.implode( ' AND ',$where );
    $sqlQuery .= " LIMIT $start, $max";
 }else{
    $sqlQuery .= " LIMIT $start, $max";
 }

$sql = mysql_query($sqlQuery) or die(mysql_error());

Then i show the result in the page. After that i put the page links, as in this code:

<?php
   if($sqlResult > 1 && $pg<= $sqlResult){
       for($i=1; $i<=$sqlResult; $i++){
           echo "<a href='?pg=$i'>$i</a> ";
       }
   }       
?>

Is there something wrong or missing in this code?

are you escaping $pg? I'm not sure I'd be totally trusting of $pg to use it as my limit without checking it first. — sjt003, May 06 '14 at 17:32
isn't this: "$pg = (isset($_GET["pg"])) ? (int)$_GET["pg"] : 1 ;" the checking of the $pg? how would you do it? — ziad.ali, May 06 '14 at 17:39
instead of casting a value to an integer, which might result in something you dont want, would you rather use is_int and not even execute the query if you were getting a non-integer. You're way seems good though. Just offering a suggestion. — sjt003, May 06 '14 at 18:31
@sjt003 how would you do that? can you show me in code, please? i didn't use that function before, so i don't know how to do what you telling me. Thank you. — ziad.ali, May 07 '14 at 13:10

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

is_int() doc is here

$pg = (isset($_GET["pg"]) && is_int($_GET["pg"])) ? (int)$_GET["pg"] : 1 ;

of course with this there is no reason for (int)... because we've already established $_GET["pg"] as an int, so type conversion is moot.

$pg = (isset($_GET["pg"]) && is_int($_GET["pg"]) &&  $_GET["pg"] > 0) ? $_GET["pg"] : 1 ;

this will return 1 if

$_GET["pg"]

returns a string of numbers like "42". For an alternative and is_numeric and a description and comparison of the two check out this article.

If GET returns a string

pg_escape_string($_GET["pg"])

pg_escape_string doc

Back to your original question. When you change pages the limit is not being executed. So the offset and max are not being set. take a look at the query executed when you hit the second page. Are you calculating the offset correctly?

are you only specifying $pg in

  echo "<a href='?pg=$i'>$i</a> ";

you'd have to put in the search term as well to pass it to the $where array. The count will be 0 if you don't and that's why the implode doesn't happen.

multiple variables here

edited May 23 '17 at 10:26

Community

1
1

answered May 07 '14 at 15:58

sjt003

2,407
5
24
39

hey @stj003. I replaced with you code doing is_int. Do i still use that: "$start = ($pg - 1) * $max;" ? About your question: I set the limit to be 2 register per page. When i open the page the query is: "SELECT a.* FROM a INNER JOIN b ON a.id = b.id LIMIT 0, 2". When i try to search for something the query is: "SELECT a.* FROM a INNER JOIN b ON a.id = b.id WHERE `a`.`name` like "%joe%" LIMIT 0, 2". And then finallly when i change the page the query is: " SELECT a.* FROM a INNER JOIN b ON a.id= b.id LIMIT 2, 2". – ziad.ali May 07 '14 at 17:54
the second page query looks like it would return 2 records **not** seen on the first page (i.e records 2-4, right?), but you're saying the second query returns the same records as the first, right? Your db has no duplicate records, right? – sjt003 May 07 '14 at 18:06
yes, my query with the search %joe% should return 4 records. I do not have duplicate records. When i move to the second page it shows me the results of the second page of the first query, got it? as you can see, in the second page the query is not applying this: "WHERE a.name like "%joe%" ". – ziad.ali May 07 '14 at 18:51
check out my edit I think when you loop through the page names you're only supplying limit and not the search term. – sjt003 May 07 '14 at 19:21
riiiiight, that's make sense. But, when i have just only field for search i would pass like this: "?search=$searchField;pg=1" right? But i have more than one field and inner join, how do i do that? – ziad.ali May 07 '14 at 19:37
@sjt003 - changing his code to use `is_int()` doesn't make it any more secure - just longer. – Sam Dufel May 07 '14 at 19:37
You will though be defaulted to $pg = 1 more often with is_int(). $pg could be set to a character and then converted to an integer not equal to one with @user3383534's code. Correct? – sjt003 May 07 '14 at 19:43
1

What if someone sets a page number of -1? – Sam Dufel May 07 '14 at 20:07
@sjt003 thank you for your help! i can't vote up for you answer because i do not have enough reputation, sorry. – ziad.ali May 07 '14 at 21:55

Sam Dufel · Accepted Answer · 2014-05-07T21:28:14.930

0

When you combine a search form with pagination, you need to embed the search parameters in the paging links.

<?php
   if($sqlResult > 1 && $pg<= $sqlResult){
       $query = $_GET;
       for($i=1; $i<=$sqlResult; $i++){
           $query['pg'] = $i;
           echo "<a href='?" . http_build_query($query) ."'>$i</a> ";
       }
   }       
?>

The above code takes everything in your current query string and adds in the applicable pg attribute when building the pagination links.

-- edit --
Combining a POST search form with pagination gets a little weird. There are a couple things you can do:

Use javascript to populate a hidden "page" field in the form when you click one of the pagination links, and resubmit the form, e.g.:

$('.pagination-link').on('click', function() {
    $('#page').val($(this).html());  //where you have a hidden field #page
    $('$searchform').submit();
});

OR
Change the form method to GET, and use the example I posted above

edited May 07 '14 at 21:28

answered May 07 '14 at 19:29

Sam Dufel

17,560
3
48
51

"$query = $_GET;" Do i have to put something after the $_GET? like $_GET['something'] ? Because i tried your code and still doing the same thing. – ziad.ali May 07 '14 at 19:49
is your search form method `POST`? – Sam Dufel May 07 '14 at 20:03
Yes it's. Why? Do i need to change something? – ziad.ali May 07 '14 at 20:14
will something like this work? echo "$i" – sjt003 May 07 '14 at 20:16
@ziad.ali - using a `POST` search form with pagination gets a little weird. See my edit above – Sam Dufel May 07 '14 at 21:28
I chose to use GET instead and it's working now! Thank you @SamDufel for the help! – ziad.ali May 07 '14 at 21:52

Pagination issue in PHP

2 Answers2