当添加不同的sql查询条件时，Pagination Php不起作用

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           
          }
        ?>

Answer 1

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           
          }
        ?>