Avatar of kgp43
kgp43
Flag for Denmark asked on

Trying to parse xml into database, but it stops.

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)
PHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Ray Paseur

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.
kgp43

ASKER
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)"
Ray Paseur

I'll make a valid copy of the XML string and see if I can show a way to parse it.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
kgp43

ASKER
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

kgp43

ASKER
That was a fast fix it seems.
$o_name = (string)mysql_real_escape_string($operator->attributes()->name);

Open in new window

Ray Paseur

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
kgp43

ASKER
Thank's :)
Ray Paseur

Thanks for the points, and thanks for using EE.  Happy New Year 2013, ~Ray