?
Solved

parse / save XML data using Php / MySQL

Posted on 2011-05-03
3
Medium Priority
?
734 Views
Last Modified: 2012-05-11
I use curl using php and the successful URL call returns an XML (sample below).

 
<?xml version="1.0" encoding="UTF-8" ?>
<results>
	<product category="baby">
		<language>english</language>

		<value>160</value>

		<wholesale_value>10</wholesale_value>

		<stats>

			<sales year="2011">
				<type>broad</type>
				<avg_competition>low</avg_competition>
			</sales>

			<sales year="2010">
				<type>naarrow</type>
				<avg_competition>high</avg_competition>
			</sales>
		</stats>

	</product>
</results>

Open in new window


I need to parse the returned XML and save the information in a MySQL table. The primary identifier is       <product category="baby">, which is unique.

Any help is highly appreciated with a working PHP/ MySQL example to parse the XML and save it in the DB.
0
Comment
Question by:nainil
3 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 35583368
try using simplexml. The following link shows you how to use it:
http://www.willfitch.com/simplexml-tutorial-part1.html

the documentation/api is at:
http://php.net/manual/en/book.simplexml.php

Since you already have the remote xml as a string (the result from curl), you can use
simplexml_load_string() to create an object from it (this will make more sense once you read the first link)
0
 
LVL 5

Assisted Solution

by:dnzone88
dnzone88 earned 248 total points
ID: 35688098
here is a working example
<?php
$xml_str = '
<?xml version="1.0" encoding="UTF-8" ?>
<results>
	<product category="baby">
		<language>english</language>
		<value>160</value>
		<wholesale_value>10</wholesale_value>
		<stats>
			<sales year="2011">
				<type>broad</type>
				<avg_competition>low</avg_competition>
			</sales>
			<sales year="2010">
				<type>naarrow</type>
				<avg_competition>high</avg_competition>
			</sales>
		</stats>
	</product>
</results>';

$xml_obj = simplexml_load_string(trim($xml_str));

// save info directly into the database
if(count($xml_obj->product) > 0){
	foreach($xml_obj->product as $product){
		$key = $product['category'];
		$language =  $product->language;
		$value = $product->value;
		$wholesale = $product->wholesale_value;
		// create your query here
		$query = "insert into item (category, language, value, wholesale) values('$key', '$language', '$value', '$wholesale')";
		// execute $query here
		echo $query, '<br />';
		if(isset($product->stats)){
			$sales = $product->stats;
			if(count($sales->sales) > 0){
				foreach($sales->sales as $year){
					$yr = $year['year'];
					$type = $year->type;
					$avg = $year->avg_competition;
					// create your sub query here
					$query = "insert into sales (category, year, type, avg_competiton) values('$key', '$yr', '$type', '$avg')";
					// execute $query here
					echo $query, '<br />';
				}
			}
		}
	}
}
?>

Hope it help

Open in new window

0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 252 total points
ID: 35690633
http://www.laprbass.com/RAY_temp_nainil.php

Outputs: FOUND BABY
<?php // RAY_temp_nainil.php
error_reporting(E_ALL);
echo "<pre>";

$xml = <<<XML
<?xml version="1.0" encoding="UTF-8" ?>
<results>
	<product category="baby">
		<language>english</language>

		<value>160</value>

		<wholesale_value>10</wholesale_value>

		<stats>

			<sales year="2011">
				<type>broad</type>
				<avg_competition>low</avg_competition>
			</sales>

			<sales year="2010">
				<type>naarrow</type>
				<avg_competition>high</avg_competition>
			</sales>
		</stats>

	</product>
</results>
XML;

$obj = SimpleXML_Load_String($xml);

foreach ($obj->product as $p)
{
    if ($p["category"] == 'baby') echo "FOUND BABY";
}

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question