This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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.
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:
(the xml code above is from my xml feed, other countries has been removed etc)
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());
}
}
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>
(the xml code above is from my xml feed, other countries has been removed etc)
Who is Participating?
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.
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.
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)"
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)"
Hi, works great.
However, seems my problem is this (same error as previous):
When I try to put that into my database, then i get this error:
However, seems my problem is this (same error as previous):
operator code="Ul'yanovsk GSM"
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
That was a fast fix it seems.
$o_name = (string)mysql_real_escape_string($operator->attributes()->name);
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
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
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..
Open in new window
Open in new window
Best regards, ~Ray