Skip to Content
It is currently January 22nd, 2020, 3:07 pm

All times are UTC - 6 hours [ DST ]




Post new topic Reply to topic  [ 12 posts ] 
Author Message
PostPosted: April 8th, 2014, 9:41 am 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
So I've been trying to figure out a way to reduce song request counts in the DB on a daily basis via a PAL script and Im just not quite sure how to script it.

On top of the scripting issue I'm also trying to figure out the best way to degrade the song requests count to keep a top 10 most requested songs set fresh and current. I was thinking a daily reset but that might be too drastic so I was then thinking that maybe a graduated degradation might be the best to way handle it but I'm also thinking that just a one size fits all approach is the best way so its a steady rise and steady decline. Any opinions?

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 8th, 2014, 9:49 am 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
I'm thinking something like this would work. However I'm not if the syntax is correct.

Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0);

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 8th, 2014, 10:29 am 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7902
Location: Aachen (Germany)
The syntax looks fine. Both PAL- and SQL-wise.

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: April 8th, 2014, 2:50 pm 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
It was missing a few items, was missing a second variable [] and a closing '.

Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0',[]);

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 8th, 2014, 2:59 pm 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
Okay so my previous post works but I'm not happy with it. Id like to add 1 more restriction to the sql command and require it to check the last_requested and only apply the degradation if the song hasn't been requested in 30 days. Any ideas with this?

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 8th, 2014, 3:38 pm 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
This executes but doesn't seem to affect the old requests.

Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0 AND last_requested > ADDDATE(last_requested , INTERVAL 30 DAY)',[]);

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 8th, 2014, 5:27 pm 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7902
Location: Aachen (Germany)
Your code checks if a song was requested 30 days after it was last requested. (Do you see the problem?)

This is probably what you are looking for:
Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0 AND last_requested >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)',[]);

This code affects only those tracks that were at least requested once in their lifetime and where the last_requested date is AFTER or EQUAL TO the date that was A MONTH AGO TODAY.

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: April 8th, 2014, 6:15 pm 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
I see it now... thanks again for the correction

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 11th, 2014, 9:15 am 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
after testing, this wasn't doing what I needed. The sql command was doing the exact opposite of what I needed

Original:
Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0 AND last_requested >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)',[]);


Revised and working as intended:
Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0 AND last_requested <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)',[]);

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 11th, 2014, 10:18 am 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7902
Location: Aachen (Germany)
Original:
Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0 AND last_requested >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)',[]);

UPDATE only those that have at least 1 request and were last_requested after $today-1 month$
or shorter: Update those that were requested in the last month

Revised and working as intended:
Code:
ExecSQL('UPDATE songlist SET count_requested = count_requested - 1 WHERE count_requested > 0 AND last_requested <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)',[]);

UPDATE only those that have at least 1 request and were last_requested BEFORE $today-1 month$
or shorter: Update those that were requested more than a month ago

I understood your request as if you were looking for the first and wanted to update the request count for the latest requests and not for the oldest requests.

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: April 11th, 2014, 12:25 pm 
Offline
Experienced User
Experienced User

Joined: March 26th, 2014, 7:47 pm
Posts: 291
Location: New Jersey
I was trying to degrade the oldest requests so they don't get picked up for a top 10 most requested show so that the show is current and fresh. Thanks again for the assist with it.

_________________
[INSERT]Signature Here[/INSERT]


Top
 Profile  
Reply with quote  
PostPosted: April 11th, 2014, 1:09 pm 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7902
Location: Aachen (Germany)
Ok :o , then it makes sense that my code did the wrong thing. I understood it the other way around :?

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC - 6 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group