| About |
| Broadcasters |
| Free Trial |
| Purchase |
| News |
| Documentation |
| OAS Audio API |
Interacting with Playout (cont)...
Putting the scripts together
There are 3 distinct pages for the listener requests:
- Main opening page where the listener can enter search criteria for the track he/she is looking for
- The search page where the results of the criteria are displayed along with options to select each track
- The request page where the desired track is entered into the system
Opening Page
The opening page pure HTML, there is no scripting involved here at all.
The page simply consists of a standard form into which the Title and Artist search criteria may be entered. When the Submit button is pressed, the form information is passed to the search page, passing along the details as a HTTP POST request.
The next page displays the results of the search criteria - this is obtained by querying the Playout database and generating an HTML table using PHP script.
Here's what the opening code looks like:
<?php
/* Connecting, selecting database */
$link = mysql_connect("localhost", "user1", "password")
or die("Could not connect");
mysql_select_db("audio") or die("Could not select database");
// generate query string - pull out the basics - ID, title, artist, album
$query = "SELECT Song.SongID, Song.SongTitle, Artist.ArtistName, Album.AlbumTitle
FROM Album INNER JOIN (Artist INNER JOIN Song ON Artist.ArtistID = Song.ArtistID) ON Album.AlbumID = Song.AlbumID
WHERE (((Song.SongTitle) Like '%" . $_POST['Title'] . "%') AND ((Artist.ArtistName) Like '%" . $_POST['Artist'] . "%'))
ORDER BY Artist.ArtistName, Song.SongTitle, Album.AlbumTitle;";
// run the query
$result = mysql_query($query) or die("Query failed");
?>
First, the connection is established (to the MySQL server), the database opened then if successful an SQL query issued to retrieve the information posted into the page. Not being an SQL expert I tend to use MS-Access's GUI query builder to construct these then more or less paste the the resultant SQL code in. Together with the Playout SDK (installed with the package) you should find enough information to construct what you need to interact with the database.
So to generate the search results table, the Song table in the database needs to be searched retrieving matching criteria from this and the linked Album and Artist tables. The query I based the code on looked a bit like this.
Here I've put in some test words to try out my query on - now if you go to the SQL view in Access you can view (then cut and paste) the SQL this generates.
It then remains a simple case to replace the hard coded search criteria with the actual POST data sent in from the form. So the expression:
Like "*and*" becomes
Like '%" . addslashes($_POST['Title']) . "%'
MS-Access is a little quirky
with SQL - within Access itself, the SQL it generates - in this
case the use of the '*' character for wildcards instead of the more conventional
'%' and the double quotes (") as a delimitor also in favour of the
usual single quote('). However if you use this form in PHP code outside
of Access it will generate an error and you must use the more standard
form of SQL - '%' instead of '*'.
The addslashes call was based on the security section of the PHP
manual - "Quote each non numeric user input which is passed to the
database with addslashes() ..." this helps to reduce the possibility
of hackers damaging your database and/or gaining access to your web server
itself.
Next some HTML to give the user some info and form up the headings for the table:
<p class="detailheading">Search Results</p>
<p class="detailtext">Press the <b>Request</b> link next to the track you
want to request or use the browsers Back button to re-enter your search
criteria.</p>
<table width="95%" border="1" align="center">
<tr>
<td width="30%">
<div align="left" class="detailsubheading">Title</div>
</td>
<td width="30%">
<div align="left" class="detailsubheading">Artist</div>
</td>
<td width="29%">
<div align="left" class="detailsubheading">Album</div>
</td>
<td width="12%"> </td>
</tr>
Then some PHP code to retrieve the results of the query and generate the table:
<?php
// spin thru results
while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
print "\t<tr>\n";
print "\t\t<td class=\"detailtext\">$line[1]</td>\n";
print "\t\t<td class=\"detailtext\">$line[2]</td>\n";
print "\t\t<td class=\"detailtext\">$line[3]</td>\n";
// generate a url, encoding the song id in it print "\t\t<td class=\"detailtextlink\"><A HREF=\"request.php?Id=$line[0]\">Request</a></td>\n" ; print "\t</tr>\n"; } /* Free resultset */ mysql_free_result($result);
/* Closing connection */
mysql_close($link);
?>
</table>
Each result is fetched into the line array, with each element representing the column from the SELECT query. The Title, Artist and Album are simply stored into the table. The ID is used to form up a URL link to the request script, passing in the unique ID of the song. Therefore when the user presses the Request link for a given track, the request.php script will be called with the requested song id.
Request page (request.php)
The request page is responsible for confirming the listener's choice then asking for some information about themselves. For Hastings Rock we didn't include this page because requests were only used during periods of automation (overnight) so it would be redundant.
The information is saved in the 'Notes' field of the generated playlist entry and will then be visible through Playout.
The script starts as before, first selecting the database and this time running a query on the ID of the track passed in to obtain the Title and Artist for it which is then displayed for user confirmation.
$query = "SELECT Song.SongTitle, Artist.ArtistName
FROM Artist INNER JOIN Song ON Artist.ArtistID = Song.ArtistID
WHERE (((Song.SongID)=" . $_GET['Id'] . "));" ;
// run the query
$result = mysql_query($query) or die("Query failed");
$line = mysql_fetch_array($result, MYSQL_NUM) ;
if ( $line )
{
print "<p class=\"detailtext\">You've requested <b>'$line[0]'</b> by <b>'$line[1]'</b></p>" ;
}
else
die ("Query error") ;
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
print "<form method=\"post\" action=\"addlist.php?Id=" . $_GET['Id'] ."\">" ;
?>
<span class="detailtext">Please give us some <i>brief</i> details about your
request - who you are, who is it for etc. then press <b>Submit</b>to request
your track. </span>
<p>
<textarea name="notes" cols="50" rows="6"></textarea>
<input type="submit" name="Submit" value="Submit">
</p>
</form>
When the Submit button of this form is pressed, the next script in the sequence is executed (addlist.php) passing along the Id from the initial search page.
Request page (addlist.php)
This script is responsible for taking the listener's request and injecting it into the Playout database. Visually, this page presents very little information aside from informing the listener that their request was successful (or not).
Listener requests are stored in the Playout database as a normal playlist so the next step is to use Playout Manager to create a playlist for them - I simply called mine 'Requests'.
As before, the PHP script starts by connecting to the database, then the first step it to locate the ID assigned to the 'Requests' playlist - this will be needed to add the track later on.
// find the plist id of the "Requests" list
$query = "SELECT PlayListNames.PlayListId FROM PlayListNames WHERE
(((PlayListNames.Description)='Requests'))" ;
$result = mysql_query($query) or die("Query failed");
if ( mysql_num_rows($result) )
{
$line = mysql_fetch_array($result, MYSQL_NUM) ;
$RequestListId = $line[0] ;
}
else
die("Could not locate requests list ID" ) ;
mysql_free_result($result);
Finally the track is added to the Playlist :
// build query to add given id to plist - store the listener comments in the notes field
// of who requested the track
$query = "INSERT INTO PlayLists ( PlayListId, SongId, Played, Notes ) VALUES ( " . $RequestListId .",
" . $_GET['Id'] . ", 0, '" . $_POST['notes'] . "')" ;
if ( !mysql_query($query) )
die("Query failed");
/* Closing connection */
mysql_close($link);
As before, you can use the MS-Access query builder to construct the INSERT query. The Played field (set to zero) indicates to Playout that the track has not been played out yet.
Restricting Requests
When we setup the Hastings Rock system, the original search script generated different links depending on the time of day. Between the hours of 6am and midnight it generated links to the addlist script (where the listener could enter information about themselves) and between midnight and 6am (the automation period), the links were to a different script named overnightlist.php.
The code looked a bit like this:
// determine if this is the overnight list or daytime
$CurrentTime = localtime ( time(), TRUE ) ;
// less than 6am (ie. midnight till 6)
if ( $CurrentTime["tm_hour"] < 6 )
$OverNight = TRUE ;
else
$OverNight = FALSE ;
// generate a url, encoding the song id in it
if ( $OverNight )
print "\t\t<td class=\"$Class\"><A HREF=\"overnightlist.php?Id=$line[0]\" class=\"detailtextlink\">Request</a></td>\n" ;
else
print "\t\t<td class=\"$Class\"><A HREF=\"request.php?Id=$line[0]\"class=\"detailtextlink\">Request</a></td>\n" ;
The overnight list script does not ask for listener information, instead it is directly responsible for adding the track to the Requests playlist. However in order to prevent abuse of the system (for example one person "hogging the system" and putting in lots of requests or the same track over and over again) it applies a number of rules:
- No more than 2 tracks may be requested by 1 person that have yet to be played.
- Only one instance of a track may be played per night
- Only 5 tracks by the same artist per night
So there is a whole raft of PHP code to achieve this - in essence we used the IP address to identify a 'unqiue' listener and stored this in the Notes (instead of the listener information) field when the track is added to the request list, this means you can do something like this:
$query = "SELECT PlayLists.Notes FROM PlayLists WHERE ((PlayLists.Notes) = '" .
$_SERVER['REMOTE_ADDR'] . "' AND (PlayLists.Played) = 0)" ;
$result = mysql_query($query) or die("Query failed");
// if any from same IP, dont allow
if ( mysql_num_rows($result) > 2)
{
$AllowSubmit = FALSE ;
print "<p class=\"detailsubheading\">Previous track(s) requested by you have not yet been played</p>";
}
mysql_free_result($result);
Here you can also see that you can use the Played field as a test on whether Playout has actually begun playback of the given track. Once it does, this field will be updated to '1' (as opposed to actually deleting the record from the list).
For this to work, the INSERT
query is modified as follows:
$query = "INSERT INTO PlayLists ( PlayListId, SongId, Played, Notes ) VALUES ( " . $RequestListId .",
" . $_GET['Id'] . ", 0, '" . $_SERVER['REMOTE_ADDR']. "')" ;
The other rules - that of duplicate artists and identical
songs amount to further SELECT queries into the Song/Artist tables.