Xml to sql database

Question

iam trying to make an php file to import xml to sql database, but i have this error-:

Warning: Invalid argument supplied for foreach() in /home/morrisse/public_html/xml_to_database.php on line 18

My code:

        $url ="http://www.example/example.xml";
    $ch = curl_init();

    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_URL, $URL);  //GET THE URL CONTENDS

    $data = curl_exec ($ch);  //execute the curl request

    curl_close($ch);

    $xml = simplexml_load_string($data);
    foreach ($xml -> track as $row) {
                $title = $row -> name;
                $artist = $row -> artist;
                $duration = $row -> duration;
    // performing sql query

    $sql = "INSERT INTO 'test_xml' ('title', 'artist', 'duration')"
                . "VALUES ('$title', '$artist', '$duration')";

    $result = mysql_query($sql);
    if (!result) {
                    echo 'MYSQL ERROR';
                }   else {
                echo 'SUCESSO';
                }
                }

my xml:

      <lfm status="ok">
    <track>
    <id>602366984</id>
    <name>Barbarism Begins At Home (BBC TV Tube performance)</name>
    <mbid/>
    <url>http://www.last.fm/music/The+Smiths/_/Barbarism+Begins+At+Home+(BBC+TV+Tube+performance)</url>
    <duration>384000</duration>
    <streamable fulltrack="0">0</streamable>
    <listeners>10</listeners>
    <playcount>24</playcount>
    <artist>
    <name>The Smiths</name>
    <mbid>40f5d9e4-2de7-4f2d-ad41-e31a9a9fea27</mbid>
    <url>http://www.last.fm/music/The+Smiths</url>
    </artist>
    <toptags>
    </toptags>
    </track>
    </lfm>

help please?

The full code:

<?php

$url ="http://www.morrisseyradio.com/shoutcastinfo/cache/track.xml";
$ch = curl_init();

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_URL, $URL);  //GET THE URL CONTENDS

$data = curl_exec ($ch);  //execute the curl request

curl_close($ch);
$con=mysql_connect("localhost", "", ""); //connect to database
mysql_select_db("morrisse_forum", $con) 
if (!$con) {
    die('Could not connect: ' . mysql_error());
}; // select database


libxml_use_internal_errors(true);
$xml = simplexml_load_string($data);


if ($xml === false) {
    $errors = libxml_get_errors();
    foreach ($errors as $error) {
        echo "XML Error at line $error->line: $error->message\n";
    }
}
 else {
foreach ($xml -> track as $row) {
            $title = $row -> name;
            $artist = $row -> artist;
            $duration = $row -> duration;
// performing sql query

$sql = "INSERT INTO 'test_xml' ('title', 'artist', 'duration')"
            . "VALUES ('$title', '$artist', '$duration')";

$result = mysql_query($sql);
if (!result) {
                echo 'MYSQL ERROR';
            }   else {
            echo 'SUCESSO';
            }
            }
            }

?>

Danger: You are using an obsolete database API and should use a modern replacement. You are also vulnerable to SQL injection attacks that a modern API would make it easier to defend yourself from. — Quentin
– Quentin, Commented Jan 9, 2015 at 23:06
Please edit your question to show how you created the variable called $xml in your program. Your error message is telling you that variable isn't set up right. — O. Jones
– O. Jones, Commented Jan 9, 2015 at 23:32
the error message is correct. it's good you have had it, the error was there for real. — hakre
– hakre, Commented Apr 3, 2015 at 10:10

Handsome_devil · Accepted Answer · 2015-01-10 11:36:32Z

1

For anyone the answer is: First thing: in the line:

curl_setopt($ch, CURLOPT_URL, $URL);

the "$URL" must be Lower

Second: In this line:

$sql = "INSERT INTO 'test_xml' ('title', 'artist', 'duration')"

the "'" must be "`"

answered Jan 10, 2015 at 11:36

Handsome_devil

279 bronze badges

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

1 Comment

O. Jones Over a year ago

Right. The single quotes within the sql need to be backticks (same key as ~ on a US keyboard). Also, artist is a compound element in this XML. You might want $row->artist->name

O. Jones · Accepted Answer · 2015-01-10 00:14:27Z

It's pretty clear that your call to simplexml_load_string() failed because the xml was not well formed. When that happens you get back false rather than a usable xml element.

Here's what I suggest you do about this. Add some error-tracking code, as follows.

libxml_use_internal_errors(true);
$xml = simplexml_load_string($data);

if ($xml === false) {
    $errors = libxml_get_errors();
    foreach ($errors as $error) {
        echo "XML Error at line $error->line: $error->message\n";
    }
}
 else {
     foreach ( $xml->track as $row ) {  /* your code continues here  ... */

There's clearly something wrong with the xml you're getting back from that server you're using with curl. This code will help you figure out what is wrong.

i have done that and nothing appears, no errors, just a blank page, my full code is on the question now.
i have put an error code in the database connection and still nothing.

Collectives™ on Stack Overflow

Xml to sql database

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related