Insert XML data into MySQL with PHP

PHP
1

Hi,

My first post here, but have read many…!

I am working with an API that returns XML data as a local file - I need to get that XML data into a MySQL table. I am very nearly there (thanks to other posts on here) but need am struggling to get the INSERT statement to work.

My code at the moment opens the XML file and returns all fields as values, in a MySQL INSERT statement:


<?php require_once('../Connections/proprties.php');

$xml = simplexml_load_file('properties.xml');
$data = $fields = array();
foreach ($xml->xpath('property') as $property) {
    $fields = array_keys((array)($property));
    $data[] = '(' . join(',', (array)$property) . ')';
}
$sql = "INSERT INTO properties (" . join(', ', $fields) . ") VALUES\
" ;
$sql .= join (",\
", $data);

echo "<pre>$sql</pre>";
?>

This returns the following statement:-

INSERT INTO properties (prop_id, lastchanged, url) VALUES
(24791011, 2014-02-28T11:54:00, http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24791011),
(24787614, 2014-02-27T14:41:00, http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24787614),
(24768630, 2014-03-03T13:01:00, http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24768630),

This is almost perfect, though the MySQL insert is failing - I need to add apostrophies around the values, i.e. I need it to show:-

INSERT INTO properties (prop_id, lastchanged, url) VALUES
(‘24791011’, ‘2014-02-28T11:54:00’, ‘http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24791011’),
(‘24787614’, ‘2014-02-27T14:41:00’, ‘http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24787614’),
(‘24768630’, ‘2014-03-03T13:01:00’, ‘http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24768630’),

Any help gratefully recieved, I am out of ideas!

Thanks,

James

2

Declare

function quoteForMySQL($str) {
   return "'" . mysqli_real_escape_string($str) . "'"; }

And try changing

    $data[] = '(' . join(',', (array)$property) . ')';

to

    $data[] = '(' . join(',', array_map('quoteForMySQL', (array)$property)) . ')';
3

Hi Parallelist,

Thank you very much - that is exactly what I needed. Now exporting with apostrophies:

INSERT INTO properties (prop_id, lastchanged, url) VALUES
(‘24791011’,‘2014-02-28T11:54:00’,‘http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24791011’),
(‘24787614’,‘2014-02-27T14:41:00’,‘http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24787614’),
(‘24768630’,‘2014-03-03T13:01:00’,‘http://webservices.vebra.com/export/charterwyapi/v1/branch/27100/property/24768630’)

James