Pass a value and call previous, current and next values in order

Question

I'm using PDO. I want to be able to fetch 3 values from 3 different rows using a single query. I've got a table as below.

itemID    |  Item_Name
____________________
125       |  apple
297       |  Lychee
851       |  Mango
005       |  Orange
1009      |  Strawberry

I want to be able to send an Item_Name and sort the table by Item_Name and to be able to call the next Item_Name and the previous Item_Name. How can I do this?

e.g.:

if the $passedItem is Orange I want to output Mango and Orange and Strawberry.
if the $passedItem is Mango I want to output Lychee and Mango and Orange

Using the on() operator did not work because only one Item_Name could be passed on to the query.

$sql = "SELECT Item_Name FROM itemsTable WHERE Item_Name = :passedItem" ORDER BY ASC;
$stmt = $con_db->prepare($sql);
$stmt->execute(array(':passedItem'=>"Orange"));
$rslt = $stmt->fetchAll(PDO::FETCH_ASSOC);

Maybe just get all items, fetch until you find your item, then display index -1 and index +1 ... this is a hard question since your table ID's are useless in your case... If your table is huge, that "whole fetching" solution will not be very effective... — Julo0sS, Jun 26 '15 at 12:18
@Barmar next + previous in case of id ordered rows, i'm ok with you. But his specific case with non ordered id's is different... — Julo0sS, Jun 26 '15 at 12:20
@Julo0sS It doesn't matter what you order by, the technique is the same. He says he wants to `ORDER BY Item_Name`. — Barmar, Jun 26 '15 at 12:22
Just use the same column in the `WHERE` clause as the `ORDER BY` clause. — Barmar, Jun 26 '15 at 12:23
@Barmar the solution might be easy from your standards but I find it very confusing. Could you please show it in a simplified way? — Becky, Jun 26 '15 at 12:23
Just take the answers in that question, replace `id` with `item_name` and remove the `private IS NULL` test. — Barmar, Jun 26 '15 at 12:24
@Barmar how can I output the results? `$rslt[0]["item_name"];` does not work. Could you help on that? — Becky, Jun 26 '15 at 12:31
I've posted an answer. I stupidly assumed you knew how to take a MySQL query and put it into PDO syntax. — Barmar, Jun 26 '15 at 12:34
It's case sensitive, so it should be `$rslt[0]["Item_Name"]` — Barmar, Jun 26 '15 at 12:35

score 2 · Accepted Answer · edited May 23 '17 at 12:23

This solution is based on MySQL: Conditionally selecting next and previous rows.

$sql = "(SELECT Item_Name FROM itemsTable
         WHERE Item_Name < ?
         ORDER BY Item_Name DESC
         LIMIT 1)
        UNION ALL
        (SELECT Item_Name FROM itemsTable
         WHERE ItemName = ?)
        UNION ALL
        (SELECT Item_Name FROM itemsTable
         WHERE ItemName > ?
         ORDER BY Item_Name ASC
         LIMIT 1)";
$stmt = $con_db->prepare($sql);
$stmt->execute(array("Orange", "Orange", "Orange"));
$rslt = $stmt->fetchAll(PDO::FETCH_ASSOC);

You need to pass Orange three times to fill in all the placeholders.

You can then output it with:

foreach($rslt as $row) {
    echo $row['Item_Name'] . '<br>';
}

Pass a value and call previous, current and next values in order

1 Answers1