iTunes Style Music DB with AJAX

I recently decided that since I have some free time on my hands, something that would look really cool is a web page with a music database that works kinda like the iTunes window. The pretty tables are all good, but what I really love about the iTunes window is the Search box. You type things in and then the results appear instantly below. With AJAX, we can do the same thing...

First off, since we all like playing with the end results first, you can find one version of the results here. Do note that that Firefox has some minor issues with this page and Opera some more major ones. More on that later.

Next, we need just a little bit of background. Before starting this project, the site had a mySQL database table called "choir". The choir table holds such information as the song title, what time it was recorded, and the actual location of the audio file.

I decided to do this in PHP, so, the first thing I needed was a function to print the table where the search results would be displayed. This function needs to take two parameters: one parameter indicates which text the user is searching for (starts blank) and the second parameter indicates which entry the results should start at (starts at zero). This means that we only ever need one PHP function to print the data.

One last thing before we look at the main function. I used a very small helper function for outputting cells of a table which looks like so:

function cellPrint($cellText)
{
echo "<td>$cellText</td>";
}

All of this information is contained in a file called choirwindow.php.

Step one then is to connect to the database and query for the information.

function printChoirs($searchText,$startNumber)
{
//This variable represents the maximum number of songs to be shown
//on one page.
$maxAudioTableSize = 20;

//Keep the username/password/db stuff in easy to grep for places so they
//are easy to change if you need to later.
$username = "SuperUser";
$password = "AdminPassword";
$database = "SuperSpecialDatabase";

//Connect to the mySQL database
$link = mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die (mysql_error());

//This first query is to find out how many choir songs total match up to the
//search text. For optimization, this could be buffered and passed along in
//a post variable if the user is going to spend a lot of time selecting the NEXT
//or PREVIOUS links.
$countQuery = "SELECT count(*) from choir";
$countQuery = $countQuery . " WHERE song_name LIKE '%$searchText%'";
$countResult = mysql_query($countQuery);
$count = mysql_result($countResult,0,"count(*)");

//This second query is to retrieve the actual song information that we are
//going to display and save it in the $result variable.
$infoquery = "SELECT * FROM choir";
$infoquery = $infoquery . " WHERE song_name LIKE '%$searchText%'";
$infoquery = $infoquery . " order by date DESC";
$infoquery = $infoquery . " limit $startNumber,$maxAudioTableSize";
$result = mysql_query($infoquery);

//Let the user know how many search results he/she has.
echo "There are: " . $count . " choir song(s)";

One interesting thing to note from this is that mySQL is doing all of the work for the search mechanism through the use of the LIKE construct. This is not only lazy but good design as well as the search algorithm for mySQL is quite fast and running natively as compared to any possible solution in PHP.

Next up, we need to output the table itself. This is done quite simply by echoing out each row of the mySQL $result to a row of an HTML table. In the code, you will note that the table is completely unadorned. The visual look of the table is cleaned up later using css.

//$num is the number of results we have for THIS page.
$num = mysql_numrows($result);

//Start the HTML table.
echo "<table>";

//Output Table Header.
echo <thead><tr>";
cellPrint("Song Name");
cellPrint("Date");
cellPrint("");
echo "</tr></thead>";

//Now the table body.
echo "<tbody>";
$i = 0;
while ($i < $num)
{
echo "<tr>";
$choir_name=mysql_result($result,$i,"song_name");
$date=mysql_result($result,$i,"date");
$filename=mysql_result($result,$i,"filename");
$id=mysql_result($result,$i,"id");
$dateString = strftime("%m/%d/%y",$date);
cellPrint($choir_name);
cellPrint($dateString);
cellPrint("<a href=\"$filename\">Download</a>");
echo "</tr>";

$i++;
}
echo "</tbody>";

echo "</table>";

//Don't forget to close your db connection!
mysql_close($link);

Now that the table itself is on the page, the only other HTML we have to put out is the code for the "hyperlinks" at the bottom of the table that allow navigation through various pages of results. You may have noticed from the web page that these are not actually hyperlinks, they just look like them. They are actually connected to a javascript function called "reloadMusic" which we will get to shortly. They appear to be real links because they are set to the class "fakelink" which corresponds to the following css rule.

.fakelink{text-decoration:underline;cursor:pointer;cursor:hand}

Now, the code:

//We only need to do this if there are more total entries than current...
//Remember, $count is total entries matching the search string whereas
//$num is the number of entries returned by our mySQL query.
if ($count > $num)
{
// Print out PREV if necessary
if ($startNumber > 0)
{
$previousNumber = $startNumber-$maxAudioTableSize;
echo "<a class=\"fakelink\" onclick=\"reloadMusic('$searchText',$i);\">$page</a> ";
}

// Print out a bunch of numbers for each of the pages.
$i = 0; //This will be the base index number for each page set.
$page = 1; //Which "Page" is this located... the 1,2,3,4 selection.
while ($i < $count)
{
if ($i <> $startNumber)
{
echo " <a class=\"fakelink\" onclick=\"reloadMusic('$searchText',$i);\">$page</a> ";
}
else
{
echo " $page ";
}

$page++;
$i = $i+$maxAudioTableSize;
}



// Print out NEXT if necessary.
if ( ($startNumber+$maxAudioTableSize) < $count)
{
$nextNumber = $startNumber+$maxAudioTableSize;
echo "<a class=\"fakelink\" onclick=\"reloadMusic('$searchText',$nextNumber);\">Next</a>";
}
}

}

This brings us naturally to the question of the javascript. Obviously, if I'm calling this AJAX (Asynchronous Javascript and XML) it's gotta have some javascript in it. Although, I confess, I am not making use of XML in this example which technically makes it AJAH (Asynchronous Javascript and HTML) but that is a much more obtuse term. In any case, it is time to examine the Javascript. The javascript is printed out in a seperate PHP function which is called by parent file shortly after (or in) its header. The javascript could easily have stood on its own in this case, but I wanted to make the file as modular as possible. Hence, the PHP function that prints out javascript:

function printChoirHeader()
{
print"
<script language=\"javascript\" type=\"text/javascript\" src=\"./ajax.js\"></script>
<script language=\"javascript\" type=\"text/javascript\">

function reloadMusic(searchText,startNumber){
var url =\"choirwindow.php?searchText=\" + escape(searchText);
url = url + \"&startNumber=\" + escape(startNumber);
http.open(\"GET\", url,true);
http.onreadystatechange = handleHTTPResponse;
http.send(null);
}

function search(){
var searchText = document.getElementById(\"searchtext\").value;
var url = \"choirwindow.php?searchText=\" + escape(searchText);
url = url + \"&startNumber=0\";
http.open(\"GET\", url,true);
http.onreadystatechange = handleHTTPResponse;
http.send(null);
}

function handleHTTPResponse() {
if (http.readyState == 4) {
document.getElementById('audiotable').innerHTML = http.responseText;
}
}

var http = getHTTPObject(); // We create the HTTP Object
</script>";
}

This uses the same pattern as my appearing map example, so it might be helpful to get some review there. Basically, the reloadMusic() and search() functions take whatever parameters were previously there and ferry them on to a call to choirwindow.php and pass GET parameters for the search text and the startNumber which is the mySQL index of what will be the first entry of the new table. The reload music function is passed these parameters by the PHP code that we have previously seen and does no work for them on its own. When writing the search function, it is already known that since we are now performing a new search, we will start at a new index (0). All the javascript function has to do then is to get the search text which is does by finding the box with the id of "searchtext" in the calling page. This naturally means that any file that makes use of the choirwindow.php functionality and desires to use its search functionality must give its searchbox an id of "searchtext". (See Requirements section below). Similarly, the calling file must have a division whose id is "audiotable" in which to print this table by calling printChoirs("",0);

Now, this code calls back to choirwindow.php with its GET parameters. These are handled outside of any function by the following code:

$searchText = $_GET['searchText'];
$startNumber = $_GET['startNumber'];

if (isset($searchText))
{
printChoirs($searchText,$startNumber);
}

This means that when the javascript calls choirwindow.php with parameters, choirwindow.php will respond by printing its music table. However, when the file is included, since these parameters will not be set during the include call, no output will muddy up the top of the page.

Finally, for the sake of thoroughness, let's see the code with the search box and audiotable div:

Search: <input maxlength="60" size="60" name="searchtext" id="searchtext" onkeyup="search();">

<div id="audiotable" class="audiotable">

printChoirs("",0);
?>


Requirements:
In order to use this PHP functionality then, the calling PHP file must do the following:

1. Must contain a search field named "searchtext" whose onClick function calls "search();"
2. Must call printChoirHeader() in its header
3. Must call printChoirs() from within a div whose id is "audiotable"

Firefox and Opera issues:
Both Firefox and Opera have, on occasion, issues with this functionality. While both have different visual manifestations, they seem to have the same root cause. After the asynchronous call to reload the page, Firefox sometimes throws a javascript exception and refuses to redraw the page. The result of this is that the table dissapears. Interestingly, if you view the source that Firefox sees, you will find that the source is rendered perfectly. Additionally, you can save the source as an html file, reload with Firefox, and everything will appear correctly. Not being a Firefox developer, I cannot tell you why exactly that is, but, I can point you to the bug report where you can vote for it to get fixed faster :)

Similarly, Opera sometimes "forgets" to erase what was in the audiotable div before, resulting in two different musical tables in the results box. Like with the Firefox bug, though, this seems to be a rendering issue as you can examine the source that Opera is trying to render and the source is correct.

Internet Explorer seems to render the table perfectly every time.