Jump to content

Parsing an XML file using PHP & Upload it to a Database.


kbrice

Recommended Posts

I was wondering if anyone out there knows the proper way to upload XML data to a database.

I have the ability to parse the XML file & display the data to a web page.

AND I know how to INSERT data to my database, but i don't know how to combine both scripts.

So, does anyone have any idea how to do this. I've provided some examples below.

 

XML example:

 

<?xml version="1.0" encoding="ISO-8859-1"?>

<hotel>

<name>La Sommità</to>

<location>Puglia</from>

<country>Italy</heading>

</hotel>

 

SQL query to upload data.

 

$sql= "INSERT INTO hotelrecords ('id','name','location','country') " .

"VALUES ($idValue, $nameValue, $locationValue, $countryValue)";

mysql_query($sql, $conn)

or die('Could not upload data; ' . mysql_error());

 

PHP parse to Database.

??? That's where I need help.

 

 

Alright guys, this is the PHP parse code, It works great even with huge xml files. If there is a good spot to add the INSERT query, I think it would be within the endTag() function. Though one of the problem is that the query has to be written differently for each values I'm guessing. Hope this helps.

 

<?php
// Connect to the Database.
//==========================================	
define('SQL_HOST','********'); // stars to be replaced with the correct info
define('SQL_USER','********');
define('SQL_PASS','********');
define('SQL_DB','********');

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
  or die('Could not connect to the database; ' . mysql_error());

mysql_select_db(SQL_DB, $conn)
  or die('Could not select database; ' . mysql_error());


// PARSE XML  SETUP 
//==========================================	
$file = "filename.xml";

// function to handle the start tags 
function startTag($parser, $data){
    echo "<b>";
}

// function to handle the data between the tags 
function contents($parser, $data){
    echo $data;
}

// function to handle the end tags 
function endTag($parser, $data){
    echo "</b><br />";
}

$xml_parser = xml_parser_create();

xml_set_element_handler($xml_parser, "startTag", "endTag");

xml_set_character_data_handler($xml_parser, "contents");

$fp = fopen($file, "r");

$data = fread($fp, filesize($file));

if(!(xml_parse($xml_parser, $data))){
    die("Error on line " . xml_get_current_line_number($xml_parser));
}

xml_parser_free($xml_parser);

fclose($fp);
mysql_close($conn);
?> 

why dont you write a function to read the xml file and insert it into the databse you may ask arround if a text file is best or a blob im not sure on that for xml buy seing as its xml text feild should do teh job

<?php
$data = fread($fp, filesize($file)); // you must add this stuff after this line in ur code and fill in teh db connection stuff

//$data is teh data so do an INSERT to ur database with $data

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

$query = "INSERT INTO xml_table (id, xml) VALUES  (NULL, '".$data."')"; 
// the table should be 2 feilds 1 int called id and 1 called xml  of type text field auto increment id and make it primary key
$mysql_query($query);
mysql_close($link);
?>

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.