get previous next row using PDO to create links

Question

i've got some bad sytax in my query where i'm trying to create 'prev_id' to pull into the $row result link. any ideas on a better way of doing this?

edit: the error is "Check the manual that corresponds to your MySQL server version for the right syntax to use near 'prev_id'."

function traversePhoto($the_selected_id) {
global $pdo;

$id = $the_selected_id;

$stmt_a = $pdo->prepare("
    (SELECT * FROM images WHERE id < '.intval($id).' ORDER BY id DESC LIMIT 1)
     UNION (SELECT * FROM images WHERE id = (SELECT MAX(id) FROM images)) LIMIT 1 prev_id");
$stmt_b = $pdo->prepare("
    (SELECT * FROM images WHERE id > '.intval($id).' ORDER BY id ASC LIMIT 1)
     UNION (SELECT * FROM images WHERE id = (SELECT MIN(id) FROM images)) LIMIT 1 next_id");

// $vars = array(':id' => $id);
$prev = $stmt_a->execute();
$next = $stmt_b->execute();

if ($prev) {
    while($row = $stmt_a->fetchObject()) {
        echo '<a href="' . $row['prev_id'] . '">Previous</a>';
    } 
} else {
    echo 'no previous';
}

if ($next) {
    while($row = $stmt_b->fetchObject()) {
        echo '<a href="' . $row['next_id'] . '">Next</a>';
    }
} else {
    echo 'no next';
}

}

you can try storing the query result in sessions and work with that tracking where is current position of user,etc .. instead of recalculate what's previous,next,last etc, on every page request — Gntem, Aug 17 '12 at 16:39
@jessicaldale: Yes, don't use `global`. They are *evil* and break encapsulation: [Exhibit A](http://stackoverflow.com/a/5166527/420001) | [Exhibit B](http://my.opera.com/zomg/blog/2007/08/30/globals-are-evil) — Josh, Aug 17 '12 at 17:05

Green Black · Accepted Answer · 2012-08-17T18:28:27.860

2

Try it like this

<?php function traversePhoto($the_selected_id) {
global $pdo;

$id = $the_selected_id;

$stmt_a = $pdo->prepare("
(SELECT * FROM images WHERE id < ? ORDER BY id DESC LIMIT 1)
 UNION (SELECT * FROM images WHERE id = (SELECT MAX(id) FROM images)) LIMIT 1");
$stmt_b = $pdo->prepare("
(SELECT * FROM images WHERE id > ? ORDER BY id ASC LIMIT 1)
 UNION (SELECT * FROM images WHERE id = (SELECT MIN(id) FROM images)) LIMIT 1");

// $vars = array(':id' => $id);
$prev = $stmt_a->execute(array( (int)$id ));
$next = $stmt_b->execute(array( (int)$id ));

if ($stmt_a->rowCount() > 0) {
    while($row = $stmt_a->fetch(PDO::FETCH_ASSOC)) {
        echo '<a href="' . $row['prev_id'] . '">Previous</a>';
    } 
 } else {
    echo 'no previous';
}

if ($stmt_b->rowCount() > 0) {
   while($row = $stmt_b->fetch(PDO::FETCH_ASSOC)) {
      echo '<a href="' . $row['next_id'] . '">Next</a>';
  }
} else {
echo 'no next';
}

edited Aug 17 '12 at 18:28

answered Aug 17 '12 at 16:39

Green Black

5,037
1
17
29

1

it is fetch_assoc. It is better to use this this way, but I got lazy: `$sth->fetch(PDO::FETCH_ASSOC);` – Green Black Aug 17 '12 at 16:46
thanks John - it still does not like my use of 'prev_id' and 'next_id' in my query. – jessicaldale Aug 17 '12 at 16:48
Sorry, my bad. It should be `$stmt_a->rowCount()`, not $prev->rowCount(). I edited my post. – Green Black Aug 17 '12 at 18:04
I cannot see what your tables look like. But I think you should replace `$row['next_id']` and `$row['prev_id']` by `$row['id']` – Green Black Aug 17 '12 at 18:21
i was trying to create 'next_id' (as an Alias??) from the sql query to be used to create the link href. does that make sense? – jessicaldale Aug 17 '12 at 18:23
No, that make no sense that way, sorry I did not see that last piece of your sql code. You can use `SELECT id as next_id FROM` if you like it that way. And you should enable errors so you can see what is going on. – Green Black Aug 17 '12 at 18:30
ah, makes sense. so i can wrap a SELECT id as next_id around each query. – jessicaldale Aug 17 '12 at 18:34
I don't know what you ment by wrap. But here is the complete query: `(SELECT `id` as `next_id` FROM images WHERE id > ? ORDER BY id ASC LIMIT 1)`. – Green Black Aug 17 '12 at 18:35

get previous next row using PDO to create links

1 Answers1