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
}
?>
The 2 problems where:
$_POST will be empty)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.'&ciudad='.$ciudad.'&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.'&ciudad='.$ciudad.'&tipo='.$tipo; ?>"><?php echo $i;?></a></li>
<?php
}
?>
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.
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