SQL query for next and previous record?

My database structure is like this:

[id]	[cat]	  [episode]		[time]
 4	  1		01		timestamp
 2	  1		02		timestamp
 8	  1	     03 04		timestamp
 3	  1	 	05		timestamp
 5	  2		01		timestamp
 6	  2		02		timestamp
 1	  2		03		timestamp
 7	  2	     Movie		timestamp

Code (markup):

My current sql is like this

SELECT * FROM `table` WHERE `cat`='1' AND `episode`='02' ORDER BY `episode`

Code (markup):

How would I select the previous episode(01) and next episode(03 04) as well?

 

If you want separate queries, then this could work
For previous episode(01)

SELECT * FROM `table` WHERE `cat`='1' AND `episode`< '02' ORDER BY `episode` DESC LIMIT 1

For next episode(03 04)

SELECT * FROM `table` WHERE `cat`='1' AND `episode`> '02' ORDER BY `episode` LIMIT 1

 

As you can see the episodes are not all integers, so it won't work.

 

I think you need to restructure the episode data, or else (simpler) add a new column ('episode_order') with only integers in it corresponding to the desired order of the episodes. Then in the above queries, replace 'episode'<'02' with 'episode_order' < n (where n = episode_order value corresponding to '02'), and 'episode'>'02' with 'episode_order' < n. If you need all 3 rows from the same single query, then your SQL gets more complicated. Do you need it that way?

 

Oops - sorry - second replacement should be 'episode_order' > n.

 

Ah I see, thank you for the suggestion, never thought of that way, thanks rep added