Search Interface For The Jukebox Server...

The main purpose of the 'Jukebox' suite is the ability for individuals to participate in the selection of the current media presentation. From their laptops or cell phones. As long as they are authenticated on the local network.

This is the interface script used to query the database for media content choices.

<?php
  // Copyright (c) 2014 No Fun Farms A.K.A. www.smegware.com
  $index = 0;
  $dbrows = 0;
  $dbcols = 0;
  $dbcol1 = "Artist";
  $dbcol2 = "Album";
  $dbcol3 = "Title";
  $querymax = 50;
  if(!empty($_GET['index']))
  {
   $index = $_GET['index'];
  }
  $dbh = mysql_connect("localhost:3306","user","pass");
  if($dbh)
  {
   $sql = "";
   mysql_select_db("jukebox");
   if(!empty($_GET['artist']) || !empty($_GET['album']) || !empty($_GET['title']))
   {
    $sql = "SELECT artist.artistid,artist,album.albumid,album,Title,Genre,smprate,bitrate,milisec,songid FROM artist,album,song ".
	   "WHERE album.artistid=artist.artistid AND song.albumid=album.albumid ";
    if(!empty($_GET["artist"]))
    {
     $sql .= "AND LOWER(artist.artist)=LOWER('".$_GET['artist']."') ";
    }
    if(!empty($_GET["album"]))
    {
     $sql .= "AND LOWER(album.album)=LOWER('".$_GET['album']."') ";
    }
    if(!empty($_GET["title"]))
    {
     $sql .= "AND LOWER(song.title)=LOWER('".$_GET['title']."') ";
    }
    $sql .= "ORDER BY artist,album,Title LIMIT {$index},{$querymax}";
    $dbcols = 3;
   }
   else if(!empty($_GET['wildcard']))
   {
    $sql = "SELECT artist.artistid,artist,album.albumid,album,Title,Genre,smprate,bitrate,milisec,songid FROM artist,album,song ".
	   "WHERE song.title LIKE '%".$_GET["wildcard"]."%' ".
	   "AND album.artistid=artist.artistid AND song.albumid=album.albumid ".
	   "ORDER BY artist,album,Title LIMIT {$index},{$querymax}";
    $dbcols = 3;
   }
   else if(!empty($_GET['wildartist']))
   {
    if(!empty($_GET['showalbum']))
    {
     $sql = "SELECT artist.artistid,artist,album.albumid,album FROM artist,album ".
	    "WHERE artist.artist LIKE '%".$_GET["wildartist"]."%' ".
	    "AND album.artistid=artist.artistid ".
	    "ORDER BY artist,album LIMIT {$index},{$querymax}";
     $dbcols = 2;
    }
    else
    {
     $sql = "SELECT artistid,artist FROM artist ".
	    "WHERE artist LIKE '%".$_GET['wildartist']."%' ".
	    "LIMIT {$index},{$querymax}";
     $dbcols = 1;
    }
   }
   else if(!empty($_GET['wildalbum']))
   {
    if(!empty($_GET['showtitle']))
    {
     $sql = "SELECT artist.artistid,artist,album.albumid,album,Title,Genre,smprate,bitrate,milisec,songid FROM artist,album,song ".
	    "WHERE album LIKE '%".$_GET['wildalbum']."%' ".
	    "AND album.artistid=artist.artistid ".
	    "AND song.albumid=album.albumid ".
	    "ORDER BY artist,album,Title LIMIT {$index},{$querymax}";
     $dbcols = 3;
    }
    else
    {
     $sql = "SELECT artist.artistid,artist,album.albumid,album FROM artist,album ".
	    "WHERE album LIKE '%".$_GET['wildalbum']."%' ".
	    "AND album.artistid=artist.artistid ".
	    "ORDER BY artist,album LIMIT {$index},{$querymax}";
     $dbcols = 2;
    }
   }
   else if(!empty($_GET['style']))
   {
    if($_GET['style'] == "type")
    {
     $sql = "SELECT DISTINCT(Genre) AS artist FROM song ".
            "WHERE Genre LIKE '%".$_GET['genre']."%' ".
            "ORDER BY Genre LIMIT {$index},{$querymax}";
     $dbcol1 = "Genre";
    }
    else if($_GET['style'] == "alltype")
    {
     $sql = "SELECT DISTINCT(Genre) AS artist FROM song ".
            "ORDER BY Genre LIMIT {$index},{$querymax}";
     $dbcol1 = "Genre";
    }
    if($_GET['style'] == "date")
    {
     $sql = "SELECT DISTINCT(Year) AS artist FROM song ".
            "WHERE Year LIKE '%".$_GET['year']."%' ".
            "ORDER BY Year LIMIT {$index},{$querymax}";
     $dbcol1 = "Year";
    }
    else if($_GET['style'] == "alldate")
    {
     $sql = "SELECT DISTINCT(Year) AS artist FROM song ".
            "ORDER BY Year LIMIT {$index},{$querymax}";
     $dbcol1 = "Year";
    }
    $dbcols = 1;
   }
   else if(!empty($_GET['songid']))
   {
    $sql = "SELECT artist.artistid,artist,album.albumid,album,song,Size,Title,Year,Genre,Track,Position,Length,smprate,bitrate,milisec ".
           "FROM artist,album,song ".
	   "WHERE song.songid='{$_GET['songid']}' ".
	   "AND album.artistid=artist.artistid AND song.albumid=album.albumid";
   }
   else if($_GET['stats'] == "show")
   {
    $sql = "SELECT st_0,st_1,st_2,st_3,st_4,st_5,st_6,st_7 ".
           "FROM stats WHERE name='music'";
   }
   else if(!empty($_GET['artistid']))
   {
    $sql = "SELECT DISTINCT artist.artistid,artist,album.albumid,album FROM artist,album ".
           "WHERE artist.artistid=".$_GET['artistid']." AND album.artistid=".$_GET['artistid']." ".
           "ORDER BY artist,album LIMIT {$index},{$querymax}";
    $dbcols = 2;
   }
   else if(!empty($_GET['albumid']))
   {
    $sql = "SELECT DISTINCT artist.artistid,artist,album.albumid,album,Title,Genre,smprate,bitrate,milisec,songid FROM artist,album,song ".
           "WHERE artist.artistid=album.artistid AND album.albumid=".$_GET['albumid']." AND song.albumid=".$_GET['albumid']." ".
           "ORDER BY Title LIMIT {$index},{$querymax}";
    $dbcols = 3;
   }
   else if(!empty($_GET['genreid']))
   {
    $sql = "SELECT DISTINCT artist.artistid,artist,album.albumid,album FROM artist,album,song ".
           "WHERE artist.artistid=album.artistid AND album.albumid=song.albumid ".
           "AND song.Genre='".$_GET['genreid']."' ".
	   "ORDER BY artist,album LIMIT {$index},{$querymax}";
    $dbcols = 2;
   }
   else if(!empty($_GET['yearid']))
   {
    $sql = "SELECT DISTINCT artist.artistid,artist,album.albumid,album FROM artist,album,song ".
           "WHERE artist.artistid=album.artistid AND album.albumid=song.albumid ".
           "AND song.Year='".$_GET['yearid']."' ".
	   "ORDER BY artist,album LIMIT {$index},{$querymax}";
    $dbcols = 2;
   }
   else
   {
    echo("  <p>\n   Qeury fields are empty. Please select better next time.\n  </p>\n");
   }
   $table = mysql_query($sql, $dbh);
   if($table)
   {
    if(!empty($_GET['stats']))
    {
     if($row = mysql_fetch_array($table, MYSQL_ASSOC))
     {
      echo("  <p class = \"juke_stats\">\n".
           "   <b>Artists = </b>{$row['st_0']}<br>\n".
           "   <b>Albums = </b>{$row['st_1']}<br>\n".
           "   <b>Titles = </b>{$row['st_2']}<br>\n".
           "   <b>Genres = </b>{$row['st_3']}<br>\n".
           "   <b>Years = </b>{$row['st_4']}<br>\n".
           "   <b>Sample Rates = </b>{$row['st_5']}<br>\n".
           "   <b>Bit Rates = </b>{$row['st_6']}<br>\n".
           "  </p>\n");
      $dbrows = 1;
     }
    }
    else if(!empty($_GET['songid']))
    {
     if($row = mysql_fetch_array($table, MYSQL_ASSOC))
     {
      $minutes = ($row['milisec'] / 60000) % 1000;
      $seconds = ($row['milisec'] / 1000) % 60;
      $milisec = $row['milisec'] % 1000;
      echo("  <p class = \"juke_stats\">\n".
           "   <b>Artist = </b>{$row['artist']}.<br>\n".
           "   <b>Album = </b>{$row['album']}.<br>\n".
           "   <b>Title = </b>{$row['Title']}.<br>\n".
           "   <b>Name = </b>{$row['song']}.<br>\n".
           "   <b>Size = </b>{$row['Size']} bytes.<br>\n".
           "   <b>Year = </b>{$row['Year']}.<br>\n".
           "   <b>Genre = </b>{$row['Genre']}.<br>\n".
           "   <b>Track = </b>{$row['Track']}.<br>\n".
           "   <b>Position = </b>{$row['Position']}.<br>\n".
           "   <b>Length = </b>{$row['Length']}.<br>\n".
           "   <b>Sample Rate = </b>{$row['smprate']} smp/s.<br>\n".
           "   <b>Bit Rate = </b>{$row['bitrate']} bit/s.<br>\n".
           "   <b>Time = </b>{$minutes}:".sprintf('%02d',$seconds).":{$milisec}.<br>\n".
           "  </p>\n");
      $dbrows = 1;
     }
    }
    else
    {
     //echo("  <p>\n  </p>\n");
     echo("  <table class=\"juke_table\">\n");
     echo("  <tr>\n");
     echo("   <th>{$dbcol1}</th>\n");
     if($dbcols > 1)
     {
      echo("   <th>{$dbcol2}</th>\n");
     }
     if($dbcols > 2)
     {
      echo("   <th>{$dbcol3}</th>\n");
     }
     echo("  </tr>\n");
     while($row = mysql_fetch_array($table, MYSQL_ASSOC))
     {
      echo("  <tr>\n");
      echo("   <td class=\"juke_item_artist\">");
      if($dbcol1 == "Genre")
      {
       $refa = array('genreid'=>$row['artist']);
      }
      else if($dbcol1 == "Year")
      {
       $refa = array('yearid'=>$row['artist']);
      }
      else
      {
       $refa = array('artistid'=>$row['artistid']);
      }
      $refq = http_build_query($refa, '', '&amp;');
      echo(" <a href=\"?{$refq}\">{$row['artist']}</a></td>\n");
      if($dbcols > 1)
      {
       echo("   <td class=\"juke_item_album\">");
       $refa = array('albumid'=>$row['albumid']);
       $refq = http_build_query($refa, '', '&amp;');
       echo(" <a href=\"?{$refq}\">{$row['album']}</a></td>\n");
      }
      if($dbcols > 2)
      {
       $minutes = ($row['milisec'] / 60000) % 1000;
       $seconds = ($row['milisec'] / 1000) % 60;
       $milisec = $row['milisec'] % 1000;
       echo("   <td class=\"juke_item_song\" title=\"genre={$row['Genre']} :".
            "samples={$row['smprate']} : bitrate={$row['bitrate']} :".
            "length={$minutes}:".sprintf('%02d',$seconds).":{$milisec}.\">".
            "<a href=\"?songid={$row['songid']}\">{$row['Title']}</a></td>\n");
      }
      echo("  </tr>\n");
      $dbrows += 1;
     }
     echo(" </table>\n");
    }
    mysql_free_result($table);
   }
   mysql_close($dbh);
  }
  if($dbrows)
  {
    $ref = "http://".$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
    $ref = str_replace("&index={$index}", "", $ref);
    $lastindex = $index - $querymax;
    $index += $querymax;
    $ref .= "&index=";
    $ref = str_replace("&", "&amp;", $ref);
    echo(" <p class=\"query_navigate\">\n");
    if($index > $querymax)
    {
      echo("  <a href=\"{$ref}{$lastindex}\"><--Previous Results</a>\n");
    }
    echo("  Showing results ".(($index - $querymax) + 1)." - ".($index - ($querymax - $dbrows))."\n");
    if($dbrows == $querymax)
    {
     echo("  <a href=\"{$ref}{$index}\">Next Results--></a>\n");
    }
    echo(" </p>\n");
  }
  $plural = "s";
  if($dbrows == 1)
  {
   $plural = "";
  }
  echo(" <p>\n </p>\n <table class=\"coin_tally\">\n");
  echo("  <tr>\n   <td>Showing {$dbrows} item{$plural}.</td>\n  </tr>\n");
  echo(" </table>\n");
?>

  

So How Does The Code Work...

It is important to note here that this code dynamically creates script to be generated by a server which will be interpreted on a remote machine. It is therefore necessary to provide a bidirectional means by which context can be preserved between queries. This is accomplished through the HTTP GET method where the encoded URL maintains a set of variable that defines the context. Parsing of the variables is then used to build a search query which is forwarded to the companion SQL server which may have results to show. If so, and the result set is larger then the page is willing to display then options are provided for traversing the page set for both forward and reverse directions as context determines. Since all the information to regenerate the page is contained within the URL results can also be bookmarked or favorited.

Additionally the code is linear in flow. It handles exceptions on the fly and is very careful while validating it's options. In other words... It properly handles the unexpected (errors) so the framework (server) doesn't have to therefore providing valid markup to the client. Always.