Navigating through the outputs from a MySQL query

Question

Navigating through the outputs from a MySQL query

There is a specific functionality I am trying to achieve, although I may not have the exact terms to describe it. Let me explain what I need in detail.

Initially, there is a variable named $id, which essentially represents $_GET['id']. For instance, if a user lands on the page by requesting: /page.php?id=6. The goal here is to fetch information about the subsequent 3 pages from the database. Below is an overview of the database structure:

  TABLE `pages`

  id   |   page_name
  ______________________
  1    |   AAAAA
  2    |   BBBBB
  3    |   CCCCC
  4    |   DDDDD
  5    |   EEEEE
  6    |   FFFFF
  7    |   GGGGG
  8    |   HHHHH
  9    |   IIIII

When requesting the page with id 6, the following script retrieves the next 3 pages (7,8,9):

 $res = mysql_query("SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` DESC LIMIT 3");
 while($arr = mysql_fetch_assoc($res))
 {
       print("Page ID: ".$arr['id']."; Page Name: ".$arr['page_name']."\n");
 }

The output for this query would be:

 Page ID: 7; Page Name: GGGGG
 Page ID: 8; Page Name: HHHHH
 Page ID: 9; Page Name: IIIII

This method performs well until the value of $id surpasses 6. In such cases, instead of displaying 3 pages, only 2 or 1 page might be returned, and no output when $id equals 9.

Hence, my query is: Is there a mechanism to loop back to the beginning when insufficient results (less than 3) are available to show?

If one were to access /page.php?id=8, the desired output should include pages with ids 9, 1, and 2.
Similarly, upon reaching /page.php?id=9, the expected result should encompass pages bearing ids 1, 2, and 3.
Moreover, navigating to /page.php?id=3 should reveal pages identified as 4, 5, 6, and so forth.

php mysql while-loop

Answer 1

Answer №1

(SELECT *, 0 AS custom_order FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3)
UNION ALL
(SELECT *, 1 AS custom_order FROM `pages` ORDER BY `id` ASC LIMIT 3)
ORDER BY custom_order, id ASC
LIMIT 3

By utilizing this method, you are guaranteed to receive a set of 3 pages. In case there aren't enough subsequent pages, up to 3 pages will be retrieved from the start.

Answer 2

(SELECT *, 0 AS custom_order FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3)
UNION ALL
(SELECT *, 1 AS custom_order FROM `pages` ORDER BY `id` ASC LIMIT 3)
ORDER BY custom_order, id ASC
LIMIT 3

By utilizing this method, you are guaranteed to receive a set of 3 pages. In case there aren't enough subsequent pages, up to 3 pages will be retrieved from the start.

Answer 3

Answer №2

To make changes to the query, you can consider rephrasing it as follows:

 SELECT * FROM 
     (SELECT *, id-$inval($id) AS order_by 
         FROM pages WHERE id > $inval($id) ORDER BY id ASC LIMIT 3
      UNION
      SELECT *, id AS order_by 
         FROM pages ORDER BY id ASC LIMIT 3 ) AS pages
 ORDER BY order_by ASC

Answer 4

To make changes to the query, you can consider rephrasing it as follows:

 SELECT * FROM 
     (SELECT *, id-$inval($id) AS order_by 
         FROM pages WHERE id > $inval($id) ORDER BY id ASC LIMIT 3
      UNION
      SELECT *, id AS order_by 
         FROM pages ORDER BY id ASC LIMIT 3 ) AS pages
 ORDER BY order_by ASC

Answer 5

Answer №3

Here is one approach to solving the problem (it's worth noting that there may be a potential issue with the resultset returning 6 records instead of just 3):

$result = mysql_query("(SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3) UNION DISTINCT (SELECT * FROM `pages` WHERE id>0 ORDER BY id ASC LIMIT 3)");
$counter = 0;
 while($array = mysql_fetch_assoc($result) && $counter<3)
 {
       $counter++;
       print("Page ID: ".$array['id']."; Page Name: ".$array['page_name']."\n");
 }

Answer 6

Here is one approach to solving the problem (it's worth noting that there may be a potential issue with the resultset returning 6 records instead of just 3):

$result = mysql_query("(SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3) UNION DISTINCT (SELECT * FROM `pages` WHERE id>0 ORDER BY id ASC LIMIT 3)");
$counter = 0;
 while($array = mysql_fetch_assoc($result) && $counter<3)
 {
       $counter++;
       print("Page ID: ".$array['id']."; Page Name: ".$array['page_name']."\n");
 }

Navigating through the outputs from a MySQL query

Answer №1

Answer №2

Answer №3

Similar questions

Guide on accessing JSON data from Google Insight API using PHP

Generate an image with PHP and then have it be sent as a response to an AJAX

looking for distinct integer values using addAjaxFilterBox within ajaxCrud

The PHP script designed to generate a hierarchical array containing all files within a directory and its subdirectories is experiencing difficulties

Ways to automatically refresh the div block's content whenever new data is added or updated in the database

Having difficulty retrieving an id from another table in Laravel

information not transferring between pages in the session

Using jQuery to send a GET request to the current page with specified parameters

Obtain the Foursquare access_token through a PHP cURL request

PHP htaccess error: Rewrite rules causing 500 ERROR for nonexistent pages

Eloquent in Laravel 5 makes it easy to retrieve all attributes from two related tables

Retrieve data from the MySQL database that has not been previously accessed

Remove Chosen Pictures (Checkbox/PHP/MySQL)

What is the proper way to pass variables for alternative if-syntax?

Creating a Basic jQuery AJAX call

Is there a way to retrieve the name associated with the ID or organize them in a specific order?

In order to obtain three different arrays as output, what is the step-by-step process for writing this query using PDO?

Using cookies for authentication in Angular 5

How to access the calling scope within a PHP function?

Leveraging Postmark in PHP applications