A doubly-linked list is a classic data structure, something you can find out more about in any data structures text. Basically, each item contains 'pointers' to the preceding and following items. In a typical programming exercise (e.g. in C) these pointers would be literal pointers to the nodes. To emulate this in a sql database, you would first create a unique id (autonumber is perfectly fine for this) for each item. Then, you create a linking table that specifies, for each item, what its predecessor and successor items are.
create table slide
(
id int primary key auto_increment,
file text // name of jpeg file on disk
// many other columns here
);
create table show
(
id int primary key auto_increment
// other columns here...
);
create table build_list
(
show_id int, // references show.id
node int, // references slide.id of the actual slide
prev int, // references slide.id of previous slide, 0 if this is the first slide
next int // references slide.id of next slide, 0 if this is the last one
);
To add a slide to the list, you need to know where it goes in relation to the rest of the show, so you need to already know the id's of the slides it's going between. For the start/end, you use 0 for the ID.
To remove a slide from the list, you first find its predecessor and successor, and point them to each other. Then delete the row containing the slide.
Moving a slide is just the composite of these two operations.
Yes, it's a lot of work to add, delete, or move a slide, but the important thing is, it's completely independent of how long the show is. Inserting a new slide to a show that contains 2,000 slides is no slower than for one that has only 2.
Displaying the slides in sequence requires one query per slide. First initialize your $next variable to zero, then loop on the following:
select slide.id. slide.file, build_list.prev, build_list.next
from slide, build_list
where build_list.show_id = $show_id and build_list.node = $next
and build_list.node = slide.id
