hi david. I try to parse xml in mysql but it seems that I do something wrong.
It parse empty fields except orders_products table.
I also need to loop in sql1 and sql2.
Thanks in advance,
Demis
<?php
header("Content-Type: text/html; charset=utf-8");
require("MagicParser.php");
$filename = "Orders.xml";
include 'global/con.php';
function myRecordHandler($product)
{
$sql = "INSERT INTO orders SET
orders_id = '".$product["ORDERS_ID"]."',
customers_id = '".$product["CUSTOMERS_ID"]."',
customers_name = '".$product["CUSTOMERS_NAME"]."',
customers_company = '".$product["CUSTOMERS_COMPANY"]."',
customers_street_address = '".$product["CUSTOMERS_STREET_ADDRESS"]."',
customers_suburb = '".$product["CUSTOMERS_SUBURB"]."',
customers_city = '".$product["CUSTOMERS_CITY"]."',
customers_postcode = '".$product["CUSTOMERS_POSTCODE"]."',
customers_state = '".$product["CUSTOMERS_STATE"]."',
customers_country = '".$product["CUSTOMERS_COUNTRY"]."',
customers_telephone = '".$product["CUSTOMERS_TELEPHONE"]."',
customers_email_address = '".$product["CUSTOMERS_EMAIL_ADDRESS"]."',
customers_address_format_id = '".$product["CUSTOMERS_ADDRESS_FORMAT_ID"]."',
delivery_name = '".$product["DELIVERY_NAME"]."',
delivery_company = '".$product["DELIVERY_COMPANY"]."',
delivery_street_address = '".$product["DELIVERY_STREET_ADDRESS"]."',
delivery_suburb = '".$product["DELIVERY_SUBURB"]."',
delivery_city = '".$product["DELIVERY_CITY"]."',
delivery_postcode = '".$product["DELIVERY_POSTCODE"]."',
delivery_state = '".$product["DELIVERY_STATE"]."',
delivery_country = '".$product["DELIVERY_COUNTRY"]."',
delivery_address_format_id = '".$product["DELIVERY_ADDRESS_FORMAT_ID"]."',
billing_name = '".$product["BILLING_NAME"]."',
billing_company = '".$product["BILLING_COMPANY"]."',
billing_street_address = '".$product["BILLING_STREET_ADDRESS"]."',
billing_suburb = '".$product["BILLING_SUBURB"]."',
billing_city = '".$product["BILLING_CITY"]."',
billing_postcode = '".$product["BILLING_POSTCODE"]."',
billing_state = '".$product["BILLING_STATE"]."',
billing_country = '".$product["BILLING_COUNTRY"]."',
billing_address_format_id = '".$product["BILLING_ADDRESS_FORMAT_ID"]."',
payment_method = '".$product["PAYMENT_METHOD"]."',
cc_type = '".$product["CC_TYPE"]."',
cc_owner = '".$product["CC_OWNER"]."',
cc_number = '".$product["CC_NUMBER"]."',
cc_expires = '".$product["CC_EXPIRES"]."',
cvvnumber = '".$product["CVVNUMBER"]."',
date_purchased = '".$product["DATE_PURCHASED"]."',
orders_status = '".$product["ORDER_STATUS"]."',
orders_date_finished = '".$product["ORDERS_DATE_FINISHED"]."',
currency = '".$product["CURRENCY"]."',
currency_value = '".$product["CURRENCY_VALUE"]."',
purchased_without_account = '".$product["PURCHASED_WITHOUT_ACCOUNT"]."',
comments = '".$product["COMMENTS"]."',
order_type_id = '".$product["ORDER_TYPE_ID"]."'";
if (@mysql_query($sql)) {
echo '<p>orders has been added.</p>';
} else {
echo '<p>Error adding submitted orders: ' . mysql_error() . '</p>';
}
$sql1 = "INSERT INTO orders_products SET
orders_products_id = '".$product["ORDERS_PRODUCTS_ID"]."',
orders_id = '".$product["ORDERS_ID"]."',
products_id = '".$product["PRODUCT_ID"]."',
products_model = '".$product["PRODUCTS_MODEL"]."',
products_name = '".$product["PRODUCTS_NAME"]."',
products_price = '".$product["PRODUCTS_PRICE"]."',
final_price = '".$product["FINAL_PRICE"]."',
products_tax = '".$product["PRODUCTS_TAX"]."',
products_quantity = '".$product["PRODUCTS_QUANTITY"]."'";
if (@mysql_query($sql1)) {
echo '<p>products has been added.</p>';
} else {
echo '<p>Error adding products: ' . mysql_error() . '</p>';
}
$sql2 = "INSERT INTO orders_products_attributes SET
orders_products_attributes_id = '".$product["ORDERS_PRODUCTS_ATTRIBUTES_ID"]."',
orders_id = '".$product["ORDERS_ID"]."',
orders_products_id = '".$product["ORDERS_PRODUCTS_ID"]."',
products_options = '".$product["PRODUCTS_OPTIONS"]."',
products_options_values = '".$product["PRODUCTS_OPTIONS_VALUES"]."',
options_values_price = '".$product["OPTIONS_VALUES_PRICE"]."',
price_prefix = '".$product["PRICE_PREFIX"]."'";
if (@mysql_query($sql2)) {
echo '<p>attributes has been added.</p>';
} else {
echo '<p>Error adding submitted attributes: ' . mysql_error() . '</p>';
}
}
MagicParser_parse($filename,"myRecordHandler","xml|CUSTOMERORDERS/ORDERSPRODUCTS/");
print mysql_error();
?>
Hello David,
Thanks for that really fast answer! :)
So, I did something like this and it seems that it worked fine for me.
//example of insert
products_options_values = '".mysql_escape_string($product2["PRODUCTS_OPTIONS_VALUES"])."',
//example of MagicParser parse
MagicParser_parse($filename,"myRecordHandler","xml|CUSTOMERORDERS/ORDERS/");
MagicParser_parse($filename,"myRecordHandler1","xml|CUSTOMERORDERS/ORDERSPRODUCTS/");
MagicParser_parse($filename,"myRecordHandler2","xml|CUSTOMERORDERS/ORDERSPRODUCTSATTRIBUTES/");
Once more thanks for your help!
Cheers,
Demis
Hello Demis,
The most likely cause is that the value of some fields is breaking the SQL. When you use data from a feed to construct an SQL statement, you should use the mysql_escape_string() function to make sure that it is safe. For example, instead of directly using:
$product["CUSTOMERS_NAME"]
...you would use:
mysql_escape_string($product["CUSTOMERS_NAME"])
...for example:
customers_name = '".mysql_escape_string($product["CUSTOMERS_NAME"])."',
Hope this helps!
Cheers,
David.