SQL Query Help for those Interested

[wrm]

All,

Based on a number of threads regarding SQL queries in a few different forums here and some general comments about not really understanding what, why,
when or where I thought I'd attempt to help. I am not a Database Administrator but I did get to play at being one during my IT career.


The following is a script that was originally posted by Marius


-----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
ORDER BY `date_played` ASC
LIMIT 2;
------

In the SQL world this is basically a nested select statement with some conditionals added. I will simplify this and remove 4 lines that I will describe later.


----- query with 4 lines removed -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
------------------------------------------

Unless I have fat fingered something, this last SQL statement should work using HeidiSQL. Now I'll split this up into the two select(s). I will start with the inner one
because that select is what provides the data to the outer select statement.

----- inner select -----
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1
-------------------------

And here is the outer one ( Note that this select statement returns *TWO* items one being the "ID" which is what RadioDJ uses to fetch the song.

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------

If the inner select is run using HeidiSQL you should be able to see that it returns a single item. Which is an Artist.
Select `artist` FROM `songs` [ See ? this says "Show me all of the Artist fields from the songs table ]

This returns something like:

Pink Floyd
Buddy Holly
Meatloaf
Pink Floyd
etc etc etc

This result will be presented to you in the order that is finds them in the songs table. And it will display all occurrences of each artist but not in any particular order.
Now lets apply the conditional(s)


WHERE `enabled`=1 [ Only show the enabled songs ]

AND `song_type`=0 [ should be self explanatory ]

GROUP BY `artist` [ All results of a particular artist will be grouped together ]

HAVING COUNT(*) > 1 [ count(*) is a shorthand for *number of rows* so only show those results numbering more than 1 ]
This makes sense right ? How can you play two songs from an artist that only has a single song in the Database ?


ORDER BY `date_played` ASC LIMIT 1 [ This will ORDER the results displayed by *date_played* in ASCENDING (ASC) Order and only show 1 (ONE) result]

For fun try that query and change LIMIT 1 to LIMIT 10 and you'll get the idea You can also change the ASC (Ascending) to DESC (Descending)


So to recap, this simple query provides a result consisting of Artists having at least two songs in the songs table.

The outer Query then takes this artist info and operates on that. Here is the outer query again.

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------


Let's say that the Artist Name returned is "Pink Floyd"

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=`Pink Floyd`
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------

Can you see that the inner query simply provides that info to the outer query ?


That's all the simple stuff. But for completeness here are the 4 lines that I removed.

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
----

And this one too

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
----


These conditionals rely on variables not readily available to HeidiSQL. But it's easy to get around this because these variables are defined within RadioDJ in the repeat options.
So you can simply replace the variables with the actual data from your options.
Let us assume that the $TrackRepeatInterval$ is defined in your options to be 600 minutes and $ArtistRepeatInterval$ is set to 300 minutes

To use HeidiSQL to examine this query we need to replace those variables with the values. This is because HeidiSQL doesn't know about these variables. So just use their value

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 600) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 300))
----

Please be aware that this doesn't really provide a RANDOM data though for most users this might well be enough.

Using HeidiSQL you can look at all the various RadioDJ tables and the fields within each one and possibly write php scripts to form other queries that you might find useful.

Hope this helps

Bill

PS: any errors contained in this are all mine

[Capt_Fuzzy]

This is awesome!!

Someone PLEASE STICKY THIS!!

[wrm]

Here is what I am using for a twofer rotation

Since the twofer was for a "Rock" show I added

"AND `id_genre`=118"

to both select statements

------------------ begin -------------
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
AND `id_genre`=118
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
AND `id_genre`=118
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
ORDER BY `date_played` ASC
LIMIT 2;
--------------------------

[Capt_Fuzzy]

I'm using the same script that Marius wrote as well for a twofer show as well, it works great except for one small hitch.
I set mine up, or so I thought, to run on Tuesdays only, but I've discovered that it continues to add random 'twofers' all week!

I've tried a couple of things to try and get it to only play them on Tuesdays, but it still randomly loads the two tracks... 

[wrm]

I'm using the same script that Marius wrote as well for a twofer show as well, it works great except for one small hitch.
I set mine up, or so I thought, to run on Tuesdays only, but I've discovered that it continues to add random 'twofers' all week!

I've tried a couple of things to try and get it to only play them on Tuesdays, but it still randomly loads the two tracks... 

Hi Steve,

I have never witnessed this behavior. If I am understanding you correctly that is.  Mine is also done as a scheduled event.

Here are some specifics:

Event Name: TwoFer Tuesday Start

Repeat by Day - Tuesday  ( Ours starts at 8AM )

1) Stop!
2) Run Application TTS-OFF.bat
3) Load Rotation TwoFer Tuesday
4) Clear Playlist!
5) Play!


Hope that helps

Regards

Bill


PS: TTS-OFF is a .bat file that turns off TTS. I have another to turn it back on as well. If you need or want any details let me know.

[Capt_Fuzzy]

Hi Steve,

I have never witnessed this behavior. If I am understanding you correctly that is.  Mine is also done as a scheduled event.

Here are some specifics:

Event Name: TwoFer Tuesday Start

Repeat by Day - Tuesday  ( Ours starts at 8AM )

1) Stop!
2) Run Application TTS-OFF.bat
3) Load Rotation TwoFer Tuesday
4) Clear Playlist!
5) Play!


Hope that helps

Regards

Bill


PS: TTS-OFF is a .bat file that turns off TTS. I have another to turn it back on as well. If you need or want any details let me know.

Here's how I have mine:

Event Name: Two For Tuesday Start

Repeat by Day and Hour- Tuesday  ( Ours starts at 10AM & ends at 10PM )

1) Enable Events Category|1|Two for Tuesday
2) Load Track By ID|1|1822two4tuesday.mp3|Bottom

This was the only way I could come up with to make mine work...   

[TQ]

... but do you have an event to replace the rotation (after Tuesday). There is no un-load event action as far as I know.

TQ

[Capt_Fuzzy]

... but do you have an event to replace the rotation (after Tuesday). There is no un-load event action as far as I know.

TQ

Yes there is...

Disable Events Category|1|Two for Tuesday

[TQ]

Hadn't heard from you so I thought you'd fixed it

I haven't tested it but does that not disable the loading any NEW events from that category rather than REMOVE the result of a past action from an event from that category (ie. the TFT rotation)?

Maybe you could put me right on this?

TQ

[Capt_Fuzzy]

Ya know, ya might be right...
I hadn't really thought about that until you mentioned this... 

[ghm72]

I've managed to get TechMonkey's last website script he was working on up and running on a test websever on this machine.

Now here a piece of SQL code that I feel may help people.

Code Select Expand

$query1 = "SELECT * from songs where `enabled`='1' AND `song_type`=0 AND `id_subcat`NOT IN('1','59') AND ID_Subcat in (SELECT subID FROM rotations_list WHERE pID = (SELECT value from settings where setting='RotationID')) ORDER BY RAND() LIMIT 0,$random_limit";

AFAIK It displays songs in the request script from the rotation currently in use.  Subcats 1, 59 are examples.

There is also this:

Code Select Expand

$query1 = "SELECT * FROM songs WHERE  ID_Subcat in (SELECT subID FROM rotations_list WHERE pID = (SELECT value from settings where setting='RotationID')) ORDER BY count_played DESC, date_played DESC LIMIT $start, $page_limit";

I would release the whole script but alas its not complete and bits of it aren't working as intended. I hope this gives people at least a couple of pointers as to we were doing it with our scripts.

[eradiodj]

I am looking to implement this Twofer query and it works as is but I cannot figure out how to insert a Twofer sweep between the two songs.

Does anyone have any guidance on modifying this script please?

Here is what I am using for a twofer rotation

Since the twofer was for a "Rock" show I added

"AND `id_genre`=118"

to both select statements

------------------ begin -------------
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
AND `id_genre`=118
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
AND `id_genre`=118
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
ORDER BY `date_played` ASC
LIMIT 2;
--------------------------

[radiosoundfm]

Not just sql but powershell script.  It looks for changing the nowplaying txt file and then it can kick of another script or whatever...

Code Select Expand

Add-Type -Path "$(Split-Path -Parent $MyInvocation.MyCommand.Path)\MySQL.Data.dll"

[string]$sMySQLUserName = 'xxxxr'
[string]$sMySQLPW = 'xxx'
[string]$sMySQLDB = 'radiodjxxx'
[string]$sMySQLHost = 'localhost'
[string]$sConnectionString = "server="+$sMySQLHost+";port=3306;uid=" + $sMySQLUserName + ";pwd=" + $sMySQLPW + ";database="+$sMySQLDB


$oConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($sConnectionString)
$Error.Clear()
try
{
    $oConnection.Open()
}
catch
{
    write-warning ("Could not open a connection to Database $sMySQLDB on Host $sMySQLHost. Error: "+$Error[0].ToString())
}



### SET FOLDER TO WATCH + FILES TO WATCH + SUBFOLDERS YES/NO
    $watcher = New-Object System.IO.FileSystemWatcher
    $watcher.Path = "C:\twits"
    $watcher.Filter = "nowplaying.txt"
    $watcher.IncludeSubdirectories = $No
    $watcher.EnableRaisingEvents = $true 


### DEFINE ACTIONS AFTER AN EVENT IS DETECTED
    $action = {
   
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText="UPDATE history INNER JOIN songs ON ((history.artist = songs.artist) AND (history.title = songs.title)) SET history.songID = songs.id"
$iRowsAffected=$oMYSQLCommand.executeNonQuery();


In my case i fill up a extra row in request table for  making a script with request+name+message for twitter and on website.


I am NOT a powershell or dbase programmer!!!!
USE AT OWN RISC.

[Uit_Het_Raam]

I would like to verify some things.

Is it possible to select more than one genre? For example:

Code Select Expand

AND `id_genre`=1
OR `id_genre`=2

Is it also possible to filter by year this way?

Code Select Expand

AND Year > 1979
AND Year < 1990

Or by weight?

Code Select Expand

AND Weight > 26
AND Weight < 101
"

[PresidentOfACPJ]

All,

Based on a number of threads regarding SQL queries in a few different forums here and some general comments about not really understanding what, why,
when or where I thought I'd attempt to help. I am not a Database Administrator but I did get to play at being one during my IT career.


The following is a script that was originally posted by Marius


-----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
ORDER BY `date_played` ASC
LIMIT 2;
------

In the SQL world this is basically a nested select statement with some conditionals added. I will simplify this and remove 4 lines that I will describe later.


----- query with 4 lines removed -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
------------------------------------------

Unless I have fat fingered something, this last SQL statement should work using HeidiSQL. Now I'll split this up into the two select(s). I will start with the inner one
because that select is what provides the data to the outer select statement.

----- inner select -----
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1
-------------------------

And here is the outer one ( Note that this select statement returns *TWO* items one being the "ID" which is what RadioDJ uses to fetch the song.

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------

If the inner select is run using HeidiSQL you should be able to see that it returns a single item. Which is an Artist.
Select `artist` FROM `songs` [ See ? this says "Show me all of the Artist fields from the songs table ]

This returns something like:

Pink Floyd
Buddy Holly
Meatloaf
Pink Floyd
etc etc etc

This result will be presented to you in the order that is finds them in the songs table. And it will display all occurrences of each artist but not in any particular order.
Now lets apply the conditional(s)


WHERE `enabled`=1 [ Only show the enabled songs ]

AND `song_type`=0 [ should be self explanatory ]

GROUP BY `artist` [ All results of a particular artist will be grouped together ]

HAVING COUNT(*) > 1 [ count(*) is a shorthand for *number of rows* so only show those results numbering more than 1 ]
This makes sense right ? How can you play two songs from an artist that only has a single song in the Database ?


ORDER BY `date_played` ASC LIMIT 1 [ This will ORDER the results displayed by *date_played* in ASCENDING (ASC) Order and only show 1 (ONE) result]

For fun try that query and change LIMIT 1 to LIMIT 10 and you'll get the idea You can also change the ASC (Ascending) to DESC (Descending)


So to recap, this simple query provides a result consisting of Artists having at least two songs in the songs table.

The outer Query then takes this artist info and operates on that. Here is the outer query again.

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------


Let's say that the Artist Name returned is "Pink Floyd"

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=`Pink Floyd`
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------

Can you see that the inner query simply provides that info to the outer query ?


That's all the simple stuff. But for completeness here are the 4 lines that I removed.

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
----

And this one too

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
----


These conditionals rely on variables not readily available to HeidiSQL. But it's easy to get around this because these variables are defined within RadioDJ in the repeat options.
So you can simply replace the variables with the actual data from your options.
Let us assume that the $TrackRepeatInterval$ is defined in your options to be 600 minutes and $ArtistRepeatInterval$ is set to 300 minutes

To use HeidiSQL to examine this query we need to replace those variables with the values. This is because HeidiSQL doesn't know about these variables. So just use their value

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 600) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 300))
----

Please be aware that this doesn't really provide a RANDOM data though for most users this might well be enough.

Using HeidiSQL you can look at all the various RadioDJ tables and the fields within each one and possibly write php scripts to form other queries that you might find useful.

Hope this helps

Bill

PS: any errors contained in this are all mine

Need to be sticky ASAP.