Insert XML Data to MySQL Table Using PHP

Question

I am trying to insert xml data to my sql but it is not inserting. how to write the foreach(xml->) for this type of xml.the xml is generated dynamically in this structure.this is a sample xml in this format

<?php
$xmlData =<<< END
<?xml version="1.0"?>
<Customer>
<id>1</id>
<name>Oluwafemi</name>
<address>Cresent Drive, TX</address>
<list>
<contact>56689</contact>
<telephone>5889745</telephone>
</list>
<offer>congrats</offer>
</Customer>
END;

$xml = simplexml_load_string($xmlData) or die("ERROR: Cannot create SimpleXML object");
$connection = mysqli_connect("localhost", "root", "", "Customers") or die ("ERROR: Cannot connect");

foreach ($xml->Customer as $Customer) {
$id = $Customer->id;
echo "$id";
$name =  $Customer->name;
$address = $Customer->address;

$sql = "INSERT INTO customerdata (id, name, address ) VALUES ('$id', '$name', '$address')";
mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");
}
mysqli_close($connection);
?>

Hint: The mysql commandline tool can import most XML files directly. See as well How to import XML file into MySQL database table using XML_LOAD(); function — hakre
– hakre, Commented Mar 15, 2015 at 13:02
Hntt#2: $xml in your example represents the document <Customer> element already. This mistake is common amongst new SimpleXML users. In your case you just don't need to foreach here as it's only a single customer row. — hakre
– hakre, Commented Mar 15, 2015 at 13:07

score 0 · Accepted Answer · 2015-03-15 06:35:44Z

0

You need to edit your XML document, encapsulate the document with this tag:
<Customers> ... </Customers>

So the beginning portion of your code should look like:

<?xml version="1.0"?>
$sXmlString =<<< END
<Customers>
  <Customer>
    <id>1</id>
    <name>Oluwafemi</name>
    <address>Cresent Drive, TX</address>
  </Customer>
</Customers>
END;

EDIT: OP is unable to edit the XML provided, I assume it is structured where new customers would be added in with additional id, name, address entities. Here is the updated code for OP:

<?php
$xmlData =<<< END
<?xml version="1.0"?>
<Customer>
  <id>1</id>
  <name>Oluwafemi</name>
  <address>Cresent Drive, TX</address>
  <id>2</id>
  <name>Rob</name>
  <address> 123 Longhorn </address>
</Customer>
END;

$xml = simplexml_load_string($xmlData) or die("ERROR: Cannot create SimpleXML object");
$connection = mysqli_connect("localhost", "root", "", "Customers") or die ("ERROR: Cannot connect");

/* Assumes that the number of IDs = number of customers */
$size = sizeOf($xml->id);
$i = 0; //index

/* Add each customer to the database, See how we reference it as    $xml->ENTITY[INDEX] */
while($i != $size) 
{
    //echo $xml->id[$i]; //Test
    $sql = "INSERT INTO customerdata (id, name, address ) VALUES ('$xml->id[$i]', '$xml->name[$i]', '$xml->address[$i]')";
    mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");

    $i++; //increment index
}

mysqli_close($connection);

I also added in an additional customer id with data. You can remove that and the code will still work fine.

edited Mar 15, 2015 at 6:35

answered Mar 15, 2015 at 5:14

user1893702

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

6 Comments

user3243931 Over a year ago

Hi RobMullins, the xml is generated dynamically. any help for that xml

user1893702 Over a year ago

You should really structure your XML better. Since you cannot, I will update the answer...

user1893702 Over a year ago

Please let me know if you have any questions about this.

user3243931 Over a year ago

Thanku Rob Mullins. got the expected output what i want. thanku so much

user3243931 Over a year ago

if there is one more element inside how can we get the data.

|

Vladimir Ramik · Accepted Answer · 2015-03-15 05:06:35Z

Assuming you have multiple customers...

$sXmlString =<<< END
<?xml version="1.0"?>
<Content>
<Customer>
<id>1</id>
<name>Oluwafemi</name>
<address>Cresent Drive, TX</address>
</Customer>
<Customer>
<id>2</id>
<name>Oluwafemi2</name>
<address>Cresent Drive 2, TX</address>
</Customer>
</Content>
END;

$oXml       = simplexml_load_string($sXmlString);
$sJson      = json_encode( $oXml );
$aContent   = json_decode( $sJson, TRUE );
$aCustomers = $aContent[ 'Customer' ];
var_dump( $aCustomers );
$iCountCustomers = count( $aCustomers );
for( $i = 0; $i < $iCountCustomers; ++$i )
{
    $sId      = $aCustomers[ $i ][ 'id' ];
    $sName    = $aCustomers[ $i ][ 'name' ];
    $sAddress = $aCustomers[ $i ][ 'address' ];
    var_dump( $sId );
    var_dump( $sName );
    var_dump( $sAddress );
}

Collectives™ on Stack Overflow

Insert XML Data to MySQL Table Using PHP

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related