Joe's Blog

I have written this PHP function which will do the following:

• For the INITIAL request (i.e. the first search for that band, ever), the XML file for that artist will be downloaded from the EchoNest API server, parsed, and saved to MySQL DB table. The song URL will be retrieved from the database, ordered by random, and the first URL will be echoed to the user.
• For subsequent requests (when there are database entries for the artist), the data will be selected directly from the database, and echoed to the user. This vastly reduces load times, as it removes the need for the local server to connect to the EchoNest’s API server (which can add seconds to the load time).

This is the PHP Code:

[cc lang='php' ] $db_server = “127.0.0.1″; //MySQL Server
$db_user = “”; //MySQL Username
$db_password = “”; //MySQL Password
$db_name = “”; //MySQL Database
$api_key = “”; //TheEchoNest API Key
$apinresults = 5; //Number of API Results to store in database. For initial queries, a larger value will increase load time (considerably). Default = 5

$conn = mysql_connect($db_server, $db_user, $db_password) OR DIE(mysql_error()); //connect to DB, or print error
mysql_select_db($db_name, $conn) OR DIE(mysql_error()); //select database, or print error

function getAudio($artistname){
GLOBAL $api_key;
GLOBAL $apinresults;
$artist = mysql_real_escape_string($artistname); //help to prevent sql injection
$artist = str_replace(” “, “+”, $artist);
if($artist != “”){
$query = “SELECT url FROM audio WHERE artist = ‘”.$artist.”‘”; //sql query to check number of rows for that artist
$query = mysql_query($query); //query to check number of rows
if(mysql_num_rows($query) == 0){ //if number of rows is 0, download fresh data from API
$url = “http://developer.echonest.com/api/v4/artist/audio?api_key=”.$api_key.”&name=”.$artist.”&format=xml&results=”.$apinresults; //create API request URL
$xml = file_get_contents($url); //download XML document
$audios = new SimpleXMLElement($xml); //Parse XML Document

foreach($audios->audio->audio as $audio){ //go through XML Document, insert values into DB.
$query = “INSERT INTO `audio` (`aid` ,`title` ,`url` ,`artist` ,`date` ,`length` ,`link` ,`release` ,`id`)VALUES (NULL, ‘”.mysql_real_escape_string($audio->title).”‘,’”.mysql_real_escape_string($audio->url).”‘,’”.$artist.”‘,’”.mysql_real_escape_string($audio->date).”‘,’”.mysql_real_escape_string($audio->length).”‘,’”.mysql_real_escape_string($audio->link).”‘,’”.mysql_real_escape_string($audio->release).”‘,’”.mysql_real_escape_string($audio->id).”‘)”;
mysql_query($query); //insert data into db
}
}
//AFTER having fetched data from API (if no entries in db table), select the URL from the table, order by random, and select a single value.
$query = mysql_query(“SELECT `url` FROM `audio` WHERE `artist`=’”.$artist.”‘ ORDER BY RAND() LIMIT 1″);
$row = mysql_fetch_row($query);
echo $row[0]; //output URL to browser
}
}
?>[/cc]

I have tried to add as many comments as possible – but if there is anything you’d like to ask me about, then just add a comment.

This is the accompanying SQL file:

[cc lang='sql' ]CREATE TABLE IF NOT EXISTS `audio` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`title` text NOT NULL,
`url` text NOT NULL,
`artist` text NOT NULL,
`date` text NOT NULL,
`length` text NOT NULL,
`link` text NOT NULL,
`release` text NOT NULL,
`id` text NOT NULL,
PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;[/cc]

will also be needed. You might notice that I have included all of the data within the XML file – this is purely for future use, and so that the uploader of the song can be credited.

The script can be called as so:
[cc lang='php']
[/cc]

You can download a zip of the files here