Jump to content



Photo

Importing large XML into mySQL without shell access

mysql xml import

  • Please log in to reply
9 replies to this topic

#1 Tjcrazy

Tjcrazy

    Your Average Neowin Guy.

  • Joined: 02-May 09
  • Location: The Cotswolds, United Kingdom

Posted 20 November 2011 - 22:01

Hey guys,

Got a bit of an issue importing a large xml into my mysql database.

I've got this xml file, from lastfm which I need to import. Theres a limit to 200 tracks per page, so I'll have to change the page parameter to 2,3,4,5 etc.

http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=1

I've tried this php script, but it takes a long time to execute and it stops at number 10199
<?php
//DBCONNECTION
$total=0;
$n=1;
while($n<2320) {
$url = 'http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page='.$n;
if(!$xml = simplexml_load_file($url)) {
  echo 'Unable to load XML file';
  exit();
} else {
  $i=0;
  while ($i < 200) {
   foreach($xml as $recenttracks) {
    $artist = $recenttracks->track[$i]->artist[0];
    $name = $recenttracks->track[$i]->name[0];
   }
   foreach($xml->recenttracks[0]->track[$i]->date[0]->attributes() as $a => $b) {
    $date = $b;
   }
   $artist = mysql_real_escape_string ($artist);
   $name = mysql_real_escape_string ($name);
   $date = mysql_real_escape_string ($date);
   echo 'Number: '.$total.' n: '.$n.' i: '.$i.'<br />';
   echo $date.'<br />';
   echo $name.'<br />';
   echo $artist.'<br /><br />';
   $i++;
   $total++;
   mysql_query("INSERT INTO songs (Artist, Title, Time, DJ) VALUES ('$artist', '$name', '$date', 'Unknown')") or die(mysql_error());
  }
}
$n++;
}
?>

Has anyone got any ideas how I can import these 200,000 tracks from XML into my mySQL database?


Thanks a lot!


#2 Clearskies

Clearskies

    Neowinian

  • Joined: 10-November 11
  • Location: Ottawa, Canada
  • OS: W8 / Xubuntu
  • Phone: Samsung Galaxy SII X

Posted 20 November 2011 - 22:12

Perhaps your time limit is too low. Try changing it in your php.ini (if you have access), or look at this:
http://php.net/manua...-time-limit.php

#3 OP Tjcrazy

Tjcrazy

    Your Average Neowin Guy.

  • Joined: 02-May 09
  • Location: The Cotswolds, United Kingdom

Posted 20 November 2011 - 22:13

Perhaps your time limit is too low. Try changing it in your php.ini (if you have access), or look at this:
http://php.net/manua...-time-limit.php


I changed the max_execution_time to 8000 seconds - I was getting errors before, so I don't think thats an issue.

#4 Phouchg

Phouchg

    has stopped responding

  • Tech Issues Solved: 9
  • Joined: 28-March 11

Posted 21 November 2011 - 01:40

Why are you echoing out every value you read? With such huge heaps of data in a single script PHP could be easily running out of memory.

1. Remove echoing
2. Consider using mysqli prepared statements to get by without resource-hungry string operations (escaping) and to take advantage of binary transfers.
3. Temporarily increase the memory limit with ini_set('memory_limit', $someverybigvalue);

If this crystal ball guesswork fails, defining what the "stop" actually means might help.
Or you could make many to process certain ranges of all that stuff in parallel, although then the hard drive will hate you for eternity.

#5 OP Tjcrazy

Tjcrazy

    Your Average Neowin Guy.

  • Joined: 02-May 09
  • Location: The Cotswolds, United Kingdom

Posted 21 November 2011 - 15:18

Why are you echoing out every value you read? With such huge heaps of data in a single script PHP could be easily running out of memory.

1. Remove echoing
2. Consider using mysqli prepared statements to get by without resource-hungry string operations (escaping) and to take advantage of binary transfers.
3. Temporarily increase the memory limit with ini_set('memory_limit', $someverybigvalue);

If this crystal ball guesswork fails, defining what the "stop" actually means might help.
Or you could make many to process certain ranges of all that stuff in parallel, although then the hard drive will hate you for eternity.


I've removed the echo, and changed the memory limit. Never used mysqli before, so not sure about that.

This is the current version of code. Followed by its output!
<?php
ini_set('memory_limit','199M');
//CONNECTION
$total=0;
$n=0;
while($n<2320) {
$n=$n+1;
$url = 'http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page='.$n;
if ($n=1) { echo $url; } //Debug
if(!$xml = simplexml_load_file($url)) {
  echo 'Unable to load XML file';
  exit();
} else {
  $i=0;
  while ($i < 200) {
   foreach($xml as $recenttracks) {
    $artist = $recenttracks->track[$i]->artist[0];
    $name = $recenttracks->track[$i]->name[0];
   }
   foreach($xml->recenttracks[0]->track[$i]->date[0]->attributes() as $a => $b) {
    $date = $b;
   }
   $artist = mysql_real_escape_string ($artist);
   $name = mysql_real_escape_string ($name);
   $i++;
   $total++;
   mysql_query("INSERT INTO songs (Artist, Title, Time, DJ) VALUES ('$artist', '$name', '$date', 'Unknown')") or die(mysql_error());
  }
}
}
echo $total;
?>

Output (This proves that there must be something wrong with my code!):
http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=1http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=2

As you can see, the code states that when n is 1, it should show the URL (this was for debug). But now its outputting the URL numerous times when $n is 2 (because the page = 2 in the URL.

I'm willing to pay if someone can fix (not much).

#6 Phouchg

Phouchg

    has stopped responding

  • Tech Issues Solved: 9
  • Joined: 28-March 11

Posted 21 November 2011 - 16:20

Here you have an erroneous assignment operator instead of a comparison:
if ($n=1) { echo $url; } //Debug
Should be:
if ($n==1) { echo $url; } //Debug

I'll see if I can reproduce the rest of the problems.

#7 OP Tjcrazy

Tjcrazy

    Your Average Neowin Guy.

  • Joined: 02-May 09
  • Location: The Cotswolds, United Kingdom

Posted 21 November 2011 - 19:29

Interesting, I've noticed that it doesn't output the total at the end, after the while. Does this mean the while is not finishing?

I've tried turning error_reporting to -1 (all), no errors reported!

#8 Phouchg

Phouchg

    has stopped responding

  • Tech Issues Solved: 9
  • Joined: 28-March 11

Posted 21 November 2011 - 21:50

Sent you a PM with my solution (hopefully).

#9 CrispCreations

CrispCreations

    Neowinian

  • Joined: 19-April 04
  • Location: UK

Posted 22 November 2011 - 13:20

Hey guys,

Got a bit of an issue importing a large xml into my mysql database.

// snipped

Has anyone got any ideas how I can import these 200,000 tracks from XML into my mySQL database?


Thanks a lot!

200,000? more like 464,000 surely? (2320*200)

One change I would make is to not run one db insert for every record, you can insert 200 at a time quite easily with a slight change to your code in the second while loop

		if(!$xml = simplexml_load_file($url)) {
			echo 'Unable to load XML file';
			exit();
		} else {
			$i=0;
			// keep track of values to insert
			$values = array();
			while ($i < 200) {
				foreach($xml as $recenttracks) {
					$artist = $recenttracks->track[$i]->artist[0];
					$name = $recenttracks->track[$i]->name[0];
				}
				foreach($xml->recenttracks[0]->track[$i]->date[0]->attributes() as $a => $b) {
					$date = $b;
				}
				$artist = mysql_real_escape_string ($artist);
				$name = mysql_real_escape_string ($name);
				$date = mysql_real_escape_string ($date);
				echo 'Number: '.$total.' n: '.$n.' i: '.$i.'<br />';
				echo $date.'<br />';
				echo $name.'<br />';
				echo $artist.'<br /><br />';
				$i++;
				$total++;
				// add value to array
				$values[] = "('$artist', '$name', '$date', 'Unknown')";
			}
			// insert all 200 in one go
			if (!empty($values)) {
				$query = "INSERT INTO songs (Artist, Title, Time, DJ) VALUES " . implode(',',$values);
				mysql_query($query) or die(mysql_error());
			}
		}
		$n++;

That one change will reduce your db queries from 464000, down to 2320

#10 Phouchg

Phouchg

    has stopped responding

  • Tech Issues Solved: 9
  • Joined: 28-March 11

Posted 23 November 2011 - 15:27

I haven't received an answer from OP but I suppose my script works quite good, maybe even fully. So I'm publishing what I had cooked up.

<?php
    ini_set('memory_limit', '1024M');
    ini_set('max_execution_time', '180');
    $time_limit = 120;
    $this_time = 0;
    $cycle = time();
    if (($_SERVER['QUERY_STRING'] == '') || isset($_GET['help'])) {
	    echo <<<EOT
		    <pre>
		    Usage:<br />
			    {$_SERVER['PHP_SELF']}?parameters<br /><br />
		    Parameters:<br />
			    begin_at : (integer) which page should be the first (1..9999)<br />
			    end_at   : (integer) which page should be the last (<=9999)<br /><br />
		    There will be other parameters used during the execution and after it. Don't panic.<br />
		    Specifying them manually will only produce incorrect results in the end.<br /><br />
		    Have a nice day!</pre>
EOT;
	    die;
    }
    if (isset($_GET['start_time'])) {
	    $start_time = filter_var($_GET['start_time'], FILTER_VALIDATE_INT);
	    if (!$start_time) {
		    $start_time = time();
	    }
    } else {
	    $start_time = time();
    }
    if (isset($_GET['processed'])) {
	    $total = filter_var($_GET['processed'], FILTER_VALIDATE_INT);
	    if (!$total) {
		    $total = 0;
	    }
    } else {
	    $total = 0;
    }
    if (isset($_GET['begin_at'])) {
	    $begin_at = filter_var($_GET['begin_at'], FILTER_VALIDATE_INT);
	    if (!$begin_at) {
		    echo '<pre>Error: "begin_at" not specified or invalid. Fix your query string!</pre>';
		    die;
	    }
    } else {
	    echo '<pre>Error: "begin_at" not specified or invalid. Fix your query string!</pre>';
	    die;
    }
    if (($begin_at == 0) || ($begin_at > 9999)) {
	    echo '<pre>Error: "begin_at" is outside the safety limits (1..9999). Fix your query string!</pre>';
	    die;
    }
    if (isset($_GET['end_at'])) {
	    $end_at = filter_var($_GET['end_at'], FILTER_VALIDATE_INT);
	    if (!$end_at) {
		    echo '<pre>Error: "end_at" not specified or invalid. Fix your query string!</pre>';
		    die;
	    }
    } else {
	    echo '<pre>Error: "end_at" not specified or invalid. Fix your query string!</pre>';
	    die;
    }
    if ($end_at < $begin_at) {
	    echo '<pre>Error: "end_at" is less than "begin_at". Fix your query string!</pre>';
	    die;
    }
    if ($end_at > 9999) {
	    echo '<pre>Error: "end_at" is outside the safety limits ("beginat"..9999). Fix your query string!</pre>';
	    die;
    }
    $db_host = 'localhost';
    $db_user = ''; // replace with your own
    $db_pass = ''; // replace with your own
    $db_port = 3306;
    $db_name = ''; // replace with your own
    $db_link = mysqli_init();
    if ($db_link) {
	    if (mysqli_real_connect($db_link, $db_host, $db_user, $db_pass, $db_name, $db_port)) {
		    $stmt_insert = mysqli_prepare($db_link, 'INSERT INTO songs (Artist, Title, Time, DJ) VALUES (?, ?, ?, ?);');
		    if ($stmt_insert) {
			    if (mysqli_stmt_bind_param($stmt_insert, 'ssss', $artist, $name, $date, $dj)) {
				    if (mysqli_autocommit($db_link, false)) {
				    } else {
					    echo '<pre>Error: mysqli_autocommit() bungled.</pre>';
					    mysqli_stmt_close($stmt_insert);
					    mysqli_close($db_link);
					    die;
				    }
			    } else {
				    echo '<pre>Error: mysqli_stmt_bind_param() bungled.</pre>';
				    mysqli_stmt_close($stmt_insert);
				    mysqli_close($db_link);
				    die;
			    }
		    } else {
			    echo '<pre>Error: mysqli_prepare() bungled.</pre>';
			    mysqli_close($db_link);
			    die;
		    }
	    } else {
		    echo '<pre>Error: mysqli_real_connect() bungled.</pre>';
		    mysqli_close($db_link);
		    die;
	    }
    } else {
	    echo '<pre>Error: mysqli_init() bungled.</pre>';
	    die;
    }
    $error = 0;
    $url = 'http://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&user=bbcradio1&api_key=c910b0bc1e2d4e64964ebcd2d69c255c&limit=200&page=';
    $dj = 'Unknown';
    $finished = false;
    $redir = false;
    for ($page = $begin_at; $page <= $end_at; $page++) {
	    $xml = simplexml_load_file($url.$page);
	    if ($xml) {
		    foreach ($xml->recenttracks->track as $track) {
			    $artist = (string) $track->artist;
			    if ($artist == '') {
				    $finished = true;
			    } else {
				    $name = (string) $track->name;
				    foreach ($track->date->attributes() as $a => $b) {
					    $date = (string) $b;
				    }
				    if (mysqli_stmt_execute($stmt_insert)) {
					    $total++;
				    } else {
					    echo '<pre>Error: mysqli_stmt_execute() bungled.</pre>';
					    $error = 1;
					    break 2;
				    }
			    }
		    }
		    if (mysqli_commit($db_link)) {
			    if ($finished)
				    break;
		    } else {
			    echo '<pre>Error: mysqli_commit() bungled.</pre>';
			    $error = 1;
			    break;
		    }
	    } else {
		    echo '<pre>Error: simplexml_load_file() bungled.</pre>';
		    $error = 1;
		    break;
	    }
	    if ($page == $end_at) {
		    $finished = true;
		    break;
	    } else {
		    $this_time = $this_time + (time() - $cycle);
		    $cycle = time();
		    if ($this_time > $time_limit) {
				    $redir = true;
				    $begin_at = $page + 1;
				    break;
		    }
	    }
    }
    mysqli_stmt_close($stmt_insert);
    mysqli_close($db_link);
    if ($error) {
	    die;
    }
    if ($finished) {
	    $elapsed_time = time() - $start_time;
	    echo '<pre>Success: processed '.$total.' entries in ~'.$elapsed_time.' seconds. Have a nice day.</pre>';
    } else
    if ($redir) {
	    header('Location: '.$_SERVER['PHP_SELF'].'?begin_at='.$begin_at.'&end_at='.$end_at.'&start_time='.$start_time.'&processed='.$total);
    }
?>