CODEWITHSUNDEEP
phpmysql
Becky
Becky

Reputation: 5595

Get next and previous records within the same query

I'm having this PDO query to call data from a MySQL.

$sql = "SELECT itemName FROM furniture WHERE itemID = :item";

While calling for this particular itemName, is it possible to get the next and previous itemNames by using its itemID within this same query itself without having to write a new query to get the next and previous itemNames?

e.g. if 

itemID   |   itemName
___________________________
553      |   Mahogani Black Chair
554      |   Teak Round Table
555      |   Thulang Relaxing Chair
556      |   Teak Relaxing Chair

$sql = "SELECT itemName FROM furniture WHERE itemID = :item";
$stmt = $connect->prepare($sql);
$stmt->execute(array(':item'=>"554"));
$rslt = $stmt->fetchAll(PDO::FETCH_ASSOC);

I'm looking away of getting Teak Round Table and Mahogani Black Chair and Thulang Relaxing Chair

Upvotes: 8

Views: 183

Answers (2)

Dimos
Dimos

Reputation: 8928

$sql = "SELECT itemName FROM furniture WHERE itemID IN (:item-1, :item, :item+1) ORDER BY itemID";

For iterating the results, you can also use PDO fetch() function to get each row.

Upvotes: 1

Ramki
Ramki

Reputation: 519

Please use this code:

(SELECT itemName FROM furniture WHERE itemID < 554 order by itemID desc limit 1) 
UNION
(SELECT itemName FROM furniture WHERE itemID >= 554 order by itemID asc limit 2)

For Example code :

MyTable:
================
id  Store_name
================
1   English
2   French
3   Tamil
4   Uk
5   US

<?php  
$con = mysqli_connect("localhost","root","ramki","ramki");
$sql = "(SELECT store_name FROM store WHERE id < 2 order by id desc limit 1) 
        UNION
        (SELECT store_name FROM store WHERE id >= 2 order by id asc limit 2)";
$query = mysqli_query($con,$sql);
while ($row = mysqli_fetch_assoc($query)) {
echo $row['store_name'];
echo "<br>";
}
?>

Upvotes: 2

Related Questions