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';
}
}
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';
}