I read through other similar questions before, but none seems to have answered my question. Apologies if this is a repeat of something else, but I couldn't seem to find this question answered elsewhere.
I've got a database table that looks more or less like this:
ID | URL | Title | Episode | Sequence | Created_at | Modified_at
These are used to assign specific links to episodes of podcasts, and they're displayed in the order illustrated by the Sequence. I've built a Laravel framework that interacts with the table, and I'm able to change the order of things by manipulating the sequence, but I'm having trouble removing things and maintaining the sequence for the purposes that I want it. Say I have the following four things in the table:
id = 5, sequence = 1
id = 6, sequence = 3
id = 7, sequence = 2
id = 8, sequence = 4
When viewing the site, the page would list them in id order 5, 7, 6, 8. However, if I realize that there's been a problem with one of the links, I would remove it, at which point I might be left with ids 5, 7, 8. The sequence would be 1, 2, 4.
What I'm looking for at the moment is a way of "reseeding" the sequence column on a per-episode basis. It'd be something that basically takes all rows with a specific episode, sorts them in ascending order on sequence, and then "renumbers" them in ascending order. The sequence = 1, 2, 4, 5, 7, 8, 9 would change to 1, 2, 3, 4, 5, 6, 7.
It should be a relatively simple query such as
select
YT.*
from
YourTable YT
where
YT.Episode = 'MO50'
order by
YT.Sequence
Now, if your sequences are creating gaps for whatever reason you could always implement a mySql variable to increment regardless of the ACTUAL sequence.. Try..
select
@tmpRow := @tmpRow +1 as NewSequence,
YT.*
from
YourTable YT,
( select @tmpRow := 0 ) sqlvars
where
YT.Episode = 'MO50'
order by
YT.Sequence
So, even if your actual sequences for a given show "MO50" are 2, 3, 8, 12, 15. The actual column "NewSequence" would show as 1, 2, 3, 4, 5.
Now, that said, you would STILL have the original "ID" column as the basis of editing any details and or remove episodes as needed... Does this help you?
I don't know if there is a built-in feature that does what you need elegantly.
Here is a dumb way of doing it.
Suppose you want to move id=8 from sequence 4 to 2.
UPDATE episodes
SET sequence = sequence+1
WHERE episode='Episode Name'
AND sequence BETWEEN 2 AND 4;
UPDATE episodes
SET sequence = 2
WHERE id = 8;
You want to put this in a transaction always to ensure your sequence does not get out of kilter.
The sequence+1 is for moving an item UP the list. If you are moving down it would be -1.
This also means you must maintain the sequence if you delete a row. So if you delete something you have to shift everything underneath it up one. Do that in a transaction too.
Where you might have a problem with this is if you have a unique key spanning the episode and sequence columns. Which you ought to strictly. You would have to assign a temporary sequence value on the row you are shifting first to avoid a duplicate key error.