I am currently using this query to select a media record in my PHP application and get the associated user data (if available):
SELECT media.*,
user.*
FROM media_table AS media
LEFT JOIN user_table AS user ON
(user.user_id = media.user_id)
WHERE media.media_id = {$mediaId}
What I'm interested in doing is selecting media.* for the next available media_table record and the previous media_table record. I could do this based on the media_id itself (media_table.media_id is auto increment) or there are other columns available such as media_date (this may be more accurate - it's an INT column in epoch time format).
And I would like to do this (if possible) via a join so I don't have to run multiple queries.
It reminds me of this answer: https://stackoverflow.com/a/1446831/1593325
It gave me some idea, but I couldn't get it to work (unless I ran separate queries).
Any help is much appreciated. Thank you so much for your time.