Clash Royale CLAN TAG#URR8PPP

PHP Pagination based on some condition

I'm new to PHP. I have android app which displays wallpapers.The following PHP script returns the data from Most recent to Most old in pagination. It is working fine but the problem is it always starts collecting rows from the end no matter what the condition is. For example if i want to retrieve the Wallpapers with category ABSTRACT from most recent to most old it starts with the last id and ignore the category and give incorrect result.
Here is what i want.

i hope you guys have understood what i really want to do. Please help me.

<?php

$conn = new mysqli('server', 'abc', '123', 'Walls');

if (!$conn)
die("Connection failed: " . mysqli_connect_error());


$page_number = $_POST["page_number"];
$item_count = 40;


$r = mysqli_query($conn, "SELECT * from wallpaper_info order by id desc
limit 1;");
$row = mysqli_fetch_array($r);
$total = $row[0];


$page_limit = ($total/$item_count) - $page_number;

$from = $page_limit * $item_count - ($item_count - 1);
$to = $page_limit * $item_count;

$response = array();
$status = array();

if($page_limit <= 0)

array_push($response,array('status'=>'end'));
echo json_encode($response);

else

array_push($response,array('status'=>'ok'));

//Working fine
$mysql_query = "select * from wallpaper_info where id BETWEEN $from AND $to
order by id desc;";
//Not working
$mysql_query = "select * from wallpaper_info where id BETWEEN $from AND $to
and category = 'Abstract' order by id desc;";

$results = mysqli_query($conn,$mysql_query);

$wallpapers = array();

while($row = mysqli_fetch_array($results))

array_push($wallpapers,
array(

'id'=>$row[0],'name'=>$row[1],'category'=>$row[2],'thumb_url'=>$row[3],'img_
url'=>$row[4],'size'=>$row[5],'downloads'=>$row[6],'views'=>$row[7]

));


array_push($response,array('wallpapers'=>$wallpapers));
echo json_encode($response);
mysqli_close($conn);


?>


1 Answer
1

$mysql_query = "SELECT * FROM `wallpaper_info`
WHERE category = 'Abstract'
ORDER BY id DESC
LIMIT 10 OFFSET 0
";


https://www.w3schools.com/sql/sql_top.asp has good examples

for page number you can try something like:

<?php
$pagesize = 10;//LIMIT
$offset = 0;//OFFSET

//pdo query example..
$sql = "SELECT FOUND_ROWS();"
$totall_rows = $conn->query($sql)->fetchColumn(0);

$calc_pages = ceil($totall_rows / $pagesize);
$last_row_number = $pagesize + $offset - 1;

if ($last_row_number >= $totall_rows)
$last_row_number = $totall_rows - 1;
if ($totall_rows > 0)
$current_page = ceil(( $calc_pages * ($offset + 1)) / $totall_rows );
?>
<form method="GET" enctype="application/x-www-form-urlencoded" action="">
Page:
<input type="submit" value="<?php echo "< ";echo($current_page - 1);?>">
<?php echo "<b>$current_page</b>/$calc_pages"; ?>
<input type="submit" value="<?php echo($current_page + 1); echo " >";?>">
</form>
<?php


didnt test it, tell if not working

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.