Trying to save XML data in Mysql table

[Z33M@N]

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.

[kenrbnsn]

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

 

[Z33M@N]

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! 

[devwalt]

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.