Parse xml-file and insert into mysql database

Question

I'm trying parse an xml file and insert it into an mysql database. The xml file (syntest.xml) looks like this:

<synonyms>
<syn level="4.0"><w1>LP</w1><w2>vinylskiva</w2></syn>
<syn level="3.7"><w1>OK</w1><w2>javisst</w2></syn>
<syn level="4.6"><w1>OS</w1><w2>operativsystem</w2></syn>
</synonyms>

I have parsed the xml file using simplexml_load_file, and when I use an foreach loop to print the records in the browser the loop goes through all records and everyting looks fine. But when I try to insert the data into the database, the loop doesnt iterate and only one record is inserted.

The code looks like this:

<?php

$con = mysql_connect("localhost","user","username");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("db_name", $con);


if(!$xml=simplexml_load_file('syntest.xml')){
trigger_error('Error reading XML file',E_USER_ERROR);
}



foreach ($xml as $syn) {
$w1 = $syn->w1;  
$w2 = $syn->w2;     
}

$sql="INSERT INTO db_name (w2, w1)
VALUES
('$w1','$w2')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Records added";

mysql_close($con)
?>

All help much appreciated!

Ok, thanks, and how do I insert the record in the loop? I'm quite new to php, so I would really appreciate if you could show me the correct php! — user1009453, Jan 10 '12 at 19:15

Valeh Hajiyev · Accepted Answer · 2012-01-10T20:58:45.623

2

The place of mysql_query isn`t correct. You must insert records in loop:

foreach ($xml as $syn) 
{
    $w1 = $syn->w1;  
    $w2 = $syn->w2;  

    $sql    = "INSERT INTO db_name (w1, w2) VALUES ('$w1','$w2')";
    $query  = mysql_query($sql);

    if (!$query)
    {
        echo ('Error: ' . mysql_error());
    }
    else 
    {
        echo "Record added";
    }
}
mysql_close($con);

edited Jan 10 '12 at 20:58

answered Jan 10 '12 at 18:34

Valeh Hajiyev

3,216
4
19
28

huh ? that do not change much , since you are calling mysql_query after loop – SergeS Jan 10 '12 at 18:35
Thank you for your answer, but as SergeS says, that doesn't change much for me. Still only one record added to database. Any suggestions? – user1009453 Jan 10 '12 at 19:31
Thanks for taking the time with this. I managed to insert the whole file now with this script. So that's a huge step forward. But it was only when I deleted the primare key for the column id. Any idea why that is? – user1009453 Jan 10 '12 at 21:03

Parse xml-file and insert into mysql database

1 Answers1

Linked