Inserting data to MySQL database from XML feed

Question

Someone is helping me with this script to to insert XML data into MySQL, the XML data is from the audioscrobbler.com API, and I need to insert one row to the mysql database with the xml data $song_album, $song_albumURL and $song_tag, but even though I'm getting the data in the database, I adds the same song data like 12 times, and data is in different rows. Can I get HELP to correct this please?

<?php
error_reporting(E_ALL);
$mysql_hostname = "localhost";
$mysql_user = "user";
$mysql_password = "pass";
$mysql_database = "database";
$myAPIKey = '****';

// Connect
$mysqli = new mysqli($mysql_hostname, $mysql_user, $mysql_password, $mysql_database);

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$artist_raw = 'Metallica';
$title_raw = 'Whiskey In The Jar';
$artist = urlencode($artist_raw);
$title = urlencode($title_raw);

$xml = simplexml_load_file("http://ws.audioscrobbler.com/2.0/?method=track.getInfo&api_key=$myAPIKey&artist=$artist&track=$title") 

   or die("Error: Cannot create object");


foreach($xml->track->children() as $track => $data){
$song_album = $data->title;
$song_tag = $data->tag->name[0];
$song_albumURL = $data->image[2];

$xml = NULL;
$stmt = $mysqli->prepare("INSERT INTO `music` (`song_title`, `song_artist`, `song_album`, `song_tag`, `song_albumURL`) values ('{$title_raw}', '{$artist_raw}', '{$song_album}', '{$song_tag}', '{$song_albumURL}')");
$stmt->bind_param('s', $song_album, $song_albumURL, $song_tag);
$stmt->execute();
$stmt->close();

}
?>

Here is the XML raw data:

<lfm status="ok">
<track>
<id>1003737</id>
<name>Whiskey in the Jar</name>
<mbid>664ae92a-f25a-4df4-a564-66ee87dff1c8</mbid>
<url>...</url>
<duration>303000</duration>
<streamable fulltrack="0">0</streamable>
<listeners>433263</listeners>
<playcount>3389216</playcount>
<artist>
<name>Metallica</name>
<mbid>65f4f0c5-ef9e-490c-aee3-909e7ae6b2ab</mbid>
<url>http://www.last.fm/music/Metallica</url>
</artist>
<album position="9">
<artist>Metallica</artist>
<title>Garage, Inc.</title>
<mbid/>
<url>http://www.last.fm/music/Metallica/Garage,+Inc.</url>
<image size="small">http://userserve-ak.last.fm/serve/64s/54059755.png</image>
<image size="medium">http://userserve-ak.last.fm/serve/126/54059755.png</image>
<image size="large">http://userserve-ak.last.fm/serve/174s/54059755.png</image>
<image size="extralarge">http://userserve-ak.last.fm/serve/300x300/54059755.png
</image>
</album>
<toptags>
<tag>
<name>metal</name>
<url>http://www.last.fm/tag/metal</url>
</tag>
<tag>
<name>heavy metal</name>
<url>http://www.last.fm/tag/heavy%20metal</url>
</tag>
<tag>
<name>hard rock</name>
<url>http://www.last.fm/tag/hard%20rock</url>
</tag>
<tag>
<name>rock</name>
<url>http://www.last.fm/tag/rock</url>
</tag>
<tag>
<name>cover</name>
<url>http://www.last.fm/tag/cover</url>
</tag>
</toptags>
<wiki>...</wiki>
</track>
</lfm>

Please share with us the debugging you have done so far (Does the code complete? Do all functions return values as expected? Any error message?) — RandomSeed
– RandomSeed, Commented Jan 14, 2014 at 15:03
I get no error messages, all values return, but it just keeps inserting the data over and over. — AJ152
– AJ152, Commented Jan 14, 2014 at 15:04
Are you sure? It looks like $title_raw is hard-coded, though. I wouldn't be surprised if simplexml_load_file() always loaded the same URL ;) — RandomSeed
– RandomSeed, Commented Jan 14, 2014 at 15:12
It is hard-coded for this test, but in the real script it changes — AJ152
– AJ152, Commented Jan 14, 2014 at 15:31

RandomSeed · Accepted Answer · 2014-01-14 15:56:41Z

1

Your foreach iterates over children of the <track> node, and inserts (incomplete) rows at each iteration (for each child node, that is).

That's not what you want. You want to insert one single row here. Remove your loop and do something like:

$song_album = $xml->track->album->title;
$song_tag = $xml->track->toptags->tag[0]->name;
dbQuery('INSERT INTO ...');

answered Jan 14, 2014 at 15:56

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting data to MySQL database from XML feed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related