A web interface to search and download albums in an Amarok library

A PHP script that will let guests search and download albums from your MySQL Amarok music database (make sure access to this file is password-protected, obviously):

<?php
$db = mysql_connect('localhost', 'YOUR_DATABASE_USER', 'YOUR_DATABASE_PASSWORD'); // edit these
mysql_select_db('YOUR_DATABASE_NAME'); // edit this

// download the zipped files if artist_id and album_id are present
if (is_numeric($album_id = $_GET['album_id']) && is_numeric($artist_id = $_GET['artist_id'])):      
  $result = mysql_query("SELECT CONCAT_WS('/', d.lastmountpoint, t.url) AS path FROM `tags` t LEFT JOIN `devices` d ON t.deviceid = d.id WHERE t.album = $album_id AND t.artist = $artist_id");
  
  if (! mysql_num_rows($result)) exit("no matching albums\n");
  
  $zip = new ZipArchive(); 
  $file = "/tmp/$album_id$artist_id.zip";
  if ($zip->open($file, ZIPARCHIVE::OVERWRITE) !== TRUE) exit("cannot open <$file>\n");
  
  while($track = mysql_fetch_object($result))
    $zip->addFile($track->path, basename($track->path));
  
  $zip->close();
  
  header('Content-type: application/zip');
  header('Content-Disposition: attachment; filename="' . basename($file) . '"');
  header('Content-Length: ' . @filesize($file));
  readfile($file);
  
  unlink($file);

// provide a search form and download links
else:
?>

<form action="" method="GET">
<input type="text" length="30" name="q" value="<?php print htmlentities(stripslashes($_GET['q'])) ?>"/>
<input type="submit" value="search"/>
</form>

<ul>
<?php
// search the database for artists or albums matching the query terms
if ($_GET['q']){
  $terms = array();
  foreach (explode(' ', mysql_real_escape_string($_GET['q'])) as $term)
    $terms[] = "(a.name LIKE '%$term%' OR b.name LIKE '%$term%')";
  
  $result = mysql_query("SELECT a.name as artist, a.id as artist_id, b.name as album, b.id as album_id FROM `artist` a INNER JOIN `tags` t ON a.id = t.artist INNER JOIN `album` b ON t.album = b.id WHERE " . implode(' AND ', $terms) . " GROUP by album ORDER BY artist ASC");
  
  if (! mysql_num_rows($result)) exit("no matching albums\n");
  
  while($album = mysql_fetch_object($result))
    print '<li><a href="?album_id=' . $album->album_id . '&artist_id=' . $album->artist_id . '">' . $album->artist . ' - ' . $album->album . '</a>';
}
?>
</ul>

<?php
endif;
mysql_close($db);
?>

Comments

Iam currently Coding on a fork of this code with the following features:

Playlist generating
Download Tracks and Albums
Create self albums
a Stylish design
AND

Maybe functionality to burn a disk when webserver is on localhost (e.g. for music terminals)

Greets Silicium

Fantastic script. It searches the database just fine. However when I click on an album, it downloads an empty zip file. Any clues?

Posted by: Micky Williamson on September 18, 2007 3:58 AM [Reply]

All fields are optional, email address will not be shown; no HTML, URLs are automatically hyperlinked.