Solved

Trying to parse xml into database, but it stops.

Posted on 2012-12-30
9
293 Views
Last Modified: 2012-12-30
Hi,

I have a small problem with this function.
It need to parse an xml file and put the required details into my database, but it stops.

I know the reason, but don't know why my coding does not work (think it should)... and it's driving me insane.

# SMS XML FEED
function sms_xml() {
	
	$xml_url = '.....';

	# load the xml file
	$xml_data = file_get_contents($xml_url);

	# parse the file into an SimpleXML object
	$xml = new SimpleXMLElement($xml_data);

	# Create array with USD exchange rates
	$USD_exchange_rate = array();
	
	$USD_query = mysql_query("SELECT * FROM currency_exchange_rates WHERE base='USD' ") or die(mysql_error());
	while($USD_fetch = mysql_fetch_array($USD_query)) {

		$USD_exchange_rate[$USD_fetch['country']] = $USD_fetch['value'];
	}
	
	# let's fill the country array
	foreach ($xml->service->countries->country as $country) {
		$operators = array();
		
		# skip countries, that have not been approved yet
		//if ($country['approved'] == 'false') continue;
		
		# get all pricepoints, message profiles and operators
		foreach ($country->prices->price as $price_level) {
		
			$price = reset($price_level['amount']); // make array into string
			$currency = reset($price_level['currency']); // make array into string
			
			foreach ($price_level->message_profile as $profile) {
				$keyword = $profile['keyword'];
				$shortcode = $profile['shortcode'];
				
				foreach ($profile->operator as $operator) {
					$name = $operator['name'];
					$revenue = floatval($operator['revenue']);
					$billing_type = $operator['billing_type'];
					
					# PRICE
					$price_USD = round($price/$USD_exchange_rate[$currency], 2);
					
					# REVENUE
					$revenue_USD = round($revenue/$USD_exchange_rate[$currency], 2);
					
					# Insert operators into table
					mysql_query("INSERT INTO sms_operators (id, name, country, country_code, price, price_USD, revenue, revenue_USD, currency, billing_type, keyword, shortcode)
						values ('', '$name', '{$country['name']}', '{$country['code']}', '$price', '$price_USD', '$revenue', '$revenue_USD', '$currency', '$billing_type', '$keyword', '$shortcode' )") or die(mysql_error());
				}
			}
		}
		
		# Insert countries into table
		mysql_query("INSERT INTO sms_countries (id, code, name, vat, approved, text)
			values ('', '{$country['code']}', '{$country['name']}', '{$country['vat']}', '{$country['approved']}', '{$country->promotional_text->local}' )") or die(mysql_error());
	}
}

Open in new window


My function stops (nothing in the database) right before Russia.
The reason is probably the different prices (countries before Russia only have 1 price).

XML:
<services_api_response version="2.0">
	<status>
		<code>0</code>
		<message>OK</message>
	</status>
	<service id="gdfg445454hhggddfgf">
		<countries>
			<country code="RU" vat="18.00" name="Russia" approved="false">
				<prices>
					<price amount="168.99" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Ul'yanovsk GSM" billing_type="MO" default_billing_status="OK" revenue="63.01" name="Ul'yanovsk GSM"/>
							<operator code="BaicalWestCom" billing_type="MO" default_billing_status="OK" revenue="63.01" name="BaicalWestCom"/>
							<operator code="Tele2 RU" billing_type="MO" default_billing_status="OK" revenue="71.61" name="Tele2 RU"/>
							<operator code="Megafon" billing_type="MO" default_billing_status="OK" revenue="63.01" name="Megafon"/>
							<operator code="MTS" billing_type="MO" default_billing_status="OK" revenue="60.15" name="MTS"/>
						</message_profile>
					</price>
					<price amount="169.00" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Yaroslavl' GSM" billing_type="MO" default_billing_status="OK" revenue="57.29" name="Yaroslavl' GSM"/>
						</message_profile>
					</price>
					<price amount="170.00" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Beeline" billing_type="MO" default_billing_status="OK" revenue="67.71" name="Beeline"/>
							<operator code="Smarts" billing_type="MO" default_billing_status="OK" revenue="25.93" name="Smarts"/>
						</message_profile>
					</price>
					<price amount="171.10" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="NTC" billing_type="MO" default_billing_status="OK" revenue="46.40" name="NTC"/>
						</message_profile>
					</price>
					<price amount="175.82" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Motiv" billing_type="MO" default_billing_status="OK" revenue="59.60" name="Motiv"/>
							<operator code="Astrakhan' GSM" billing_type="MO" default_billing_status="OK" revenue="59.60" name="Astrakhan' GSM"/>
						</message_profile>
					</price>
				</prices>
				<promotional_text>
					<local>
						dfggfgdfgdgfg
					</local>
					<english>
						dfgdgfdggdfgdg
					</english>
				</promotional_text>
			</country>

Open in new window


(the xml code above is from my xml feed, other countries has been removed etc)
0
Comment
Question by:kgp43
  • 5
  • 4
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
You've got your work cut out for you.  XML is hierarchical and data bases are relational, so there is rarely a 1:1 mapping.  Instead you may need to "normalize" the data base using the structure of the XML.  Example: You might have a table of countries and a separate table of prices.  These could be joined by a junction table.  The "atomic" level is the XML tag that cannot have any elements inside it.  So with a quick look at the XML sample here, it seems you would an operator table that relates to a message_profile table that relates to a price table that relates to a country table.

Another alternative would be to ignore normalization and simply flatten all of the XML hierarchy, but that might make it impossible to recover the data in a hierarchically useful way.
0
 

Author Comment

by:kgp43
Comment Utility
Ye, I should probably look into normalize my database.
However, it does not change the fact that I have an error in my function - I still need to parse that xml feed i think, just insert it differently into my database (if I learn now to normalize my database).

I still can't see any error in my function. It should work with multiple prices ... "foreach ($country->prices->price as $price_level)"
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I'll make a valid copy of the XML string and see if I can show a way to parse it.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
When in doubt, var_dump() is your friend.  

The script below will successfully extract information from the XML object.  Use of (string) to cast the object attributes into a string may be helpful to creating your queries.  PHP echo will automatically turn objects into visible strings, but other PHP uses may be data-type dependent.

When your script runs a query that fails, the output of mysql_error() is only part of what you need to see. You also need to see the fully-resolved query string.  So make your query calls like this..

$sql = "INSERT...";
$res = mysql_query($sql);
if (!$res) die("FAIL: $sql BECAUSE: " . mysql_error());

Open in new window

<?php // RAY_temp_kgp43.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
echo '<pre>';

$xml = <<<ENDXML
<services_api_response version="2.0">
	<status>
		<code>0</code>
		<message>OK</message>
	</status>
	<service id="gdfg445454hhggddfgf">
		<countries>
			<country code="RU" vat="18.00" name="Russia" approved="false">
				<prices>
					<price amount="168.99" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Ul'yanovsk GSM" billing_type="MO" default_billing_status="OK" revenue="63.01" name="Ul'yanovsk GSM"/>
							<operator code="BaicalWestCom" billing_type="MO" default_billing_status="OK" revenue="63.01" name="BaicalWestCom"/>
							<operator code="Tele2 RU" billing_type="MO" default_billing_status="OK" revenue="71.61" name="Tele2 RU"/>
							<operator code="Megafon" billing_type="MO" default_billing_status="OK" revenue="63.01" name="Megafon"/>
							<operator code="MTS" billing_type="MO" default_billing_status="OK" revenue="60.15" name="MTS"/>
						</message_profile>
					</price>
					<price amount="169.00" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Yaroslavl' GSM" billing_type="MO" default_billing_status="OK" revenue="57.29" name="Yaroslavl' GSM"/>
						</message_profile>
					</price>
					<price amount="170.00" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Beeline" billing_type="MO" default_billing_status="OK" revenue="67.71" name="Beeline"/>
							<operator code="Smarts" billing_type="MO" default_billing_status="OK" revenue="25.93" name="Smarts"/>
						</message_profile>
					</price>
					<price amount="171.10" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="NTC" billing_type="MO" default_billing_status="OK" revenue="46.40" name="NTC"/>
						</message_profile>
					</price>
					<price amount="175.82" all_operators="false" vat_included="true" currency="RUB">
						<message_profile all_operators="false" shortcode="1111" keyword="FOR DOWNLOAD">
							<operator code="Motiv" billing_type="MO" default_billing_status="OK" revenue="59.60" name="Motiv"/>
							<operator code="Astrakhan' GSM" billing_type="MO" default_billing_status="OK" revenue="59.60" name="Astrakhan' GSM"/>
						</message_profile>
					</price>
				</prices>
				<promotional_text>
					<local>
						dfggfgdfgdgfg
					</local>
					<english>
						dfgdgfdggdfgdg
					</english>
				</promotional_text>
			</country>
		</countries>
	</service>
</services_api_response>
ENDXML;

// MAKE AN OBJECT
$obj = SimpleXML_Load_String($xml);

// USE AN ITERATOR TO ACCESS THE PROPERTIES OF THE OBJECT
foreach ($obj->service->countries->country as $country)
{
    // ACTIVATE THIS TO SEE THE COUNTRY OBJECT
    // var_dump($country);

    // USE THE ATTRIBUTES METHOD TO GET THE NAME
    $cn = (string)$country->attributes()->name;

    // ITERATE DEEPER INTO THE OBJECT
    foreach ($country->prices->price as $price)
    {
        $pa = (string)$price->attributes()->amount;
        $pc = (string)$price->attributes()->currency;

        // AND EVEN DEEPER
        foreach ($price->message_profile->operator as $operator)
        {
            $oc = (string)$operator->attributes()->code;
            $or = (string)$operator->attributes()->revenue;

            // SHOW THE DATA WE HAVE COLLECTED
            echo PHP_EOL . "$cn $pa $pc $oc $or";
        }
    }
}

Open in new window

Best regards, ~Ray
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:kgp43
Comment Utility
Hi, works great.

However, seems my problem is this (same error as previous):
operator code="Ul'yanovsk GSM"

Open in new window

Ul'yanovsk
When I try to put that into my database, then i get this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'yanovsk GSM', 'Russia', 'RU', '168', '5.53', '63', '2.07', 'RUB', 'MO', 'FOR DOW' at line 1

Open in new window

0
 

Author Comment

by:kgp43
Comment Utility
That was a fast fix it seems.
$o_name = (string)mysql_real_escape_string($operator->attributes()->name);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I think I would cast as string first, then escape().

As a practical matter you MUST use the escape functions on any external data before using the data in a query.
http://php.net/manual/en/function.mysql-real-escape-string.php
0
 

Author Comment

by:kgp43
Comment Utility
Thank's :)
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points, and thanks for using EE.  Happy New Year 2013, ~Ray
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now