Program Year-Based Jingles & Songs in Rotations (SQL Magic!)

[JMac]

I'll show you two ways to program year-based production elements (jingles, sweepers, liners) and songs into your RadioDJ rotations. What? Let's say you have a jingle that features 1985 and you'd love RadioDJ to play a song from 1985 after it. Done!

Awesome for decade-based radio stations OR great for anyone running a flashback feature. The two methods on offer will kick your on-air sound to the next level. (If you run an 80s station, that would be Level 42!)

Check out the tutorial!

Here's the SQL Magic! Code referenced in the video:

#Year Based Jingle_Song
SET @pick_year := (SELECT `songs`.`year` FROM `songs` WHERE `songs`.`id_subcat` = 44 AND `songs`.`year` BETWEEN 1980 AND 1989 ORDER BY `songs`.`date_played` ASC LIMIT 1);

SELECT id, title, artist FROM (
    SELECT `songs`.`id`,`songs`.`title`, `songs`.`artist` FROM `songs`
    WHERE `songs`.`id_subcat` = 44 AND `songs`.`year` = @pick_year
    ORDER BY `songs`.`date_played` ASC
    LIMIT 1
) AS jingle
UNION ALL
SELECT id, title, artist FROM (
    SELECT `songs`.`id`,`songs`.`title`, `songs`.`artist` FROM `songs`
    LEFT JOIN `queuelist` ON `songs`.`artist` = `queuelist`.`artist`
   WHERE`songs`.`enabled` = 1
AND `songs`.`song_type` = 0 AND `songs`.`id_genre` = 143 AND `songs`.`id_subcat` = 2 AND `songs`.`year` = @pick_year
AND ((TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) "greater than sign" $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) "greater than sign" $ArtistRepeatInterval$))
    AND  `queuelist`.`artist` IS NULL
    ORDER BY `songs`.`date_played` ASC
    LIMIT 1
) AS song;

*Variables to change:
id_subcat = (this is the number of your specific jingle & song subcategory)
id_genre = (this is the number of your music genre)
BETWEEN year AND year = Set to your liking
"greater than sign" = YouTube doesn't allow angled brackets in descriptions. Replace this with the greater than symbol.

For XXXtreme commercial-free 80s FUN...and to hear totally rad year-based flashbacks...check out the #4263rd popular 80s online station, XXX80s.com

[Polar]

Hm... I'm having some difficulties here ...

What I have:

I use "year" for the album release date. This can also be a "digital remastered" re-release date, and has nothing to do with the actual original date. MusicBrainz puts this album/single release date in here, so I just keep it there.

But I use the field "copyright" for the year the track was released in the charts. The db field structure is also VARCHAR, so this should work.

I don't use genres. But I have "decades" subcategories. For me this is

70s - ID34
80s - ID35
90s - etc...

The year jingles are subcat ID88.

My original SQL is working fine without the year jingle trick. It looks like this:

# Gold (90s + 80s + 70s) Least Recently Played Track


SELECT * FROM (
      # 90s Least Recently Played Track
      (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played` FROM `songs`
      LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)

    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0

    AND `songs`.`id_subcat` = 36
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL


    ORDER BY `date_played` ASC
    LIMIT 1)


  UNION
    # 80s Least Recently Played Track
    (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played` FROM `songs`
    LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)

    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0

    AND `songs`.`id_subcat` = 35
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL


    ORDER BY `date_played` ASC
    LIMIT 1)

  UNION
    # 70s Least Recently Played Track
    (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played` FROM `songs`
    LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)

    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0

    AND `songs`.`id_subcat` = 34
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL


    ORDER BY `date_played` ASC
    LIMIT 1)


 
  ORDER BY `date_played` asc
  LIMIT 3
)
temp
ORDER  BY RAND ()
LIMIT 1;


So basically pick one from the 90s, one from the 80s and one from the 70s. Sort them random, and finally pick one to play.

Now, putting your SQL to the test, I get a message "The query didn't returned any content, or it contains errors". This is what I have coded:

# Gold (90s + 80s + 70s) Least Recently Played Track

#Year Based Jingle_Song
SET @pick_year := (SELECT `songs`.`copyright` FROM `songs` WHERE `songs`.`id_subcat` = 88 AND `songs`.`copyright` BETWEEN 1970 AND 1999 ORDER BY `songs`.`date_played` ASC LIMIT 1);

SELECT id, title, artist FROM (
    SELECT `songs`.`id`,`songs`.`title`, `songs`.`artist` FROM `songs`
    WHERE `songs`.`id_subcat` = 88 AND `songs`.`copyright` = @pick_year
    ORDER BY `songs`.`date_played` ASC
    LIMIT 1
) AS jingle
UNION ALL
SELECT * FROM (
      # 90s Least Recently Played Track
      (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played` FROM `songs`
      LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)

    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0
    AND `songs`.`copyright` = @pick_year
    AND `songs`.`id_subcat` = 36
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL


    ORDER BY `date_played` ASC
    LIMIT 1)


  UNION
    # 80s Least Recently Played Track
    (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played` FROM `songs`
    LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)

    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0
    AND `songs`.`copyright` = @pick_year
    AND `songs`.`id_subcat` = 35
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL


    ORDER BY `date_played` ASC
    LIMIT 1)

  UNION
    # 70s Least Recently Played Track
    (SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played` FROM `songs`
    LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)

    WHERE `songs`.`enabled` = 1
    AND `songs`.`song_type` = 0
    AND `songs`.`copyright` = @pick_year
    AND `songs`.`id_subcat` = 34
    AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01')
    AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01')
    AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$)
    AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$)
    AND  `queuelist`.`artist` IS NULL


    ORDER BY `date_played` ASC
    LIMIT 1)


 
  ORDER BY `date_played` asc
  LIMIT 3
)
temp
ORDER  BY RAND ()
LIMIT 1;

When I replace the SET line with "SET @pick_year = 1990;" or "SET @pick_year = '1990';" I still get the same error. In HeidiSQL I can run this filter "id_subcat = 88 AND copyright = 1999", and it will give me the correct jingle.

In HeidiSQL "song_type = 0 AND id_subcat = 36 AND copyright = 1999" gives me a selection of suitable tracks.

Is there a genius in the room that can see what is going wrong?

[RadioPatrick]

I expect it's because you aren't using the ":=" operator in your SET statement. At least in MySQL (I'm not entirely sure if this goes for HeidiSql too, I'd need to look it up), but the single equals sign is for comparison, not storing a value.

[JMac]

@Polar Sorry you're having issues. I pasted your code into RDJ to test it. I'm NO SQL expert...more of a bonafide SQL dummy...Radio Patrick is the expert. BUT, I did spot some issues. To be clear, the code uses the jingle to set the year for what song is selected. You need to have jingles with year data for your date range. Since this is how it works, you only need one section of code after the jingle section. Once the year has been set, it will then select a song from your library to match. You don't need three separate sections of code for each decade.

Try this...first modify with your info and change 'year' to 'copyright':

#Year Based Jingle_Song
SET @pick_year := (SELECT `songs`.`year` FROM `songs` WHERE `songs`.`id_subcat` = 88 AND `songs`.`year` BETWEEN 1970 AND 1999 ORDER BY `songs`.`date_played` ASC LIMIT 1);

SELECT id, title, artist FROM (
    SELECT `songs`.`id`,`songs`.`title`, `songs`.`artist` FROM `songs`
    WHERE `songs`.`id_subcat` = 88 AND `songs`.`year` = @pick_year
    ORDER BY `songs`.`date_played` ASC
    LIMIT 1
) AS jingle
UNION ALL
SELECT id, title, artist FROM (
    SELECT `songs`.`id`,`songs`.`title`, `songs`.`artist` FROM `songs`
    LEFT JOIN `queuelist` ON `songs`.`artist` = `queuelist`.`artist`
   WHERE`songs`.`enabled` = 1
AND `songs`.`song_type` = 0 AND `songs`.`id_subcat` IN ('34','35','36') AND `songs`.`year` = @pick_year
AND ((TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) "greater than sign" $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) "greater than sign" $ArtistRepeatInterval$))
    AND  `queuelist`.`artist` IS NULL
    ORDER BY `songs`.`date_played` ASC
    LIMIT 1
) AS song;

I tested this and it works. Let me know if does the trick.

[JMac]

In the song section, experiment with changing this:
ORDER BY `songs`.`date_played` ASC
to
ORDER BY RAND()

I got a better variety of songs from different subcats.

[Polar]

Thx !!! Couldn't spot what you did different other then grouping the decades. But alright ! It's working now  . What needs to be done now is to give it some additional glitter. Right now it is the sung year jingle followed by the music track. I need to have a look on how to pick a sweeper, then the year jingle, then the music track, all in one go...

Thanks for this great find !

[JMac]

Awesome! Happy's it working. I actually have some code for what you're looking to do.

If you haven't checked out my YouTube channel, give it a spin! It's all RadioDJ, all the time!
RadioDJ Dude

[Polar]

I see one problem. A problem that will occur in both basic rotations and sql rotations, like in your script. Let me try and explain in short:

When I was experimenting a few years ago with jingles/sweepers before a track, I noticed that when no track is available (used all files within a criteria like f.ex no repeat with 3 hours), the sweeper plays anyway. But followed by another track, next in the playlist, with an entirely different criteria. There was no way to control this, so I stopped using playing a static sweeper related to the following track.

Your SQL has the same problem. Pick a year, then find a year jingle, then find a track from the same year, play them. I have this situation: The SQL pulled 1984 out of the hat, it finds the year jingle. But next in the playlist is a song from the "Billboard Adult top 30 new". So I checked the database, and indeed. There were no available tracks with tag 1984 (I just started editing the 80s files).

So, the way this would become bullet proof is to do a kind of control:

- Pick year
- select year jingle
- select track
- add to playlist
-- if no track available
-- then pick new year
-- pick new track
-- if all good, add them to the play list
-- else ... well, you see what I am trying to do here...right?

All this to avoid to have a sung year jingle 1984 (tataaa) followed by a new 2023 hit that entered Bilboard this week. For the specialists among us, who does like a challenge 

[JMac]

Yes, you need to make sure you have enough songs from each year. I run an 80s station, so that's not an issue. The code has been running for over a month now without snags...because I have a bevy o' songs from 1980-1989. One way to mitigate this is, at first, only load year jingles you know have enough corresponding songs. It doesn't make sense to load a 1982 jingle without any songs from 1982. Good luck!

[linawebradio]

Dear Folks!
It seems that

SET @var := (string)
nor SET @var := value

commands don't work for me! 

I am using:
Windows 10
MySQL 5.6
RadioDJ 2.0.0.6 FREE

Can anyone help me, please?

[RadioPatrick]

If you're trying to store a string, make sure you have either double (") or single (') quotes around the string text, otherwise MySQL is going to try and treat the text like a something else.

 -- Patrick

[linawebradio]

If you're trying to store a string, make sure you have either double (") or single (') quotes around the string text, otherwise MySQL is going to try and treat the text like a something else.

 -- Patrick

I get error even if i set

SET @variable := (SELECT column FROM table [...] );

;(

[RadioPatrick]

Are you trying to modify the SQL, or is it not working at all as-is? If you're trying to modify it, could you share what modification you're going for, or the actual query portion you wrote? I'll be able to help a lot more effectively.

 -- Patrick

[linawebradio]

I think the problem is that i was trying to set a parameter's value who is varchar(255) and comparise with a int(11) parameter.

I will explain better:

I have some voices that will launch a song. I put in ISRC parameter (that is a varchar(255)) the same number of the id of the song they are connected (it's a int(11)).

Example: Voice01 has isrc=1276 and the id of the song it announces is id=1276.

The query is

SET @pick_id := (SELECT isrc FROM songs WHERE id_subcat = 102 AND enabled = 1 ORDER BY date_played ASC LIMIT 1);

SELECT id, title, artist FROM
            (   SELECT id, title, artist FROM songs
                WHERE id_subcat = 102
                AND isrc = @pick_id
            ) AS launch_voice

UNION ALL

SELECT id, title, artist FROM
            (   SELECT id, title, artist FROM songs
                WHERE id = @pick_id
            ) AS song

[RadioPatrick]

Ahh yeah, that'll do it.

You'd want to change the query to convert between the varchar (which is the SQL name for a string) and an integer. All you'd really need to do is change

Code Select Expand

WHERE id = @pick_id to be

Code Select Expand

WHERE id = CAST(@pick_id AS UNSIGNED). I don't have my RDJ database in front of me to test this, but it should work for you. If not, change the UNSIGNED to SIGNED and give it a try, or shoot me another message on here.

 -- Patrick