Subsonic Forum

by **CasN** » Mon Apr 17, 2017 10:20 am

Created a small php script which will send the added media of last week to your users (as a BCC).
Running fine with 6.1Beta2 in combination with a mysql database.
Just make some changes under database and mail settings and you are done.
Manually you run :
php zzzz.php
zzzz = whatever you call the script.
I cretaed a cron job for it, so it goes automatically.
Enjoy :mrgreen:

Cas

<?php
// database settings
$username = "subsonic";
$password = "xxxxxx";
$hostname = "localhost";
// Mail settings
$from = "xxx@xxx.com";
$to = "xxx@xxx.com";
$subject ="Music-News from xxx.com";

//
// No changes required below this line
//

// Database connection
$mysqli = new mysqli($hostname, $username, $password, "subsonic")
or die("Unable to connect to MySQL");

// some definitions
$returnpath = "-f$from" ;
$headers = "From: $from"."\r\n";
$headers .= "Return-Path: $from"."\r\n";

// did we upload any new media/music last week
$result1 = $mysqli->query("SELECT music_folder.name as folder,artist,album.name as album, year,genre, created FROM album,music_folder WHERE album.folder_id=music_folder.id and album.created> (curdate()-7) order by music_folder.name,artist,album.name");
$num_rows = mysqli_num_rows($result1);
if ($num_rows <1){
return;
}

// get the email addresses
$result2= $mysqli->query("select email from user where username<>'admin'");
$num_rows = mysqli_num_rows($result2);
if ($num_rows <1){
return;
}

// Create the BCC list
$bcc= "";
while ($row = mysqli_fetch_array($result2))
{
$bcc .= $row['email'];
$bcc .=",";
}

// Add BCC
$headers .= 'BCC: '. $bcc . "\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1";

// Create the html message
$message ="<html><body>";
$message .= "New digital releases on the website:"." \n\n";
$message .= "<table><tr>";
$message .= "<td><b>Catalogue</td><td><b>Genre</td><td><b>Artist</td><td><b>Album</td><td><b>Year Released</td><td><b>Date Uploaded</td></tr></b>";
while ($row = mysqli_fetch_array($result1))
{
$message .= "<tr><td>";
$message .= $row['folder'];
$message .="</td><td>";
$message .= $row['genre'];
$message .="</td><td>";
$message .= $row['artist'];
$message .="</td><td>";
$message .= $row['album'];
$message .="</td><td>";
$message .= $row['year'];
$message .="</td><td>";
$message .= $row['created']. "</td></tr>";
}
$message .= "</table></body></html>";

// email fields: to, from, subject, and so on
$ok = mail($to, $subject, $message, $headers, $returnpath);
return $ok ;

by **CasN** » Sat May 20, 2017 2:53 pm

Please replace the following line

Code: Select all: $result1 = $mysqli->query("SELECT music_folder.name as folder,artist,album.name as album, year,genre, created FROM album,music_folder WHERE album.folder_id=music_folder.id and album.created> (curdate()-7) order by music_folder.name,artist,album.name");

with

Code: Select all: $result1 = $mysqli->query("SELECT music_folder.name as folder,artist,album.name as album, year,genre, created FROM album,music_folder WHERE album.folder_id=music_folder.id and album.created>= curdate()-interval 7 day order by music_folder.name,artist,album.name");

by **tmwsiy** » Fri Sep 15, 2017 7:41 pm

this is very cool!

I have 40 or so users and would love to do this but I didn't bother entering e-mails for most accounts.
I'd love to figure out how to run a script to do this weekly to a set list of e-mail addresses that I manually enter.
Though I don't know a darn thing about coding sadly.

by **CasN** » Sat Sep 16, 2017 10:13 am

best is to add the email addresses to your subsonic accounts.
But in case that is not what you want, you can adjust the script like below, where you can add the email addresses manually in the script.
In the end this will be more work but if that is what you want....
now you can run the script manual as explained earlier or set up a scheduled task/cron job.

Have fun,
Cas

<?php
// database settings
$username = "subsonic";
$password = "xxxxxx";
$hostname = "localhost";
// Mail settings
$from = "xxx@xxx.com";
$to = "xxx@xxx.com";
$subject ="Music-News from xxx.com";
// set the email addresses and create the BCC list
$bcc= "";
// just repeat the below 2 lines and adjust the email address
$bcc .= "email.adres@domain.com;
$bcc .=",";

//
// No changes required below this line
//

// Database connection
$mysqli = new mysqli($hostname, $username, $password, "subsonic")
or die("Unable to connect to MySQL");

// some definitions
$returnpath = "-f$from" ;
$headers = "From: $from"."\r\n";
$headers .= "Return-Path: $from"."\r\n";

// did we upload any new media/music last week
$result1 = $mysqli->query("SELECT music_folder.name as folder,artist,album.name as album, year,genre, created FROM album,music_folder WHERE album.folder_id=music_folder.id and album.created>= curdate()-interval 7 day order by music_folder.name,artist,album.name");
$num_rows = mysqli_num_rows($result1);
if ($num_rows <1){
return;
}

// Add BCC
$headers .= 'BCC: '. $bcc . "\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1";

// Create the html message
$message ="<html><body>";
$message .= "New digital releases on the website:"." \n\n";
$message .= "<table><tr>";
$message .= "<td><b>Catalogue</td><td><b>Genre</td><td><b>Artist</td><td><b>Album</td><td><b>Year Released</td><td><b>Date Uploaded</td></tr></b>";
while ($row = mysqli_fetch_array($result1))
{
$message .= "<tr><td>";
$message .= $row['folder'];
$message .="</td><td>";
$message .= $row['genre'];
$message .="</td><td>";
$message .= $row['artist'];
$message .="</td><td>";
$message .= $row['album'];
$message .="</td><td>";
$message .= $row['year'];
$message .="</td><td>";
$message .= $row['created']. "</td></tr>";
}
$message .= "</table></body></html>";

// email fields: to, from, subject, and so on
$ok = mail($to, $subject, $message, $headers, $returnpath);
return $ok ;

Subsonic Forum

Email new releases to your users

Email new releases to your users

Re: Email new releases to your users

Re: Email new releases to your users

Re: Email new releases to your users

Who is online