convert xml data into mysql insert/update query using php

Question

I am working on this project that basically reads the data from xml file and insert/update the data into mysql database.

Here is the sample.xml file:

<table name="movies">
    <column name="movie_name">Titanic</column>
    <column name="dvd">40</column>
    <column name="blueray">5</column>
    <column name="netflix">4</column>
    <column name="amazon">3</column>
</table>

I break down the problem into: 1) Get the values from XML file. 2) Insert the extracted values into mysql database.

I am able to work on insert the values to database, but the hard part is getting the values from xml making me crazy.

Here is my Database Table looks like:

Database name: Movies Columns: movie_name, dvd, blueray, netflix, amazon

Here is the code that I tried to extract the attribute values from xml.

<?php
$source = 'sample.xml';
// load as string
$xml = new SimpleXMLElement($source,null,true);
$movie_name= $xml->column->attributes()->name;
echo $movie_name. "\n";
?>

Output: Instead of getting the name of movie "Titanic", I get "movie_name".

You're getting back exactly what you asked for. attributes() looks at the attributes, name gives you the name of the attribute. — Robbert
– Robbert, Commented Sep 3, 2014 at 21:54
->attributes() pulls out metadata about the field you're dealing with. nothing to do with the values stored in the field. — Marc B
– Marc B, Commented Sep 3, 2014 at 21:54

Robbert · Accepted Answer · 2014-09-03 21:56:32Z

1

You're getting back exactly what you asked for. attributes() looks at the attributes, name gives you the name of the attribute. You can get the name by doing this

$movieName = (string)$xml->column;

If you want all the values, you can loop through the columns

foreach($xml->column as $xColumn) {
  $value = (string)$xColumn;
}

answered Sep 3, 2014 at 21:56

Robbert

6,6226 gold badges37 silver badges64 bronze badges

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

Comments

score 0 · Accepted Answer · 2014-09-03 22:47:18Z

0

You can use this query:

<?php
$source = 'sample.xml';
// load as string
$xml = new SimpleXMLElement($source,null,true);
$movie_name= $xml->column->attributes()->name;

//echo $movie_name. "\n";

$table = $xml->attributes()->name;    

    $columns ="";
    foreach($xml as $column){
        $columns .= (string)$column->attributes()->name .",";
    }
    $columns = rtrim($columns, ",");

    $values ="";
    foreach($xml->column as $value){
        $values .= $value ."," ;
    }
    $values = rtrim($values, ",");

$query = " INSERTO INTO  $table ( $columns ) VALUES ( $values ) ";   

echo $query;

?>

edited Sep 3, 2014 at 22:47

answered Sep 3, 2014 at 22:40

user4004613

Comments

Mike · Accepted Answer · 2014-09-03 21:58:50Z

0

the value of the name attribute IS movie_name. Titanic is the value of the Column element that has the name attribute with the value.

answered Sep 3, 2014 at 21:58

Mike

113 bronze badges

Collectives™ on Stack Overflow

convert xml data into mysql insert/update query using php

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related