Importing XML Feed Into MYSQL Server

[SmithyWiffy]

Hi 

 

I've searched the net high and low for an answer and found a script on this forum which I have used as shown below however the script seems to run fine and output to the screen with the correct data, however it doesn't update the database on the server. I've checked permissions and ran a simple query static query which works: (mysqli_query($con,"INSERT INTO feeforeviews (FEEDBACKID,COUNT,DATE)

VALUES ('99999,10,3 Hour(s) ago')") and it updates the "feeforeviews" table fine.

 

This is the script I would like working if possible which parses the file feefofeedtest.xml and should import the data in the database table "feeforeviews"

 

I modified the script from here:

 

http://forums.phpfreaks.com/topic/273301-simplexml-to-mysql/

http://forums.phpfreaks.com/user/3105-barand/

 

Any help greatly appreciated.

 

<?php

// Create connection

$con=mysqli_connect("localhost","user","pass","database");

 

// Check connection

if (mysqli_connect_errno())

  {

  echo "Failed to connect to MySQL: " . mysqli_connect_error();

  }

?>

 

 

 <?php

$xml = simplexml_load_file('feefofeedtest.xml');

$data = $fields = array();

foreach ($xml->xpath('FEEDBACK') as $FEEDBACK) {

    $fields = array_keys((array)($FEEDBACK));

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

}

 

$sql = "INSERT INTO feeforeviews (" . join(', ', $fields) . ") VALUES\n" ;

$sql .= join (",\n", $data);

 

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

?>

 

This outputs the following onscreen which looks fine, however it doesn't update the server.

 

INSERT INTO feeforeviews (FEEDBACKID, COUNT, DATE, HREVIEWDATE, DESCRIPTION, PRODUCTCODE, LINK, FACEBOOKSHARELINK, PRODUCTRATING, SERVICERATING, CUSTOMERCOMMENT, SHORTCUSTOMERCOMMENT, READMOREURL) VALUES
(99998, 1, 3 Hour(s) ago, 2014-03-18T10:19:03, Personalised Swarovski Crystal Hiball Glass, DG10387, http://www.keepitpersonal.co.uk, http://www.facebook.com/sharer.php?u=http%3A%2F%2Fwww.feefo.com%2FGB%2Fen%2Freviews%2FKeep-It-Personal%2F%3Fid%3D326784%26servicefeedbackid%3D83637, 5, ++, ++, jgsjhgdfhjgjsd, Service rating : jgsjhgdfhjgjsd, http://www.feefo.com/GB/en/reviews/Keep-It-Personal/?id=326784&servicefeedbackid=83637),
(88888, 2, 5 Hour(s) ago, 2014-03-18T08:02:34, Personalised Swarovski Champagne Flute, DG10492, http://www.keepitpersonal.co.uk, http://www.facebook.com/sharer.php?u=http%3A%2F%2Fwww.feefo.com%2FGB%2Fen%2Freviews%2FKeep-It-Personal%2F%3Fid%3D326784%26servicefeedbackid%3D86279, NA, ++, Excellent website, easy to understand and use, jgsjhgdfhjgjsd, http://www.feefo.com/GB/en/reviews/Keep-It-Personal/?id=326784&servicefeedbackid=86279)

 

 

Example XML file:

 

<?xml version="1.0" encoding="UTF-8"?><FEEDBACKLIST>

    <FEEDBACK>

        <FEEDBACKID>99998</FEEDBACKID>

        <COUNT>1</COUNT>

        <DATE> 3 Hour(s) ago</DATE>

        <HREVIEWDATE>2014-03-18T10:19:03</HREVIEWDATE>

        <DESCRIPTION>Personalised Swarovski Crystal Hiball Glass</DESCRIPTION>

        <PRODUCTCODE>DG10387</PRODUCTCODE>

        <LINK>http://www.keepitpersonal.co.uk</LINK>

        <FACEBOOKSHARELINK>http://www.facebook.com/sharer.php?u=http%3A%2F%2Fwww.feefo.com%2FGB%2Fen%2Freviews%2FKeep-It-Personal%2F%3Fid%3D326784%26servicefeedbackid%3D83637</FACEBOOKSHARELINK>

        <HREVIEWRATING>5</HREVIEWRATING>

        <PRODUCTRATING>++</PRODUCTRATING>

        <SERVICERATING>++</SERVICERATING>

        <CUSTOMERCOMMENT>jgsjhgdfhjgjsd</CUSTOMERCOMMENT>

        <SHORTCUSTOMERCOMMENT>Service rating : jgsjhgdfhjgjsd</SHORTCUSTOMERCOMMENT>

        <READMOREURL>http://www.feefo.com/GB/en/reviews/Keep-It-Personal/?id=326784&servicefeedbackid=83637</READMOREURL>

    </FEEDBACK>

    <FEEDBACK>

        <FEEDBACKID>88888</FEEDBACKID>

        <COUNT>2</COUNT>

        <DATE> 5 Hour(s) ago</DATE>

        <HREVIEWDATE>2014-03-18T08:02:34</HREVIEWDATE>

        <DESCRIPTION>Personalised Swarovski Champagne Flute</DESCRIPTION>

        <PRODUCTCODE>DG10492</PRODUCTCODE>

        <LINK>http://www.keepitpersonal.co.uk</LINK>

        <FACEBOOKSHARELINK>http://www.facebook.com/sharer.php?u=http%3A%2F%2Fwww.feefo.com%2FGB%2Fen%2Freviews%2FKeep-It-Personal%2F%3Fid%3D326784%26servicefeedbackid%3D86279</FACEBOOKSHARELINK>

        <PRODUCTRATING>NA</PRODUCTRATING>

        <SERVICERATING>++</SERVICERATING>

        <CUSTOMERCOMMENT>Excellent website, easy to understand and use</CUSTOMERCOMMENT>

        <SHORTCUSTOMERCOMMENT>jgsjhgdfhjgjsd</SHORTCUSTOMERCOMMENT>

        <READMOREURL>http://www.feefo.com/GB/en/reviews/Keep-It-Personal/?id=326784&servicefeedbackid=86279</READMOREURL>

    </FEEDBACK></FEEDBACKLIST>

 

[Barand]

The sample code just displays the query to demonstrate what it is doing. Are you actually executing the sql produced with mysqli_query()?

[SmithyWiffy]

oops i didnt even notice this...i've been looking at this to long... i guess i just need to change this code with mysqli_query i will try later this evening. many thanks for your reply.

[SmithyWiffy]

I don't have the knowledge to construct the correct string , i've tried to find more examples online but this is what I've got but it's pulling up an error:

 

Parse error: syntax error, unexpected ';' 

 

mysqli_query($con,"INSERT INTO feeforeviews (" . join(', ', $fields) . ") VALUES\n" ;

mysqli_query($con,' .= join (",\n", $data);

 

Any ideas?

[SmithyWiffy]

I think I am getting further, the code (see below) is executing but it pulls up an error:

 

Error creating database: 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 'Hour(s) ago, 2014-03-18T10:19:03, Personalised Swarovski Crystal Hiball Glass, D' at line 2

 

$sql = "INSERT INTO feeforeviews (" . join(', ', $fields) . ") VALUES\n" ;

$sql .= join (",\n", $data);

if (mysqli_query($con,$sql))

  {

  echo "Database my_db created successfully";

  }

else

  {

  echo "Error creating database: " . mysqli_error($con);

  }

[Barand]

String values in your query need to be enclosed in single quotes

EG

VALUES (..., '3 Hour(s) ago', '2014-03-18T10:19:03', 'Personalised Swarovski Crystal Hiball Glass', 'D', ... )

In the code you copied/pasted these quotes were already present in the XML data. They are not present in yours so you need to insert them when joining the values.

 

So this line

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

needs to be

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

[SmithyWiffy]

Thanks that's fixed this issue however I've stumbled across another issue now with ' apostrophes now.

 

The customer comments can include any characters and it looks like I will need to escape them before inserting into the DB.

 

The following link has an example which I think will fix the issue http://www.php.net/manual/en/mysqli.real-escape-string.php but I've tried to implement without success - I am so close now, any ideas?

 

$xml = simplexml_load_file('feefofeedtest.xml');

$data = $fields = array();

foreach ($xml->xpath('FEEDBACK') as $FEEDBACK) {

    $fields = array_keys((array)($FEEDBACK));

$data[] = "('" . join("', '", (array)$FEEDBACK) . "')";}

 

$sql = "INSERT IGNORE INTO feeforeviews (" . join(', ', $fields) . ") VALUES\n" ;

$sql .= join (",\n", $data);

if (mysqli_query($con,$sql))

  {

  echo "Database my_db created successfully";

  }

else

  {

  echo "Error creating database: " . mysqli_error($con);

  }

[SmithyWiffy]

something like this, still doesn't work though

 

$xml = simplexml_load_file('feefofeedtest.xml');

$data = $fields = array();

foreach ($xml->xpath('FEEDBACK') as $FEEDBACK) {

    $fields = array_keys((array)($FEEDBACK));

$data[] = "('" . join("', '", (array)$FEEDBACK) . "')", mysql_real_escape_string($CUSTOMERCOMMENT), mysql_real_escape_string($SHORTCUSTOMERCOMMENT);}

 

$sql = "INSERT IGNORE INTO feeforeviews (" . join(', ', $fields) . ") VALUES\n" ;

$sql .= join (",\n", $data);

if (mysqli_query($con,$sql))

  {

  echo "Database my_db created successfully";

  }

else

  {

  echo "Error creating database: " . mysqli_error($con);

  }

 

?>

[Barand]

Mea culpa. Should be escaping the the data anyway.

 

Try this to escape each field

foreach ($xml->xpath('FEEDBACK') as $FEEDBACK) {
    $fields = array_keys((array)($FEEDBACK));
    $data[] = "('" . join("', '", array_map(array($con, real_escape_string), (array)$FEEDBACK)) . "')";
}

[SmithyWiffy]

Many thanks that's solved this problem, but i've just come across yet another issue...Column count doesn't match value count at row 1

 

The problem appears to be that the XML TREE doesn't contain the tags which are not populated. For example <FEEDBACKID>77787</FEEDBACKID> includes <HREVIEWRATING>5</HREVIEWRATING>, however  <FEEDBACKID>86279</FEEDBACKID> does not include this tag so I assume we get the error..doh.

 

 

 

<?xml version="1.0" encoding="UTF-8"?><FEEDBACKLIST>

    <FEEDBACK>

        <FEEDBACKID>77787</FEEDBACKID>

        <COUNT>1</COUNT>

        <DATE> 3 Hour(s) ago</DATE>

        <HREVIEWDATE>2014-03-18T10:19:03</HREVIEWDATE>

        <DESCRIPTION>Special character's</DESCRIPTION>

        <PRODUCTCODE>DG10387</PRODUCTCODE>

        <LINK>http://www.keepitpersonal.co.uk</LINK>

        <FACEBOOKSHARELINK>http://www.facebook.com/sharer.php?u=http%3A%2F%2Fwww.feefo.com%2FGB%2Fen%2Freviews%2FKeep-It-Personal%2F%3Fid%3D326784%26servicefeedbackid%3D83637</FACEBOOKSHARELINK>

        <HREVIEWRATING>5</HREVIEWRATING>

        <PRODUCTRATING>++</PRODUCTRATING>

        <SERVICERATING>++</SERVICERATING>

        <CUSTOMERCOMMENT>jgsjhgdfhjgjsd'</CUSTOMERCOMMENT>

        <SHORTCUSTOMERCOMMENT>Service rating : jgsjhgdfhjgjsd</SHORTCUSTOMERCOMMENT>

        <READMOREURL>http://www.feefo.com/GB/en/reviews/Keep-It-Personal/?id=326784&servicefeedbackid=83637</READMOREURL>

    </FEEDBACK>

   <FEEDBACK>

        <FEEDBACKID>86279</FEEDBACKID>

        <COUNT>2</COUNT>

        <DATE> 5 Hour(s) ago</DATE>

        <HREVIEWDATE>2014-03-18T08:02:34</HREVIEWDATE>

        <DESCRIPTION>Personalised Swarovski Champagne Flute</DESCRIPTION>

        <PRODUCTCODE>DG10492</PRODUCTCODE>

        <LINK>http://www.keepitpersonal.co.uk</LINK>

        <FACEBOOKSHARELINK>http://www.facebook.com/sharer.php?u=http%3A%2F%2Fwww.feefo.com%2FGB%2Fen%2Freviews%2FKeep-It-Personal%2F%3Fid%3D326784%26servicefeedbackid%3D86279</FACEBOOKSHARELINK>

        <PRODUCTRATING>NA</PRODUCTRATING>

        <SERVICERATING>++</SERVICERATING>

        <CUSTOMERCOMMENT>Excellent website, easy to understand and use</CUSTOMERCOMMENT>

        <SHORTCUSTOMERCOMMENT>Excellent website, easy to understand and use</SHORTCUSTOMERCOMMENT>

        <READMOREURL>http://www.feefo.com/GB/en/reviews/Keep-It-Personal/?id=326784&servicefeedbackid=86279</READMOREURL>

    </FEEDBACK>

    </FEEDBACKLIST>

[SmithyWiffy]

I'm wondering if this is part of the solution. 

 

http://stackoverflow.com/questions/12581103/xpath-for-missing-node

 

I've had enough for tonight, i'm off to get some sleep!

[SmithyWiffy]

Actually this looks like the solution, definitely off to bed now!

 

http://stackoverflow.com/questions/7854330/php-xmlreader-dealing-with-missing-element

[SmithyWiffy]

hmmm still no joy with this

[Barand]

Can you post your table structure?

SHOW CREATE TABLE freeforeviews

[SmithyWiffy]

Hi 

 

For some reason the show command returns no result, but this is a screen print of phpmyadmin, I hope this helps. (the sql user login has full permissions so cannot see why the show command fails)

 

 

 

regards

Andy

[SmithyWiffy]

FEEDBACKID COUNT DATE HREVIEWDATE DESCRIPTION PRODUCTCODE LINK FACEBOOKSHARELINK HREVIEWRATING PRODUCTRATING SERVICERATING CUSTOMERCOMMENT SHORTCUSTOMERCOMMENT READMOREURL ITEM PRODUCTLATEST SERVICELATEST VENDORCOMMENT SHORTVENDORCOMMENT DATE2 VENDORCOMMENT3 CUSTOMERCOMMENT4 SERVICERATING5 PRODUCTRATING6

[Barand]

From that list it appears your xml records only contain (different) subsets of the whole record. You may have to break the rules and do a separate insert for each record.

$xml = simplexml_load_file('feefofeedtest.xml');
$data = $fields = array();
foreach ($xml->xpath('FEEDBACK') as $FEEDBACK) {
    $fields = array_keys((array)($FEEDBACK));
    $data = "('" . join("', '", (array)$FEEDBACK) . "')";
    $sql = "INSERT IGNORE INTO feeforeviews (" . join(', ', $fields) . ") VALUES\n" ;
    $sql .= join (",\n", $data);
    if (!mysqli_query($con,$sql))
    {
        echo "Error creating database: " . mysqli_error($con);
    }
}

[SmithyWiffy]

Thanks for your reply Barand, their appears to be a syntax error somewhere by Values now by the looks of it...

 

Warning: join() [function.join]: Invalid arguments passed in /home/giftshop/public_html/responsive/feefo/testfeefo.php on line 20
Error creating database: 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 '' at line 1

---------------------------------------

 

Warning: join() [function.join]: Invalid arguments passed in /home/giftshop/public_html/responsive/feefo/testfeefo.php on line 20
INSERT IGNORE INTO feeforeviews (FEEDBACKID, COUNT, DATE, HREVIEWDATE, DESCRIPTION, PRODUCTCODE, LINK, FACEBOOKSHARELINK, PRODUCTRATING, SERVICERATING, CUSTOMERCOMMENT, SHORTCUSTOMERCOMMENT, READMOREURL) VALUES

[Barand]

try

$xml = simplexml_load_file('feefofeedtest.xml');
$data = $fields = array();
foreach ($xml->xpath('FEEDBACK') as $FEEDBACK) {
    $fields = array_keys((array)($FEEDBACK));
    $data = join("', '", array_map(array($con, real_escape_string), (array)$FEEDBACK));
    $sql = "INSERT IGNORE INTO feeforeviews (" . join(', ', $fields) . ") VALUES ('$data')" ;
    if (!mysqli_query($con,$sql))
    {
        echo "Error creating database: " . mysqli_error($con);
    }
}