I would like to get Type, Id and Height values in database table. It is not working. There is no error message.

Objective is to get RESPECTIVE "Type" from Catalaog which has same Reference from Installed.

Well, I have 850 Mb XML file and i just would like to present small part of file:

XML:

<?xml version="1.0" encoding="utf-8"?>
<Physical>
    <Catalog>
        <EquipmentSpec>
            <Reference>MAT_4</Reference>
            <Type>Duplexeur</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>0</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_6890</Reference>
            <Type>Node</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>2</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_50</Reference>
            <Type>Single</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>5</CharacteristicValue></Characteristic>
        </EquipmentSpec>
    </Catalog>
    <Installed>
        <Equipment>
            <Id>26</Id><Reference>MAT_4</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>27</Id><Reference>MAT_6890</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>28</Id><Reference>MAT_50</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
        </Equipment>
    </Installed>
</Physical>

Code:

foreach ($xml->xpath('Physical') as $spec)
    {
        foreach ($spec->xpath('//EquipmentSpec') as $sub)
        {
            $Type = $sub->Type;
        }
        foreach ($spec->xpath('//Equipment') as $eq)
        {
            $Id = $eq->Id;
            foreach ($eq->Characteristic as $c)
            {
                if ($c->CharacteristicName == 'Height')
                {
                    $Height = $c->CharacteristicValue;
                }
            }    
        }

        $sql = "INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('".$Type."','".$Id."','".$Height."')";
        $req = new requete($site->db, $sql);
        echo $sql;

    }

EXPECTED output:

echo $Type;
  $sql = "INSERT INTO LTE_noria_antennes (`Type`,`Id`,`Height`) VALUES ('{$Type}', '{$Id}', '{$Height}')";
  $req = new requete($site->db, $sql); 

It should insert in database table like this:

 RefId**    Type          Id    Height
    1           Duplexeur    26     160     
    2              Node           27     140
     3             Single          28    180

    //  **RefId is column auto_incriment to count rows.

I hope i am clear to you.

THANKS A LOT IN ADVANCED FOR YOUR INPUT.

On line 20 you have:

$req = new requete($site->db, $sql)

What is this requete class?

Well, it is just to run query. It is/will not affect the code for sure.

Thanks.

So what's the problem, the query fails? Or the variables aren't filled correctly?

The variables aren't filled correctly.

Expected output is to get RESPECTIVE "Type" from Catalaog which has same Reference from Installed. It is not working.

It is not inserting any rows.

Can you correct the code, please?

print out the $spec first

print_r($spec) and see your variables

print_r($spec) and see your variables

@szabizs:

It is not giving any Values. How to solve this problem?

Thanks in adv.

Did you try using SimpleXML ?

I must use Xpath to get height value. I have used DOM. I think for complex XML file, DOM is the best option.

Here you go, a working example :)

Your test.xml is below

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <Physical>
        <Catalog>
        <EquipmentSpec>
            <Reference>MAT_4</Reference>
            <Type>Duplexeur</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>0</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_6890</Reference>
            <Type>Node</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>2</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_50</Reference>
            <Type>Single</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>5</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        </Catalog>
        <Installed>
            <Equipment>
                <Id>26</Id><Reference>MAT_4</Reference>
                <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
            </Equipment>
            <Equipment>
                <Id>27</Id><Reference>MAT_6890</Reference>
                <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
            </Equipment>
            <Equipment>
                <Id>28</Id><Reference>MAT_50</Reference>
                <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
            </Equipment>
        </Installed>
    </Physical>

Then your simple xml function continues

<?php

$xml = simplexml_load_file("test.xml"); 

/**
* You can use an array and iterate over it for your database if you want to.
* $array = array();
*/
foreach($xml as $key=>$val) {
    foreach($val as $key2=>$val2) {
        $Type = $val2->Type;

        foreach($val2 as $key3=>$val3) {
            if($val3->CharacteristicName == 'Height') {
                $Height = $val3->CharacteristicValue;
            }
        }
    }
}                            

?>

It sounds good.

Objective is to get RESPECTIVE "Type" from Catalaog which has SAME Reference from Installed.

Try this XML:

<?xml version="1.0" encoding="utf-8"?>
<Physical>
    <Catalog>
        <EquipmentSpec>
            <Reference>MAT_478788</Reference>
            <Type>Duplexeur</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>0</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_6890</Reference>
            <Type>Node</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>2</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_50</Reference>
            <Type>Single</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>5</CharacteristicValue></Characteristic>
        </EquipmentSpec>
    </Catalog>
    <Installed>
        <Equipment>
            <Id>26</Id><Reference>MAT_4</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>27</Id><Reference>MAT_6890</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>28</Id><Reference>MAT_50</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
        </Equipment>
    </Installed>
</Physical>

Can you tell me OUTPUT for this xml file ??

Awaiting your response,

Use my script from above and it will give you these details :)

Well, it is better than before. But It is not inserting in table and it should insert TWO rows as TWO Type are matching. :)

XML

    <?xml version="1.0" encoding="utf-8"?>
    <Physical>
       <Catalog>
          <EquipmentSpec>
             <Reference>MAT_48985</Reference>
             <Type>Duplexeur</Type>
             <Characteristic><CharacteristicName>Tilt</CharacteristicName>
             <CharacteristicValue>0</CharacteristicValue></Characteristic>
          </EquipmentSpec>
          <EquipmentSpec>
             <Reference>MAT_6890</Reference>
             <Type>Node</Type>
             <Characteristic><CharacteristicName>Tilt</CharacteristicName>
             <CharacteristicValue>2</CharacteristicValue></Characteristic>
          </EquipmentSpec>
          <EquipmentSpec>
             <Reference>MAT_50</Reference>
             <Type>Single</Type>
             <Characteristic><CharacteristicName>Tilt</CharacteristicName>
             <CharacteristicValue>5</CharacteristicValue></Characteristic>
          </EquipmentSpec>
       </Catalog>
       <Installed>
          <Equipment>
             <Id>26</Id><Reference>MAT_4</Reference>
             <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
          </Equipment>
          <Equipment>
             <Id>27</Id><Reference>MAT_6890</Reference>
             <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
          </Equipment>
          <Equipment>
             <Id>28</Id><Reference>MAT_50</Reference>
             <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
          </Equipment>
       </Installed>
    </Physical>

CODE:

    foreach ($xml->xpath('Catalog') as $spec)
    {
        foreach ($spec->xpath('//EquipmentSpec') as $sub)
        {
            $Type = $sub->Type;
          echo $Type;
        }
        foreach ($spec->xpath('//Equipment') as $eq)
        {
            $Id = $eq->Id;
          echo $Id;
            foreach ($eq->Characteristic as $c)
            {
                if ($c->CharacteristicName == 'Azimut')
                {
                    $Azimut = $c->CharacteristicValue;
                }
            }   
        }

        $sql = "INSERT INTO machines (`Type`,`Id`,`Azimut`) VALUES ('".$Type."','".$Id."','".$Height."')";
        $req = new requete($site->db, $sql);
        echo $sql;
       if (!$req)
       {
          $message  = 'Invalid query: ' . mysql_error() . "\n";
          $message .= 'Whole query: ' . $sql;
          die($message);
       }
    }

OUTPUT:

    DuplexeurNodeSingle262728INSERT INTO LTE_noria_antennes (`Type`,`Id`,`Azimut`) VALUES ('Single','28','180')

I know there is little mistake but i couldn't solve it out. Thanks for input.

Pseudo code for what you want is like this:

foreach ($spec->xpath('//Catalog/EquipmentSpec') as $sub)
{
    $Type = $sub->Type;
    $Reference -> $sub->Reference;

    // use $Reference in an XPath to find the matching Installed node
    // //Installed/Equipment/Reference[text() = '$Reference']
    // get the node's parent
    // get height and value

    // insert your data row here
}

Finally,

foreach ($xml->xpath("//Catalog/EquipmentSpec") as $n => $equipmentspec) {
    $ref = (string)$equipmentspec->Reference;
    $type = (string)$equipmentspec->Type;
    $installed = $xml->xpath("//Installed/Equipment[Reference='$ref']");
    if ($installed) {
        foreach ($installed as $inst) {
            $id = (string)$inst->Id;
            $AzimutObj = $inst->xpath("Characteristic[CharacteristicName='Azimut']");
            $Azimut = (string)$AzimutObj[0]->CharacteristicValue;
            echo "$ref | $type | $id | $Azimut<br>";
            // insertion to db here
        }
    }
}

OUTPUT:

MAT_4    | Duplexeur | 26 | 160
MAT_6890 | Node      | 27 | 140
MAT_50   | Single    | 28 | 180

IT must be two:

MAT_6890 | Node      | 27 | 140
MAT_50   | Single    | 28 | 180

Because MAT_4!=MAT_48985

When i tried this, " Equipment[Reference=='$ref' " , it is not possible.

In addition, i have 850 Mb File. I made 4 parts of 200 Mb. When i tried in Part1, it went into infinite loop.

It seems Catalog is not present in Part3 and Part4. How to solve this ?

Hard to say without knowing how the XML looks for those parts. You cannot just split an XML file, it will lose it's structure.

Depending on whether you are running this on a dedicated host, have you considered the fact that PHP might not be the logical choice to parse an 850mb file?

I had taken care of structure for XML file. There is no problem.

Two Problems:
1. How to match exactly Ref value
2.How to READ Part3, Part4

I'm not psychic, I don't know what parts 3 and 4 look like.

I'm not psychic, I don't know what parts 3 and 4 look like.

Part3, Part4 contains :

<Physical>
<Installed>
<Equipment>
<Id>26</Id><Reference>MAT_4</Reference>
<Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
</Equipment>
<Equipment>
<Id>27</Id><Reference>MAT_6890</Reference>
<Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
</Equipment>
<Equipment>
<Id>28</Id><Reference>MAT_670</Reference>
<Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
</Equipment>
/* ...... */
/* ...... There are only Equipments.. */
</Installed>
</Physical>

There is no Catalog tag present in the file.

The above code doesn't work on this, because the Catalog part is missing.

I created "machines_type" table to store all Type Values. The main table is "machines".

I need to solve Update Query with LEFT JOIN

SQL:

     UPDATE machines SET machines.Type = machines_type.Type
       WHERE Reference IN (SELECT machines.Reference, machines_type.Reference
       FROM machines
       LEFT JOIN machines_type
       ON machines.Reference = machines_type.Reference
       WHERE machines.Id IS NOT NULL )


Error:
#1241 - Operand should contain 1 column(s)

:( :P

Remove one reference in the sub-select.

#1093 - You can't specify target table 'machines' for update in FROM clause

This is completely unrelated to the rest of the thread. Explain clearly what you have and what you are trying to achieve.

This is completely unrelated to the rest of the thread. Explain clearly what you have and what you are trying to achieve.

I clearly said that i have error in SQL query and you also know well that thing. As you told:

Remove one reference in the sub-select.

AFTER removing reference in the sub-select, i got another error:

#1093 - You can't specify target table 'machines' for update in FROM clause

SO, it is very clear that i need to solve this QUERY.

Thank you.

SO, it is very clear that i need to solve this QUERY.

Yes, to you. Without more information on what you want to update, what data you have and how the tables look, I can only guess at what's happening.

The error message means that you cannot use the same table in the sub-select and the update at once.

Finally, it's SOLVED..!!

UPDATE machines LEFT JOIN machines_type ON
machines.Reference = machines_type.Reference
SET machines.Type = machines_type.Type
WHERE
machines_type.Reference IS NOT NULL

Thank you all for your help.

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.