Z33M@N Posted June 4, 2010 Share Posted June 4, 2010 Hi there, Like the title says, I'm trying to save the XML data in a mysql table. The Table: `idbooks` INT NOT NULL AUTO_INCREMENT , `author` MEDIUMTEXT NULL , `publisher` MEDIUMTEXT NULL , `title` MEDIUMTEXT NULL , PRIMARY KEY (`idbooks`) ) The XML: <?xml version="1.0" encoding="UTF-8"?> <!-- This is just a comment, ignore it --> <books> <book> <author>Jack Herrington</author> <title>PHP Hacks</title> <publisher>O'Reilly</publisher> </book> <book> <author>Jack Herrington</author> <title>Podcasting Hacks</title> <publisher>O'Reilly</publisher> </book> </books> My Code: function get_links() { // connect to database $con = mysql_connect("localhost","root","root"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("xml_import_db", $con); //set the dom file to load. $doc = new DOMDocument(); $doc->load( 'xml.xml' ); //get xml data and display it, then store in to mysql table. $books = $doc->getElementsByTagName( "book" ); foreach( $books as $book ) { $authors = $book->getElementsByTagName( "author" ); $author = $authors->item(0)->nodeValue; $publishers = $book->getElementsByTagName( "publisher" ); $publisher = $publishers->item(0)->nodeValue; $titles = $book->getElementsByTagName( "title" ); $title = $titles->item(0)->nodeValue; echo "$title - $author - $publisher<br />"; $title = serialize($title); $author = serialize($author); $publisher = serialize($publisher); $query = mysql_query("INSERT INTO books(author, publisher, title) values ('$author', '$publisher', '$title')"); echo $query; } //mysql_close(); //close connection //Return the links //return $links; } The data echo's correctly but the data does not get stored to my table, what am I doing wrong? Your help will be appreciated! Thanks guys. Link to comment https://forums.phpfreaks.com/topic/203872-trying-to-save-xml-data-in-mysql-table/ Share on other sites More sharing options...
kenrbnsn Posted June 4, 2010 Share Posted June 4, 2010 Use the function mysql_real_escape_string on the data and check for errors in your query: <?php $title = mysql_real_escape_string($title); $author = mysql_real_escape_string($author); $publisher = mysql_real_escape_string($publisher); $q = "INSERT INTO books(author, publisher, title) values ('$author', '$publisher', '$title')"; $rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); ?> Ken Link to comment https://forums.phpfreaks.com/topic/203872-trying-to-save-xml-data-in-mysql-table/#findComment-1067761 Share on other sites More sharing options...
Z33M@N Posted June 4, 2010 Author Share Posted June 4, 2010 Use the function mysql_real_escape_string on the data and check for errors in your query: <?php $title = mysql_real_escape_string($title); $author = mysql_real_escape_string($author); $publisher = mysql_real_escape_string($publisher); $q = "INSERT INTO books(author, publisher, title) values ('$author', '$publisher', '$title')"; $rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); ?> Ken Hi Ken, Thank you very much, you're awesome It's working just the way I want it to work. Marking as solved! Link to comment https://forums.phpfreaks.com/topic/203872-trying-to-save-xml-data-in-mysql-table/#findComment-1067764 Share on other sites More sharing options...
devwalt Posted June 28, 2010 Share Posted June 28, 2010 Question: What if the XML file has two authors like below but the second book has one: <?xml version="1.0" encoding="UTF-8"?> <!-- This is just a comment, ignore it --> <books> <book> <author>Jack Herrington</author> <author>Someone Else</author> <title>PHP Hacks</title> <publisher>O'Reilly</publisher> </book> <book> <author>Jack Herrington</author> <title>Podcasting Hacks</title> <publisher>O'Reilly</publisher> </book> </books> Only the first node is stored in the database. I used this particular forum response to help with my own xml file, however, I have a recipe database with several items listed and each item has it's own node. Link to comment https://forums.phpfreaks.com/topic/203872-trying-to-save-xml-data-in-mysql-table/#findComment-1078007 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.