Jump to content

Insert XML values into Mysql


Nandini
 Share

Recommended Posts

Hi

 

I have a XML file as follows:

 

<?xml version="1.0"?>

<inspection_form>

  <inspection_type>

    <inspection_area_tlb>yard_and_lot</inspection_area_tlb>

    <inspection_area>Yard and Lot</inspection_area>

    <items>

      <item>

        <item_name>PID Signage/unauthorized sign on pole</item_name>

        <item_value>0</item_value>

      </item>

      <item>

        <item_name>Landscape well maintained</item_name>

        <item_value>0</item_value>

      </item>

</items>

  </inspection_type>

  <inspection_type>

    <inspection_area_tlb>pump_island</inspection_area_tlb>

    <inspection_area>Pump Island and Canopies</inspection_area>

    <items>

      <item>

        <item_name>pumps clean and free of dirt</item_name>

        <item_value>0</item_value>

      </item>

      <item>

        <item_name>Approved trash cans/clean</item_name>

        <item_value>0</item_value>

      </item>

</items>

  </inspection_type>

</inspection_form>

 

I want to insert into DB as follows:

 

inspection_area_tlb

inspection_area

item_name

item_value

yard_and_lot

yard and Lot

PID Signage/unauthorized sign on pole

0

yard_and_lot

yard and Lot

Landscape well maintained

0

pump_island

Pump Island and Canopies

pumps clean and free of dirt

0

pump_island

Pump Island and Canopies

Approved trash cans/clean

0

 

I have written some php code. But every item node as insert for every 'inspection_type'. This is my code

 

$filename="sample.xml";
if(filesize($filename)>0)
{
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($filename)); 
foreach ($oDOM->getElementsByTagName('inspection_type') as $oBookNode)
{
foreach ($oDOM->getElementsByTagName('item') as $itmNode)
{
    $sSQL = sprintf(
        "INSERT INTO inspections_master_tablename_import (INSPECTION_TYPE_DB_C_NAME, INSPECTION_TYPE_C_NAME, INSPECTION_TYPE_ITEM_C_NAME,INSPECTION_TYPE_ITEM_VALUE_C_NAME) VALUES ('%s', '%s', '%s', '%s')",
        mysql_real_escape_string($oBookNode->getElementsByTagName('inspection_area_tlb')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('inspection_area')->item(0)->nodeValue),
        mysql_real_escape_string($itmNode->getElementsByTagName('item_name')->item(0)->nodeValue),
	mysql_real_escape_string($itmNode->getElementsByTagName('item_value')->item(0)->nodeValue)
    );
    $rResult = mysql_query($sSQL);
    
    if(mysql_errno() > 0)
    {
        printf(
            '<h4 style="color: red;">Query Error:</h4>
            <p>(%s) - %s</p>
            <p>Query: %s</p>
            <hr />',
            mysql_errno(),
            mysql_error(),
            $sSQL
        );
    }
}
}
}

 

Can anyone help me pls.

Link to comment
Share on other sites

By using this code output displaying as follows

 

inspection_area_tlb

inspection_area

item_name

item_value

yard_and_lot

yard and Lot

PID Signage/unauthorized sign on pole

0

yard_and_lot

yard and Lot

Landscape well maintained

0

yard_and_lot

yard and Lot

pumps clean and free of dirt

0

yard_and_lot

yard and Lot

Approved trash cans/clean

0

pump_island

Pump Island and Canopies

PID Signage/unauthorized sign on pole

0

pump_island

Pump Island and Canopies

Landscape well maintained

0

pump_island

Pump Island and Canopies

pumps clean and free of dirt

0

pump_island

Pump Island and Canopies

Approved trash cans/clean

0

 

But this is wrong. I want out put as like in our question

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • 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.