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

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

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

hmmm still no joy with this

Actually this looks like the solution, definitely off to bed now! http://stackoverflow.com/questions/7854330/php-xmlreader-dealing-with-missing-element

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!

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>

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); } ?>

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); }

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); }

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?

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.

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>