PDA

View Full Version : Displaying playlists in PHP - my attempt



jimwillsher
2006-02-11, 10:07
Hi All,

I'm putting together a playlist for a dinner party, and I need to know the duration of the playlist. The web interface doesn't seem to have a feature to show the duration, so I've put together a PHP script to do the job.

I run my slim using MySql rather than the standard "DBLite" (?) stuff, so this will only work with MySql. Also, it's geared towards the design of my own site. Therefore my only reason for posting it here is in case other people whnt to do something similar. Hopefully somebody else can find it useful.

Jim



<?php

function secs_to_hhmmss ($secs)
{
// reset hours, mins, and secs we'll be using
$hh = 0;
$mm = 0;
$ss = 0;

if ($secs > 3600)
{
$hh = (int) floor ($secs / 3600);
$secs -= ($hh * 3600);
}

if ($secs > 60)
{
$mm = (int) floor ($secs / 60);
$secs -= ($mm * 60);
}

$ss = $secs;

// Now format a string
$h = ($hh < 10) ? "0" . $hh : $hh;
$m = ($mm < 10) ? "0" . $mm : $mm;
$s = ($ss < 10) ? "0" . $ss : $ss;

return ("$h:$m:$s");
}


// Connect and select the database
// Do this however you would normally do it, I use a bespoke class

$DetailID = isset($_GET['DetailID']) ? $_GET['DetailID'] : NULL;

?>
<HTML>
<HEAD>
<TITLE>Slimserver Playlist Information</TITLE>
</HEAD>
<BODY bgcolor="#FFFFFF" text="#000000" background="../Site/Img/Background.jpg">
<TABLE width="50%" border="1" align="center">
<TR>
<TH colspan="3">Slimserver Playlist Information</TH>
</TR>
<TR>
<TD colspan="3">&nbsp;</TD>
</TR>
<TR>
<TH>
Playlist Name
</TH>
<TH>
Tracks
</TH>
<TH>
Duration
</TH>
</TR>

<?php
// Build a list of all the playlists
$sql = "SELECT DISTINCT(playlist) AS PlayListID FROM playlist_track";
$playlistresult = mysql_query($sql) or die('Query failed : ' . mysql_error());
while ($playlistrow = mysql_fetch_array($playlistresult, MYSQL_ASSOC))
{
$PlayListID = $playlistrow['PlayListID'];

// Extract the playlist name
$sql = "SELECT title AS PlayListTitle FROM tracks WHERE id = $PlayListID";
$result = mysql_query($sql) or die('Query failed : ' . mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$PlayListTitle = $row['PlayListTitle'];

// Extract the track count and playlist duration
$sql = "SELECT (SUM(secs)) AS TotalSecs, COUNT(tracks.id) AS TrackCount FROM playlist_track, tracks WHERE playlist_track.track = tracks.id AND playlist=$PlayListID";
$result = mysql_query($sql) or die('Query failed : ' . mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$TotalSecs = $row['TotalSecs'];
$TrackCount = $row['TrackCount'];

$FormattedSecs = secs_to_hhmmss($TotalSecs);
?>
<TR>
<TD>
<A href="<?=$_SERVER['PHP_SELF']?>?DetailID=<?=$PlayListID?>"><?= $PlayListTitle ?></A>
</TD>
<TD>
<?= $TrackCount ?>
</TD>
<TD>
<?= $FormattedSecs ?>
</TD>
</TR>
<?php
}
?>
</TABLE>

<?php
// Show a detailed list?
if ($DetailID != NULL)
{
?>
<BR>
<TABLE border="1" align="center">
<TR>
<TH>
Track
</TH>
<TH>
Title
</TH>
<TH>
Album
</TH>
<TH>
Artist
</TH>
<TH>
Duration
</TH>
</TR>
<?php
$sql = "SELECT playlist_track.id AS TrackID, tracks.title AS TrackTitle, albums.title AS AlbumTitle, contributors.name AS ArtistName, tracks.secs AS Duration"
. " FROM playlist_track, tracks, albums, contributors, contributor_track"
. " WHERE playlist_track.track = tracks.id AND tracks.album = albums.id AND tracks.id = contributor_track.track AND contributor_track.contributor = contributors.id"
. " AND playlist_track.playlist = '$DetailID'"
. " AND contributor_track.role = 1"
. " ORDER BY playlist_track.position";


$detailresult = mysql_query($sql) or die('Query failed : ' . mysql_error());
$i = 0;
while ($detailrow = mysql_fetch_array($detailresult, MYSQL_ASSOC))
{
$i++;
$TrackID = $detailrow['TrackID'];
$TrackTitle = $detailrow['TrackTitle'];
$AlbumTitle = $detailrow['AlbumTitle'];
$ArtistName = $detailrow['ArtistName'];
$Duration = secs_to_hhmmss($detailrow['Duration']);
?>
<TR>
<TD>
<?= $i?>
</TD>
<TD>
<?= $TrackTitle ?>
</TD>
<TD>
<?= $AlbumTitle ?>
</TD>
<TD>
<?= $ArtistName ?>
</TD>
<TD>
<?= $Duration ?>
</TD>
</TR>
<?php
}

?>
</TABLE>
<?php
}
?>

<TABLE border="1" align="center">
<TR>
<TD colspan="2" align="center">
<B><A href="/admin/">Main Menu</A></B>
</TD>
</TR>
</TABLE>

</BODY>
</HTML>