parse xml file to mysql database

Question

thijscream -2 Junior Poster in Training

13 Years Ago

hey people,
i have the following problem, i'm setting up a webshop with over 2k items and every 6months the prices are changing(wines) so i was wondering how to import an xml file into a mysql database without having to go in phpmyadmin. this because i don't want the people for who i'm making the website have access there to destroy the whole database. this is what i got so far:

<?php
	include "header.php";
?>
<br><br><br>
<h1>Database updaten</h1>
<form enctype="multipart/form-data" 
  action="import.php" method="post">
  <input type="hidden" name="MAX_FILE_SIZE" value="20000000" />
  <table width="600">
  <tr>
  <td>Names file:</td>
  <td><input type="file" name="file" /></td>
  <td><input type="submit" value="Upload" /></td>
  </tr>
  </table>
  </form>
  </body>
  </html>

here i choose the file i want to import,
and then i run true the following script:

<?php
include ("header.php");
$query = "TRUNCATE TABLE  `wijnen`";
$result = mysql_query($query) or die('Error : ' . mysql_error());
$data = array();  
 function add_person( $id, $domein, $naam, $land, $streek, $substreek, $jaar, $type, $smaak, $formaat, $druivensoort, $prijsnormaal,
   $prijs6, $prijs12, $prijs24, $info, $actieid, $afbeelding, $kleur, $titel, $description, $keywords, $afbeeldingalt, $spijs, $leverancier, $levertijd )
  {
    global $data;  
     $data []= array(
  'id' => $id,
  'domein' => $domein,
  'naam' => $naam,
  'land' => $land, 
  'streek' => $streek,
  'substreek' => $substreek,
  'jaar' => $jaar,
  'type' => $type,
  'smaak' => $smaak,
  'formaat' => $formaat,
  'druivensoort' => $druivensoort,
  'prijsnormaal' => $prijsnormaal,
  'prijs6' => $prijs6,
  'prijs12' => $prijs12,
  'prijs24' => $prijs24,
  'info' => $info,
  'actieid' => $actieid,
  'afbeelding' => $afbeelding,
  'kleur' => $kleur,
  'titel' => $titel,
  'description' => $description,
  'keywords' => $keywords,
  'afbeeldingalt' => $afbeeldingalt,
  'spijs' => $spijs,
  'leverancier' => $leverancier,
  'levertijd' => $levertijd
  );
      }
         if ( $_FILES['file']['tmp_name'] )
           {
            $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
             
              $rows = $dom->getElementsByTagName( 'Row' );
                $first_row = true;
                 foreach ($rows as $row)
                  {  if ( !$first_row )
                    {
                      $id = "";
  $domein = "";
  $naam = "";
  $land = "";
  $streek = "";
  $substreek = "";
  $jaar = "";
  $type = "";
  $smaak = "";
  $formaat = "";
  $druivensoort = "";
  $prijsnormaal = "";
  $prijs6 = "";
  $prijs12 = "";
  $prijs24 = "";
  $info = "";
  $actieid = "";
  $afbeelding = "";
  $kleur = "";
  $titel = "";
  $description = "";
  $keywords = "";
  $afbeeldingalt = "";
  $spijs = "";
  $leverancier = "";
  $levertijd = "";
                           $index = 1;
                            $cells = $row->getElementsByTagName( 'Cell' ); 
                            foreach( $cells as $cell )
                             {
                               $ind = $cell->getAttribute( 'Index' );
                                if ( $ind != null ) $index = $ind;
                                   if ( $index == 1 ) $id = $cell->nodeValue;
  if ( $index == 2 ) $domein = $cell->nodeValue;
  if ( $index == 3 ) $naam = $cell->nodeValue;
  if ( $index == 4 ) $land = $cell->nodeValue;
  if ( $index == 5 ) $streek = $cell->nodeValue;
  if ( $index == 6 ) $substreek = $cell->nodeValue;
  if ( $index == 7 ) $jaar = $cell->nodeValue;
  if ( $index == 8 ) $type = $cell->nodeValue;
  if ( $index == 9 ) $smaak = $cell->nodeValue;
  if ( $index == 10 ) $formaat = $cell->nodeValue;
  if ( $index == 11 ) $druivensoort = $cell->nodeValue;
  if ( $index == 12 ) $prijsnormaal = $cell->nodeValue;
  if ( $index == 13 ) $prijs6 = $cell->nodeValue;
  if ( $index == 14 ) $prijs12 = $cell->nodeValue;
  if ( $index == 15 ) $prijs24 = $cell->nodeValue;
  if ( $index == 16 ) $info = $cell->nodeValue;
  if ( $index == 17 ) $actieid = $cell->nodeValue;
  if ( $index == 18 ) $afbeelding = $cell->nodeValue;
  if ( $index == 19 ) $kleur = $cell->nodeValue;
  if ( $index == 20 ) $titel = $cell->nodeValue;
  if ( $index == 21 ) $description = $cell->nodeValue;
  if ( $index == 22 ) $keywords = $cell->nodeValue;
  if ( $index == 23 ) $afbeeldingalt = $cell->nodeValue;
  if ( $index == 24 ) $spijs = $cell->nodeValue;
  if ( $index == 25 ) $leverancier = $cell->nodeValue;
  if ( $index == 26 ) $levertijd = $cell->nodeValue;
  
                                        $index += 1;
                                         } 
                                         add_person( $id, $domein, $naam, $land, $streek, $substreek, $jaar, $type, $smaak, $formaat, $druivensoort, $prijsnormaal, $prijs6, $prijs12, $prijs24, $info, $actieid, $afbeelding, $kleur, $titel, $description, $keywords, $afbeeldingalt, $spijs, $leverancier, $levertijd );
                                           }
                                            $first_row = false;
                                             }
                                              }
                        foreach($data as $row) 
                        
                       { 
			$rowz = mysql_real_escape_string($row['domein']);
            mysql_query("insert into wijnen values('','$rowz','$row[naam]','$row[land]','$row[streek]','$row[substreek]','$row[jaar]','$row[type]','$row[smaak]',
			'$row[formaat]','$row[druivensoort]','$row[prijsnormaal]','$row[prijs6]','$row[prijs12]','$row[prijs24]','$row[info]','$row[actieid]','$row[afbeelding]',
			'$row[kleur]','$row[titel]','$row[description]','$row[keywords]','$row[afbeeldingalt]','$row[spijs]','$row[leverancier]','$row[levertijd]')");
              }
echo "<META HTTP-EQUIV='refresh' CONTENT='2; URL=bestandkiezen.php'>";
?>

now my problem, when i have signs like ë ï ö ü ä é í ó ú á and so on they aren't parsed into the database the correct way. anyone knows how i can solve this?

html-css mysql php programming-construct xml

3 Contributors
32 Replies
651 Views
3 Days Discussion Span
Latest Post 13 Years Ago Latest Post by jonnysmiths

C#Jaap 5 Junior Poster in Training

13 Years Ago

Try this information
http://nl2.php.net/manual/en/function.mysql-set-charset.php

C#Jaap 5 Junior Poster in Training

13 Years Ago

The error you get is a warning. See http://nl.php.net/manual/en/domdocument.load.php. More appropriate way to do this is to create a Domdocument and call its method: Doc->Load(filename);

This does not affect your 185 rows item. May you can find something in the error log of apache.

C#Jaap 5 Junior Poster in Training

13 Years Ago

I hope this will help you somewhat. Make sure you can see the query so you can see what's wrong with is.

<?php
include ("header.php");

main();
//to find out the problem leave this
//echo "<META HTTP-EQUIV='refresh' CONTENT='2; URL=bestandkiezen.php'>";
exit();


function main()
{
    try
    { 
        $query = "TRUNCATE TABLE  `wijnen`";
        if( !doQuery($query) ) die();
    
        $dom = new DOMDocument();
        $dom->Load($_FILES['file']['tmp_name']);
        
        $rows = $dom->getElementsByTagName( 'Row' );
        $first_row = true;
        foreach ($rows as $row)
        {  
            if ( !$first_row )
            {
                doQuery(GetRowQuery($row));
            }
        }
    }
    catch(Exception $e)
    {
        //to find out the problem echo the lines, for production use e.g. a logfile
        echo "Exception catched: " . $e->getMessage() . "<br/>"
    }
}

function doQuery($sql)
{
    $return = false;
    try
    {
        $result = mysql_query(mysql_real_escape_string($sql);
        if( !$result)
        {
            //to find out the problem echo the lines, for production use e.g. a logfile
            echo "Invalid query: " . mysql_error()  . "<br/>";
            echo "Query [$sql]<<br/>";
        }
        else
        {
            $return = true;
        } 
    }
    catch( Exception $e)
    {
        //to find out the problem echo the lines, for production use e.g. a logfile
        echo "Exception catched: " . $e->getMessage() . "<br/>"
        echo "Query [$sql]<<br/>";
    }
    
    return $return;
}

function getRowQuery( $row )
{
    $data = array();
    
    $index = 0
    //get data from the cells
    $cells = $row->getElementsByTagName( 'Cell' ); 
    foreach( $cells as $cell )
    {
        $ind = $cell->getAttribute( 'Index' );

        if ( $ind != null ) 
        {
            $index = $ind;
        }
        else
        {
            $index++;
        }
        
        switch( $index)
        {
            case 1:
                //'domein' should be a column name in the database
                $data['domein'] = $cell->nodeValue;
                break;
            case 2: 
                break;
            case    
                ...
            case 26:
                break;
            default
                echo "This wasn't really expected";
        }
    }
    
    //create the query with the available data
    $columns = "";
    $values = "";
    foreach($data as $key => $value)
    {
        $columns .= ", " . $key;
        $values .= ", '" . $value . "'";
    }
    
    return "insert into wijnen (" . substr($columns, 2) . ") values (" . substr($values, 2) . ")"; 
}

?>

thijscream commented: thanks alot :) +3

C#Jaap 5 Junior Poster in Training

13 Years Ago

That's right. My fault: forgot to set first_row false

foreach ($rows as $row)
        {  
            if ( !$first_row )
            {
                doQuery(GetRowQuery($row));
                /**
                 * what you can do to echo the query is this:
                 * $query = GetRowQuery($row);
                 * echo $query;
                 * doQuery($query);
                 **/
            }
            else
            {
                $first_row = false;
            }
        }

C#Jaap 5 Junior Poster in Training

13 Years Ago

Sorry sorry sorry I advised you badly by the line

mysql_query(mysql_real_escape_string($sql);

This will result in things like VALUES (\'red\', .... and will never work.

Apply the mysql_real_escape_string() to each separate value.

//create the query with the available data
    $columns = "";
    $values = "";
    foreach($data as $key => $value)
    {
        $columns .= ", " . $key;
        echo $columns . "<br/>"; 

        $escVal = mysql_real_escape_string($value);
        if( !$escVal )
        {
            echo 'Invalid escape: ' . mysql_error();
            $values .= ", ''";
        }
        else
        {
            $values .= ", '" . $escVal . "'";
        }
        echo $values . "<br/>";
    }

Please check also the variables with which you call the function doQuery....

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

thijscream -2 Junior Poster in Training · Answer 1 · 2011-04-27T19:22:12+00:00

thanks for the great help :)
this solved my first problem. i'm also getting the following error:
Strict Standards: Non-static method DOMDocument::load() should not be called statically in C:\xampp\htdocs\wijnkado\administratie\import.php on line 41

i don't know if the next issue is has to do anything with this. the script imports ~185 rows in my database but after that it stops and does nothing anymore.
anyone know how i can solve this?

thijscream -2 Junior Poster in Training · Answer 2 · 2011-04-27T20:21:13+00:00

ok if its only a warning it's ok..but still can't figure out why my XML file isn't full loading in :S
does anyone see something i do wrong

C#Jaap 5 Junior Poster in Training · Answer 3 · 2011-04-27T20:52:29+00:00

Maybe the XML contains data that couldn't be processed.

thijscream -2 Junior Poster in Training · Answer 4 · 2011-04-28T13:11:26+00:00

Maybe the XML contains data that couldn't be processed.

maby someone can take a look @ the xml, it's located here:
http://kerstenswijnhandel.nl/wijnen%20kerstens%20wijnhandel%20site.xml

C#Jaap 5 Junior Poster in Training · Answer 5 · 2011-04-28T13:55:48+00:00

Hello, I don't see any obviously wrong. Maybe you can split up the problem into parts. First try to dump all row on to screen. If the script can print all rows onto screen (you can use print var_export($var, true) see http://nl2.php.net/manual/en/function.var-export.php then something may be wrong with uploading it into the database. If you use PHP 5 you can make use of the try-catch-finally struct to catch errors. When catched you can print the message to screen. Alternatively you can setup your environment to debug the code.

Good luck!

thijscream -2 Junior Poster in Training · Answer 6 · 2011-04-28T13:56:59+00:00

i found out the xml is ok, i printed all the data on my webpage and that's working fine. it just doesn't parse all rows, anyone has a solution for this?
maby place a break between the sql queries? if that's possible how do i do that?

thijscream -2 Junior Poster in Training · Answer 7 · 2011-04-28T14:05:28+00:00

also figured out that's not working :S
anyone has any idea's left?

C#Jaap 5 Junior Poster in Training · Answer 8 · 2011-04-28T14:10:57+00:00

I just looked at your code. Why do you first put all data into an array an afterwards loop through the array to insert the data into the database? Why don't you directly insert these data? Instead of add_person() you creat a function insert_db

function insert_db(......)
{
    try
    {
        $sql = "insert into .....";
        $result = mysql_query(mysql_real_escape_string($sql));
        if (!$result)
        {
            print 'Invalid query: ' . mysql_error();
        }
    }
    catch(....)
    {
         ......
    }
}

thijscream -2 Junior Poster in Training · Answer 9 · 2011-04-28T16:04:19+00:00

i just made a log from the errors: