How to pass a parsed XML into a mySQL database using PHP?

Question

I have a local server , where i am receiving xml files. The xml files are received just fine but i have problem on how to pass my XML file into my database.

I retrieve the xml file simple as that :

$xml_post = file_get_contents('php://input');

The xml files i receive looks like this :

<city>
<id>1081</id>
<name>athens</name>
<country>Greece</country>
<info>etc etc etc</info>
</city>

I have manually created a database with phpmyadmin with the exact same nodes so i can save everything correctly.

Now i want to insert what i am getting in the database with mysql_query. Something like this :

mysql_query("INSERT INTO cities (id, city ,country , info)
  VALUES (4 , 'athens' , 'greece' , 'etc etc etc'");

But which are the variables that i have stored my xml to use them as values in the statement above? How can i parse the xml file that i have to store all the nodes in variables and then pass them correctly to the database?

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — mishmash
– mishmash, Commented Jan 14, 2013 at 21:20
I think you are mistaken thinking that the XML is already parsed. It is not, that's your code's job to do. After that you have the values from the XML and can send it to the database. — Sven
– Sven, Commented Jan 14, 2013 at 21:21
Duplicate question -> stackoverflow.com/questions/2161722/… — Reactgular
– Reactgular, Commented Jan 14, 2013 at 21:22

Lawrence Cherone · Accepted Answer · 2013-01-14 21:43:22Z

2

Try using simplexml_load_string()

Example usage:

<?php
$xml = '<city>
<id>1081</id>
<name>athens</name>
<country>Greece</country>
<info>etc etc etc</info>
</city>
';

$xml = simplexml_load_string($xml);
//1081
echo $xml->id;
//athens
echo $xml->name;
?>

If you have multiple nodes within your XML each node will be an array of objects:

$xml = '
<citys>
    <city>
        <id>1081</id>
        <name>athens</name>
        <country>Greece</country>
        <info>etc etc etc</info>
    </city>
    <city>
        <id>1082</id>
        <name>somwhere else</name>
        <country>Spain</country>
        <info>etc etc etc</info>
    </city>

</citys>
';

$xml = simplexml_load_string($xml);
//print_r($xml);

foreach($xml->city as $val){
    echo $val->name;
}

//or
echo $xml->city[0]->name;
//athens
echo $xml->city[1]->name;

edited Jan 14, 2013 at 21:43

answered Jan 14, 2013 at 21:21

Lawrence Cherone

46.7k7 gold badges66 silver badges107 bronze badges

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

4 Comments

donparalias Over a year ago

i tried $xml = simplexml_load_string($xml_post); but then when i try to echo something , for example echo $xml ->id; i get an error : Notice: Trying to get property of non-object . how can i access the elements?

Reactgular Over a year ago

It returns an XML element object. php.net/manual/en/class.simplexmlelement.php

donparalias Over a year ago

i am sorry i cant understand this manual..would it be easier if you could tell me how to access the elements?

donparalias Over a year ago

ok but how do i put in into the database. i try : mysql_query("INSERT INTO cities (id, city ,country , info) VALUES ($xml ->id , $xml ->city , $xml ->country , $xml ->info"); but nothing is inserted on my db

Reactgular · Accepted Answer · 2013-01-14 21:22:03Z

1

You would need to parse the XML into data readable by PHP, and then use that data in your SQL insert.

PHP SimpleXML Tutorial might be a place to start.

answered Jan 14, 2013 at 21:22

Reactgular

55.1k24 gold badges175 silver badges219 bronze badges

7 Comments

halfer Over a year ago

Yes, with the proviso that if the files won't fit in memory, an XML stream reader would be better. But they are a fair bit more complicated to use!

Reactgular Over a year ago

I think a stream reader would be to complicated for the OP, because they often need callbacks and only report the start/end of tags. But, they handle poorly formed XML better. It all depends on where the XML is coming from.

donparalias Over a year ago

I tried to parse the xml using $xml = simplexml_load_string($xml_post); but then when i try to echo something , for example echo $xml ->id; i get an error : Notice: Trying to get property of non-object . how can i access the elements?

halfer Over a year ago

@donparalias - what does print_r($xml); give you? That will show you the structure you are working with, and is a good debugging technique generally.

donparalias Over a year ago

the thing is that in this script i am receive xml files from other scripts , so if i just run it i get the error cause there is no xml file.. but now i think i am parsing it ok. i just dont know how to put it in the database.

|

Collectives™ on Stack Overflow

How to pass a parsed XML into a mySQL database using PHP?

2 Answers 2

4 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related