Reputation:
How can I do to get the next row in a table?
`image_id` int(11) NOT NULL auto_increment
`image_title` varchar(255) NOT NULL
`image_text` mediumtext NOT NULL
`image_date` datetime NOT NULL
`image_filename` varchar(255) NOT NULL
If the current image is 3 for example and the next one is 7 etc. this won’t work:
$query = mysql_query("SELECT * FROM images WHERE image_id = ".intval($_GET['id']));
echo $_GET['id']+1;
How should I do?
thanks
Upvotes: 1
Views: 8917
Reputation: 655707
If you want to circle the records, you could use this:
-- previous or last, if there is no previous
SELECT *
FROM images
WHERE image_id < 12345 OR image_id = MAX(image_id)
ORDER BY image_id DESC
LIMIT 1
-- next or first, if there is no next
SELECT *
FROM images
WHERE image_id > 12345 OR image_id = MIN(image_id)
ORDER BY image_id ASC
LIMIT 1
The same with UNION, might be even more efficient:
-- previous or last, if there is no previous
(SELECT * FROM images WHERE image_id < 12345 ORDER BY image_id DESC LIMIT 1)
UNION (SELECT * FROM images WHERE image_id = (SELECT MAX(image_id) FROM images))
LIMIT 1
-- next or first, if there is no next
(SELECT * FROM images WHERE image_id > 12345 ORDER BY image_id ASC LIMIT 1)
UNION (SELECT * FROM images WHERE image_id = (SELECT MIN(image_id) FROM images))
LIMIT 1
Upvotes: 7
Reputation: 50662
Please, for the love of the internet, don't built an SQL query yourself. Use PDO.
Upvotes: 0
Reputation: 37587
You're very close. Try this:
$query = mysql_query("SELECT * FROM images
WHERE image_id > ".intval($_GET['id'])." ORDER BY image_id LIMIT 1");
<?php echo $_GET['id'] ?>
Upvotes: 1
Reputation: 321806
SELECT * FROM images WHERE image_id < 3 ORDER BY image_id DESC LIMIT 1 -- Previous
SELECT * FROM images WHERE image_id > 3 ORDER BY image_id LIMIT 1 -- Next
Upvotes: 12