Random Select from category and then play in series

[Jhonny]

Correct me if I'm wrong but you  use  radiodj for dance lessons?
if so from me 
Then i understand the sql query's

[Tony316]

Correct me if I'm wrong but you  use  radiodj for dance lessons?
if so from me 
Then i understand the sql query's

No no they are just physics and math lessons (boring I know). Also I have made my script a bit more readable by setting variables and also because the last one I sent was taking some time to execute. Here is the new script:

Code Select Expand


-- Define subcategory name
SET @id_subcat_name = '<subcategory_name>';

-- Define subcategory id number
SET @id_subcat_no :=
(
    SELECT id
    FROM subcategory
    WHERE name = @id_subcat_name
);

-- Find most recently played track_no
SET @current_track :=
(
    SELECT track_no
    FROM songs
    WHERE id_subcat = @id_subcat_no
    AND
    enabled = 1
    ORDER BY date_played DESC
    LIMIT 1
);

-- Find track_no of next track to be played
SET @next_track :=
(
    SELECT track_no
    FROM songs
    WHERE id_subcat = @id_subcat_no
    AND
    enabled = 1
    AND
    track_no > @current_track
    ORDER BY track_no ASC
    LIMIT 1
);

-- Find smallest track_no of all tracks
SET @first_track :=
(
    SELECT track_no
    FROM songs
    WHERE id_subcat = @id_subcat_no
    AND
    enabled = 1
    ORDER BY track_no ASC
    LIMIT 1
);

-- Find biggest track_no of all tracks
SET @max_track :=
(
    SELECT track_no
    FROM songs
    WHERE id_subcat = @id_subcat_no
    AND
    enabled = 1
    ORDER BY track_no DESC
    LIMIT 1
);

-- Select track to be played
SELECT id, title, track_no
FROM songs
WHERE id_subcat = @id_subcat_no
AND
enabled = 1
AND
track_no = IF ( @current_track >= @max_track, @first_track, @next_track );