I have a MySQL query
SELECT * FROM 'redirect'
WHERE 'user_id'= \''.$_SESSION['user_id'].' \'
ORDER BY 'timestamp'`
I want to paginate 10 results per page. How Can I do it?
Add a comment
|
4 Answers
Here is a nice starting point:
<?php
// insert your mysql connection code here
$perPage = 10;
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$startAt = $perPage * ($page - 1);
$query = "SELECT COUNT(*) as total FROM redirect
WHERE user_id = '".$_SESSION['user_id']."'";
$r = mysql_fetch_assoc(mysql_query($query));
$totalPages = ceil($r['total'] / $perPage);
$links = "";
for ($i = 1; $i <= $totalPages; $i++) {
$links .= ($i != $page )
? "<a href='index.php?page=$i'>Page $i</a> "
: "$page ";
}
$r = mysql_query($query);
$query = "SELECT * FROM 'redirect'
WHERE 'user_id'= \''.$_SESSION['user_id'].' \'
ORDER BY 'timestamp' LIMIT $startAt, $perPage";
$r = mysql_query($query);
// display results here the way you want
echo $links; // show links to other pages
4 Comments
Francesc
But, How I can create the links to the others pages?
code_burgar
Select COUNT for all results matching your where clause, divide the number you get by your perPage value and you get the total number of pages, then just do a for loop to echo links to each page.
Francesc
But for the previous one? Can you put an example please? I'm a newvie.Thanks
code_burgar
there you go, you should really be able to take it from there
Use LIMIT.
SELECT *
FROM redirect
WHERE user_id = '35251'
ORDER BY timestamp
LIMIT 40, 10
40 is how many records to skip, 10 is how many to display.
There are also a few problems with your PHP. You use backticks (not single quotes) to surround table and column names. And you shouldn't use string concatenation to build your query.
2 Comments
code_burgar
There is nothing intrinsically wrong with using string concatenation to build an sql query if the data is known to be safe.
Mark Byers
@code_burgar: It won't give the wrong result, but it will result in a new query plan for every new set of parameters. You should use bind parameters.
Here is my code
which contains next and Previous button
<?php
$limit = 3; //set Number of entries to show in a page.
// Look for a GET variable page if not found default is 1.
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else { $page=1;
}
//determine the sql LIMIT starting number for the results on the displaying page
$page_index = ($page-1) * $limit; // 0
$All_Users=mysqli_query($con,"select * from users limit $page_index, $limit");
while($row=mysqli_fetch_array($All_Users))
{
//show data in table or where you want..
}
$all_data=mysqli_query($con,"select count(*) from users");
$user_count = mysqli_fetch_row($all_data); // say total count 9
$total_records = $user_count[0]; //9
$total_pages = ceil($total_records / $limit); // 9/3= 3
if($page >= 2){
echo "<a href='blog.php?page=".($page-1)."' class='btn
customBtn2'>Previous</a>";
}
if($page<$total_pages) {
echo "<a href='blog.php?page=".($page+1)."' class='btn customBtn2'>NEXT</a>";
}
?>
2 Comments
Pouria Hemi
Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.
Santosh Dangare
@PouriaHemati ok. I thought it will be enough for that question. also I have added commnets in the code for better understanding
Use the LIMIT clausule of the query to limit the amount of results you retrieve from the database.
