how to insert xml to mysql with php

Question

How to insert XML to Mysql with php?

I want to insert data in tag count to xml_id field and data in tag image to image_url field to Mysql. I tried this code with xml file, but didnt' work. What am I doing wrong with code. Please tell me.

Here is XML.

<?xml version="1.0" encoding="utf-8"?>
<Listing>
 <Listing>
 <count>1</count>
 <Ad_Type>Sale</Ad_Type>
 <Unit_Type>Apartment</Unit_Type>
 <Images>
  <image>http://example.com/watermark?c_id=1110&l_id=1908007&aid=1461&image=e296915d71.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1908007&aid=1461&image=acf49f1088.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1908007&aid=1461&image=10a639c856.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1908007&aid=1461&image=9f9db4243e.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1908007&aid=1461&image=v127aca728.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1908007&aid=1461&image=g19bdac010.jpg</image>
 </Images>
 </Listing>

 <Listing>
 <count>2</count>
 <Ad_Type>Sale</Ad_Type>
 <Unit_Type>Apartment</Unit_Type>
 <Images>
  <image>http://example.com/watermark?c_id=1110&l_id=1907998&aid=1461&image=vv96915d71.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1907998&aid=1461&image=g19bdac010.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1907998&aid=1461&image=8127aca728.jpg</image>
  <image>http://example.com/watermark?c_id=1110&l_id=1907998&aid=1461&image=9f9db4243e.jpg</image>
 </Images>
 </Listing>
</Listing>

This is my code.

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

$xmlUrl = "import_dubai.xml"; // XML ไฟล์   
$xmlStr = file_get_contents($xmlUrl);  
$xml = preg_replace('#&(?=[a-z_0-9]+=)#', '&amp;', $xmlStr);

foreach($xml -> Listing as $Listing1){       // Listing => array

   foreach($Listing1 -> Listing as $Listing){    // Listing => array

    $Listing = $Listing -> Listing;
    $count = $Listing -> count;    

       foreach($Listing -> Images as $Images){   // Images => array

    $image = $Images -> image;
    echo '<br>'.$Listing1 -> Listing.' '.$Listing -> count.' '.$Listing -> Images.' '.$Images -> image. '<br>';
    $result=mysql_query("insert into xml_image ('xml_id','image_url') values ('$count','$image')");
  }
       
   }
}

if (!$result){
 echo 'Mysql Error';
} else{
    echo ' Success'; 
}

?>

Answer 1

I have intoduced a error handler for the provided xml file, so that you can get a more detailed info of what is actually happening or whats wrong with your xml content. Please check this out

<?php
include("connection.php")

$xmlUrl = "import_dubai.xml"; // XML ไฟล์
list ($xml, $error) = parseFile($xmlUrl);
if ($xml === FALSE) {
  throw new Exception("Failed to parse your XML file: $error");
}

foreach($xml -> Listing as $Listing1){       // Listing => array
foreach($Listing1 -> Listing as $Listing){    // Listing => array
   $Listing = $Listing -> Listing;
   $count = $Listing -> count;              
   foreach($Listing -> Images as $Images){   // Images => array
      $image = $Images -> image;
      echo '<br>'.$Listing1 -> Listing.' '.$Listing -> count.' '.$Listing -> Images.' '.$Images -> image. '<br>';
      $result=mysql_query("insert into xml_image ('xml_id','image_url') values ('$count','$image')");
    }

  }
}

if (!$result){
  echo 'Mysql Error';
} else{
  echo ' Success';  
}

 function parseFile($file) {
  $xml = FALSE; // SimpleXMLElement
  $error = FALSE; // string

  if (! file_exists($file)) {
    $error = 'File ' . $file . ' does not exist.';
  } else {
     $oldLibXMLErrors = libxml_use_internal_errors();
     libxml_use_internal_errors(TRUE);

     $xml = simplexml_load_file($file, 'SimpleXMLElement', LIBXML_NOCDATA);
     if ($xml === FALSE) {
       $errors = libxml_get_errors();
       foreach ($errors as $error) {
         if ($error->level != LIBXML_ERR_ERROR && $error->level != LIBXML_ERR_FATAL) {
           continue;
       }

       $parts = array();
       if ($error->file) {
       $parts[] = "File=$error->file";
     }
     $parts[] = "Line=$error->line";
     $parts[] = "Column=$error->column";
     $parts[] = "Code=$error->code";

     $messages[] = implode(" ", $parts) . ": " . trim($error->message);
   }
   $error = implode("\n", $messages);
  }

  libxml_use_internal_errors($oldLibXMLErrors);
  }

  return array($xml, $error);
}

Answer 2

This line:

$xml = preg_replace('#&(?=[a-z_0-9]+=)#', '&', $xmlStr);

returns a string, not objects that you can traverse by using

foreach ($xml->Listing as $Listing1) ...

if you want to go with working with XML then you an extra step :

$formatted = preg_replace('#&(?=[a-z_0-9]+=)#', '&', $xmlStr);
$xml = simplexml_load_string($formatted);

That will take you in the right direction.