Pagination Php not working when different sql query conditions are added

Question

Hello I am facing a problem with my pagination system, where if I list results from a mysql table it is working fine, but in case If I add some conditions inside the SQL Query like "AND" this column "AND" other column the script shows the results properly on the first page, when I chooce the second page instead of showing the second portion of results from 26 forward it is starting a new pagination and it is showing everything from the begining without the contions added inside the query. Here is the code of the pagination with the query:

 //This gets all the other information from the form 
 $ciudad=$_POST['ciudad']; 
 $tipo=$_POST['tipo']; 

$con=mysqli_connect();
// Check connection
$sql = "SELECT * FROM cursos WHERE 1";
if (!empty($ciudad)) {
  $sql .= " AND ciudad = '$ciudad' ";
}
if (!empty($tipo)) {
  $sql .= " AND tipo= '$tipo' ";
}
if (!$result = mysqli_query($con,$sql))
{
    die("Error: " . mysqli_error($con));
}

$per_page =25;//define how many games for a page
$count = mysqli_num_rows($result);
$pages = ceil($count/$per_page);

if(!isset($_GET['page']) || $_GET['page']=="") {
  $page="1";
} else {
  $page=$_GET['page'];
}
$start    = ($page - 1) * $per_page;
$sql = "SELECT * FROM cursos WHERE 1 LIMIT $start,$per_page";

?>

This is the code of the generated pages links:

<?php
        //Show page links
        for ($i = 1; $i <= $pages; $i++)
          {?>
          <li id="<?php echo $i;?>"><a href="search_cursos.php?page=<?php echo $i;?>"><?php echo $i;?></a></li>
          <?php           
          }
        ?>

Your current code is vulnerable to SQL injection attacks. You may want to look into prepared statements instead: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — PeeHaa, Oct 30 '13 at 22:33
this page is inside sevrer protected directory so the only one who will be able to see it will be the administrator. so no worries about the SQL injection attachs, thanks for the comment by the way — John Siniger, Oct 30 '13 at 22:36

Efekan · Answer 1 · 2013-10-30T22:43:38.753

If $ciudad and $tipo both are not empty your query on execution will look like this:

SELECT * FROM cursos WHERE 1 AND ciudad = '$ciudad' ORDER BY id DESC AND tipo= '$tipo' ORDER BY id DESC

It should be like this if i am not mistaken:

SELECT * FROM cursos WHERE 1 AND ciudad = '$ciudad' AND tipo= '$tipo' ORDER BY id DESC

What I would do is change this:

$sql = "SELECT * FROM cursos WHERE 1";
if (!empty($ciudad)) {
  $sql .= " AND ciudad = '$ciudad' ORDER BY id DESC ";
}
if (!empty($tipo)) {
  $sql .= " AND tipo= '$tipo' ORDER BY id DESC ";
}

too this:

$sql = "SELECT * FROM cursos WHERE 1 ";
if (!empty($ciudad)) {
    $sql .= "AND ciudad= '$ciudad' ";
    if (!empty($tipo)) {
       $sql .= "AND tipo= '$tipo' ";
    }

      $sql .= "ORDER BY id DESC ";
    }

I've also got a link which might help you out with the pagination.

http://www.phpjabbers.com/php--mysql-select-data-and-split-on-pages-php25.html

I did removed the ORDER option from the query since this is not the real problem thanks for the comment I will think about it after the major problem is resolved — John Siniger, Oct 30 '13 at 22:43

Asenar · Accepted Answer · 2013-10-30T23:04:56.970

The 2 problems where:

additional filter are not anymore selected in the next page ($_POST will be empty)
instructions related to pagination where calculated AFTER the query (which, obviously, couldn't use theses parameters)

You can either store your extra queries conditions in session, or add it as parameter in the "next page link", or transform your link to a submit form (which is probably the best option)

<li id="<?php echo $i;?>"><a href="search_cursos.php?page=<?php echo $i.'&amp;ciudad='.$ciudad.'&amp;tipo='.$tipo; ?>"><?php echo $i;?></a></li>

If you choose the link solution, don't forget to change your _POST in _GET (or check the second if the first is empty, or use $_REQUEST)

I have to mention your code is not sql injection free and using mysqli_prepare() may worth the time (for security and performances)

EDIT: so, here we go:

sidenotes: using $_REQUEST is not always recommended

And I noticed also you execute your query BEFORE using the pagination system...

 //This gets all the other information from the form 
 $ciudad=$_REQUEST['ciudad']; 
 $tipo=$_REQUEST['tipo']; 

$con=mysqli_connect();
// Check connection
$sql = "SELECT * FROM cursos WHERE 1";
if (!empty($ciudad)) {
  $sql .= " AND ciudad = '$ciudad' ";
}
if (!empty($tipo)) {
  $sql .= " AND tipo= '$tipo' ";
}

//  PAGINATION MOVED UP

$per_page =25;//define how many games for a page
$count = mysqli_num_rows($result);
$pages = ceil($count/$per_page);

if(empty($_GET['page'])) {
  $page="1";
} else {
  $page=$_GET['page'];
}
$start    = ($page - 1) * $per_page;

$sql .= ' LIMIT '.$start.','.$per_page;

if (!$result = mysqli_query($con,$sql))
{
    die("Error: " . mysqli_error($con));
}



        //Show page links
        for ($i = 1; $i <= $pages; $i++)
          {?>
          <li id="<?php echo $i;?>"><a href="search_cursos.php?page=<?php echo $i.'&amp;ciudad='.$ciudad.'&amp;tipo='.$tipo; ?>"><?php echo $i;?></a></li>
          <?php           
          }
        ?>

If I choose the link option it shows the correct number of pages but it shows the same results on every page, and in every page it is showing all the results not 25 for example — John Siniger, Oct 30 '13 at 22:51
of course, if you add the `LIMIT X,Y` after the query is executed, well... ;) — Asenar, Oct 30 '13 at 23:07
works exclent, I am facing only one problem that the results are not filtered by the second parameter $tipo, but I will try to figure it out why. Thank you very much! — John Siniger, Oct 30 '13 at 23:20

score 0 · Answer 3 · answered Oct 30 '13 at 22:34

0

If city and type are set then your SQL will have two instances of order by... You should add order by after the if statements.

answered Oct 30 '13 at 22:34

Gavin

2,123
1
15
19

thanks I will fix that , but in general that does not fix the problem when I hit second page, the query lists all the results not the specified ones. – John Siniger Oct 30 '13 at 22:39

Pagination Php not working when different sql query conditions are added

3 Answers3