I need help getting the prev and next row using a order by with specific WHERE

[otakusan]

id   uid       show_id     season     episode            published

---

1   62       1               1               2                   1

2   64       2               1               1                       1

3      62          5                  1                    9                        1

4      62          1                  1                    3                        1

72   62       1               9               72                     1

120 62       1               9               74                        1

 

 

This is the data base i have for my tv shows now what im trying to do is get the previews and next showing at the bottom of every episode order by showid then by season and after they gotta give back the previes or next row>episode  is there anyone who can help me with this?

 

I tried so many times doing it with separate queries and all of them are just not working    for example

 

<?
    $db =& JFactory::getDBO();	
    $query="SELECT show_id FROM #__tv WHERE published= 1  and show_id < '$show_id' order by episode DESC limit 0,1 ";
	$db->setQuery($query);
	$next=$db = $db->loadResult();
         ?>

 

 

[requinix]

The best way I've found is to run two queries to get all three (previous, current, and next) rows.

One of them gets the previous and the other gets the next. Either query can get the current - doesn't matter.

 

Say the current record is the one with show_id=1 and episode=3. You can do

SELECT * FROM #__tv WHERE show_id = 1 AND episode 

[code]SELECT * FROM #__tv WHERE show_id = 1 AND episode >= 3 AND published = 1 ORDER BY episode ASC LIMIT 2

[otakusan]

Actually that was just a quick example of my database my tv table has over 3000 records  and i just wanted to give an easy example of what the database looks like it  sorry i didn't specify that part 

 

Ok so lets say im currently watching episode 4 and i wanna see whats next under the same show_id taking in consideration that the tv show is divided in seasons, so for example :

 

if there watching season 2 episode 4 the result will be for next season 2 episode 5

 

So at first the database has to organize a query to order the rows by show_id then it has to order them by season then show results from episode in DESC or ASC depending if is next or prev, now the problem is that im not really good at sql so i can't figure out how to make this kind of query 

[DavidAM]

It's pretty much the same thing, you just have to take the season into account:

 

	(SELECT * FROM #__tv 
WHERE published = 1
AND ( (season < $curSeason)
	OR ( season = $curSeason AND episode < $curEpisode) )
ORDER BY season DESC, episode DESC
LIMIT 1)
UNION
(SELECT * FROM #__tv
WHERE publishied = 1
AND ( (season > $curSeason)
	OR (season = $curSeason AND episode >= $curEpisode) )
ORDER BY season ASC, episode ASC
LIMIT 2)

 

Using a UNION lets you do it with a single call to the database.