Solved

Trying to parse xml into database, but it stops.

Posted on 2012-12-30
9
296 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 109

Expert Comment

by:Ray Paseur
ID: 38730910
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
ID: 38730930
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 109

Expert Comment

by:Ray Paseur
ID: 38730942
I'll make a valid copy of the XML string and see if I can show a way to parse it.
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38730983
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
 

Author Comment

by:kgp43
ID: 38731057
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
ID: 38731073
That was a fast fix it seems.
$o_name = (string)mysql_real_escape_string($operator->attributes()->name);

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 38731080
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
ID: 38731153
Thank's :)
0
 
LVL 109

Expert Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

776 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