Importing large XML into mySQL without shell access

November 20, 2011

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
[/CODE]

I've tried this php script, but it takes a long time to execute and it stops at number 10199

[CODE]
<?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.' ';
echo $date.' ';
echo $name.' ';
echo $artist.' ';
$i++;
$total++;
mysql_query("INSERT INTO songs (Artist, Title, Time, DJ) VALUES ('$artist', '$name', '$date', 'Unknown')") or die(mysql_error());
}
}
$n++;
}
?>
[/CODE]

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

Thanks a lot!

November 20, 2011

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/manual/en/function.set-time-limit.php

November 20, 2011

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.

5,682 · November 21, 2011

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.

November 21, 2011

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;
?>
[/CODE]

Output (This proves that there must be something wrong with my code!):

[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
[/CODE]

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).

5,682 · November 21, 2011

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.

November 21, 2011

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!

5,682 · November 21, 2011

Sent you a PM with my solution (hopefully).

November 22, 2011

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++;
[/CODE]

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

5,682 · November 23, 2011

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.

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

Sign In

Importing large XML into mySQL without shell access

Question

Tjcrazy

Link to comment

Share on other sites

9 answers to this question

Recommended Posts

Clearskies

Link to comment

Share on other sites

Tjcrazy

Link to comment

Share on other sites

Phouchg

Link to comment

Share on other sites

Tjcrazy

Link to comment

Share on other sites

Phouchg

Link to comment

Share on other sites

Tjcrazy

Link to comment

Share on other sites

Phouchg

Link to comment

Share on other sites

CrispCreations

Link to comment

Share on other sites

Phouchg

Link to comment

Share on other sites

Recently Browsing 0 members

Similar Content