I have a table called "Ev_Songs" which has these columns:
id (Primary)
id_Event (int)
id_Song (int)
Pos (int)
This table hold selected songs for a song set for an event. So if I have an Event thats id=3 there might be 5 songs selected as the set for that event:
id=1 , id_Event=3, id_Song=344, Pos=1
id=2 , id_Event=3, id_Song=454, Pos=2
id=3 , id_Event=3, id_Song=652, Pos=3
id=4 , id_Event=3, id_Song=909, Pos=4
id=5 , id_Event=3, id_Song=14, Pos=5
Notice the Pos column. That is the order in which the Song will be performed.
I need to establish ADD Song, DELETE Song, MOVE UP Song, and MOVE DOWN Song procedures.
So if I:
ADDED a song - It would count the existing related records, capture that count PLUS 1(6), add the record with the value 6 in the Pos column.
Return to List View Display which will re-sort
DELETED a song - Say I delete id=3, it would DELETE record, Find the related records sorted by POS, then REINDEX them so they would now be:
id=1 , id_Event=3, id_Song=344, Pos=1
id=2 , id_Event=3, id_Song=454, Pos=2
id=4 , id_Event=3, id_Song=909, Pos=3
id=5 , id_Event=3, id_Song=14, Pos=4
Return to List View Display which will re-sort
MOVE UP a Song - would have logic that would say if Pos=1, you can't move up ELSE:
Capture current record id and Pos value
Find related record where Pos=Current record Pos MINUS 1
Update that record to it's POS PLUS 1
Back to the original record and update it's value Pos MINUS 1
Return to List View Display which will re-sort
MOVE DOWN a Song - would have logic that would say if Pos=CountOfALLRelatedRecords, you can't move down ELSE:
Capture current record id and Pos value
Find related record where Pos=Current record Pos PLUS 1
Update that record to POS MINUS 1
Back to the original record and update it's value Pos PLUS 1
Return to List View Display which will re-sort
I have the logic in my head obviously, but since I am new to PHP/mySQL, I figured I would get some feedback prior to trying to coding it. I have not did any MATH in PHP yet.
Appreciate any feedback that can be offered. Thanks!!!