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**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — mishmash, Jan 14 '13 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, Jan 14 '13 at 21:21
Duplicate question -> http://stackoverflow.com/questions/2161722/parsing-xml-data-using-php-to-put-into-mysql-database — Reactgular, Jan 14 '13 at 21:22

Lawrence Cherone · Answer 1 · 2013-01-14T21:43:22.030

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 '13 at 21:43

answered Jan 14 '13 at 21:21

Lawrence Cherone

46,049
7
62
106

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? – donparalias Jan 14 '13 at 21:33
It returns an XML element object. http://php.net/manual/en/class.simplexmlelement.php – Reactgular Jan 14 '13 at 21:36
i am sorry i cant understand this manual..would it be easier if you could tell me how to access the elements? – donparalias Jan 14 '13 at 21:39
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 – donparalias Jan 14 '13 at 21:41

score 1 · Answer 2 · answered Jan 14 '13 at 21:22

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 '13 at 21:22

Reactgular

52,335
19
158
208

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! – halfer Jan 14 '13 at 21:25
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. – Reactgular Jan 14 '13 at 21:34
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? – donparalias Jan 14 '13 at 21:36
@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. – halfer Jan 14 '13 at 21:42
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. – donparalias Jan 14 '13 at 21:49
Please use PDO to access MySQL. I think it's a lot safer. Those "other" servers. Are they yours or something elses? Can you trust their data? – Reactgular Jan 14 '13 at 21:51
no no its my server. i just have 2 php scripts. one sends one receives and yes i am looking at PDO right now – donparalias Jan 14 '13 at 22:47

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

2 Answers2