How to get next and previous rows in database using php MySQL
⧉ Category: Programing | 📅 Date: 17 Mar 2024 | अ🉀 Translation:
Why do you need to get next and previous rows from a database? In my case, I need to know the next and previous rows of the current row to place 'Previous Post' and 'Next Post' buttons on my website.
When a user reaches the bottom of a post he searches for more posts and I need these buttons to navigate the user to other posts.
How i did it
MySQL has an operator called UNION that combines multiple SELECT statements into one statement. By adding a few conditions to the statement, you can select only the previous and next records from the list.
Here is an Example
Two statements requesting data from the same table called "post_table". By using WHERE and LIMIT clauses to limit the extraction to only one from each side. Make sure to sort one statement with DESC order and the other statement with ASC order.
$current_post_id = "10";
$sql = "(SELECT post_id FROM post_table WHERE post_id > {$current_post_id} ORDER BY post_id ASC LIMIT 1) UNION (SELECT post_id FROM post_table WHERE post_id < {$current_post_id} ORDER BY post_id DESC LIMIT 1)";
if(!$query = mysqli_query($connection, $sql)) { die(mysqli_error($connection)); }
When the data is taken, you can validate the data. If 'post_id' is less than $current_post_id, it is the previous post. If 'post_id' is higher than $current_post_id, it is the next post. You can use html tags to make previous and next buttons.
$navigation = "";
while($result = mysqli_fetch_array($query)) {
$post_id = $result_page_selection['post_id'];
if($result['post'] < $current_post_id) {
$navigation .= "<a href='/?page={$post_id}' title='previous post'><b><</b >[Previous Post]</a>";
}
if($result['post'] > $current_post_id) {
$navigation .= "<a href='/?page={$post_id}' title='next post'><b>></b >[Next Post]</a>";
}
}
echo $navigation;
«[Previous Post] Create an image using phpHow to change server timezone to local timezone in.. [Next Post]»
Comments
Please solve above captcha based on the hint given..