Jump to content

[SOLVED] Query XML Data

nostrodamned

By nostrodamned
in PHP Coding Help

Recommended Posts

nostrodamned Newbie

nostrodamned

Posted
Posted

Hi all,

I have two xml files - one called Models and one called Makes(cars).

Now what i am trying to do is findo out which model belongs to which make.

 

this is the structure of the model xml.

<ModelLijnen>
    <ModelLijn ModelLijnID="524f9f9b-d7f7-4cfd-8a52-1f0c120de36e" MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" Generatie="" ModelCodeFabrikant="" ModelLijnCode="MIN" CategorieID="81a47c1b-dec8-4aae-b856-93abe83a777e" SegmentID="70184a1b-e31e-48d9-b001-cf16fc21a4a0" StartDatum="2006-10-01" EindDatum="" Status="1">
      <Model NL="Mini (R56)" EN="Mini (R56)" FR="Mini (R56)" />
    </ModelLijn>
    <ModelLijn ModelLijnID="b2692f3f-3ca7-4503-9923-43c5c5c46eea" MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" Generatie="" ModelCodeFabrikant="" ModelLijnCode="MIN" CategorieID="81a47c1b-dec8-4aae-b856-93abe83a777e" SegmentID="aefef3ec-8482-447f-b599-45d07265db37" StartDatum="2004-01-01" EindDatum="" Status="1">
      <Model NL="MINI Cabrio" EN="MINI Cabrio" FR="MINI Cabrio" />
    </ModelLijn>
    <ModelLijn ModelLijnID="ed676cb5-8ede-414c-b934-d4b763c753ea" MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" Generatie="" ModelCodeFabrikant="" ModelLijnCode="   " CategorieID="81a47c1b-dec8-4aae-b856-93abe83a777e" SegmentID="63e3485f-ccb2-4000-beaa-1ebad89504d6" StartDatum="2007-09-12" EindDatum="" Status="1">
      <Model NL="Mini Clubman (R55)" EN="Mini Clubman (R55)" FR="Mini Clubman (R55)" />
    </ModelLijn>
  </ModelLijnen>

 

MerkId is the MakeId number

 

This is the structure of the Make XML

 

<Merken>
    <Merk MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" ConstructeurID="00000000-0000-0000-0000-000000000000" MerkCategorieID="32dec0a5-9a51-468f-8a28-79b39fac0bc8" LandID="94e92415-8505-4b29-b84c-64ce451b9c05" Code="MNIP" Indeling="" Naam="MINI" NaamCommercieel="MINI" MerkCode="MIN" Webadres="" Status="1" />
    <Merk MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" ConstructeurID="00000000-0000-0000-0000-000000000000" MerkCategorieID="32dec0a5-9a51-468f-8a28-79b39fac0bc8" LandID="94e92415-8505-4b29-b84c-64ce451b9c05" Code="MNIP" Indeling="" Naam="GOLF" NaamCommercieel="MINI" MerkCode="MIN" Webadres="" Status="1" />
  </Merken>

 

Now this data is going into a mssql database.

In the database there is two tables Car_Makes and Car_Models

 

What i am trying to do is this

//For each model get the Makelid (MerkId) from Model.xml file

//Lookup the MakeId(MerkId) in the Make.XML file

//Get The Make Name from the Make.xml file and attach to a variable

//Query the Database for the Make and get the corresponding Make Id(I can code this!)

//Search the Model Table - if the model is there update else insert new model (Ican code this bit too)

 

I am using simplexml if that helps!

 

I hope that makes sense!!

 

Thanks for any assistance in this

 

 

 

 

Link to comment
https://forums.phpfreaks.com/topic/131773-solved-query-xml-data/
Share on other sites
nostrodamned Newbie

nostrodamned

Posted
  • Author
Posted

Hi, yep it is - i was jsut doing testing on this.

i have progressed a little - with this and almost got it working, the one thing i have to do now is somehow do a subquery within a for each statement.

foreach($sxml->ModelLijnen->ModelLijn as $Model){
           $Modeln = $sxml->ModelLijnen->ModelLijn->Model ; //This was my attempt at a sub query - i need to get this model name!
           $Mname =   $Modeln['NL']; 
            $MerkId = $Model['MerkID']; //Ok we need the Merk ID for later for updates to other tables
            $ModelLineId = $Model['ModelLijnID'];
            
            echo "Merk Id here is =".$MerkId."<br>";
            echo "Model Name here is =".$Mname."<br>"; //doesnt work - not related Model number
            echo "Model Line Id is =".$ModelLineId;

i have commented the code with what i am trying

Basically i need to get the Model name (NL) for the Model

 

rhodesa Newbie

rhodesa

Posted
Posted 
foreach($sxml->ModelLijnen->ModelLijn as $ModelLine){
  $Mname = $ModelLine->Model['NL'];
  $MerkId = $ModelLine['MerkID'];
  $ModelLineId = $ModelLine['ModelLijnID'];

  echo "Merk Id here is =".$MerkId."<br>";
  echo "Model Name here is =".$Mname."<br>";
  echo "Model Line Id is =".$ModelLineId."<br>";
  echo "<hr>";
}

nostrodamned Newbie

nostrodamned

Posted
  • Author
Posted

Hi Rhodesa - no its two seperate xml files i will post where i am at.

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

//Let's Start with the Make(Merk.xml)
if (file_exists('xmlfiles/Model.xml')) {
    $sxml = simplexml_load_file('xmlfiles/Model.xml');
   // print_r($xml);
} else {
    exit('Failed to open Model.xml.');
}

foreach($sxml->ModelLijnen->ModelLijn as $Model){
           $Modeln = $sxml->ModelLijnen->ModelLijn->Model ;
           $Mname =   $Modeln['NL']; 
            $MerkId = $Model['MerkID']; //Ok we need the Merk ID for later for updates to other tables
            $ModelLineId = $Model['ModelLijnID'];
            
            echo "Merk Id here is =".$MerkId."<br>";
            echo "Model Name here is =".$Mname."<br>"; 
            echo "Model Line Id is =".$ModelLineId;   
//Load the Merk.XML File          
if (file_exists('xmlfiles/Merk.xml')) {
   $smxml = simplexml_load_file('xmlfiles/Merk.xml');
} else {
    exit('Failed to open Model.xml.');
}

// Ok we are going to open the Make XML file and find out which model belongs to the make
foreach ($smxml->Merken->Merk as $merkidno) {
    switch((string) $merkidno['MerkID']) { // Get attributes as element indices
    case $MerkId:
        echo $merkidno, ' Make Found ='."<br>" ;
        echo $merkidno['Naam'];
        $MakeName=  $merkidno['Naam']; 
        //Now We Can Get The Make ID - should already in the Database
        $modelidsql=mssql_query("select id FROM AK_CAR_MAKE where Car_Name = '".$MakeName."'");
        $row = mssql_fetch_array($modelidsql)  ;
        echo "Make Id is = ".$row["id"]."<br>";
        //Ok We Got Make Id Number Now To get the last model id number in the database
        $lastmodelid = mssql_query("SELECT TOP 1 * FROM AK_CAR_MODEL ORDER BY id DESC");
                            $modelrow = mssql_fetch_array($lastmodelid)  ;

  echo "last row is" . $modelrow["id"]."<br>";

                               //ok we got the last new id number - lets import the data 
                            //  (int)$lastmodelid = (int)$modelrow["id"];
                         //  echo "last id is = ".$lastid;
                         //Create the new unique id number
                               $lastmodelid=$modelrow["id"]+1;
                               echo "New Id will be = ".$lastmodelid."<br>"; 
                               //Now we can check to see if the Model already exists in the db
                              echo"We Will be checking against this ". $ModelName."<br>"; //This is the issue here - need to get model name for each model in loop


                               
        break;
    case 'stars':
        echo $rating, ' stars';
        break;
    }
}


htmlentities((string) $sxml->Merken->Merk);
            
            //Get the Make Id
           // echo $MerkId."<br>";
            
} 
?>

 

This is a sample of the XML file model.xml

<?xml version="1.0" encoding="utf-8"?>
<ModelLijnenExport>
  <Header>
    <Sender>RDC VGS EP</Sender>
    <Receiver>NED_MINI</Receiver>
    <DocumentType>Full ModelLijnen</DocumentType>
    <CreationDate>20080918</CreationDate>
  </Header>
  <ModelLijnen>
    <ModelLijn ModelLijnID="524f9f9b-d7f7-4cfd-8a52-1f0c120de36e" MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" Generatie="" ModelCodeFabrikant="" ModelLijnCode="MIN" CategorieID="81a47c1b-dec8-4aae-b856-93abe83a777e" SegmentID="70184a1b-e31e-48d9-b001-cf16fc21a4a0" StartDatum="2006-10-01" EindDatum="" Status="1">
      <Model NL="Mini (R56)" EN="Mini (R56)" FR="Mini (R56)" />
    </ModelLijn>
    <ModelLijn ModelLijnID="b2692f3f-3ca7-4503-9923-43c5c5c46eea" MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" Generatie="" ModelCodeFabrikant="" ModelLijnCode="MIN" CategorieID="81a47c1b-dec8-4aae-b856-93abe83a777e" SegmentID="aefef3ec-8482-447f-b599-45d07265db37" StartDatum="2004-01-01" EindDatum="" Status="1">
      <Model NL="MINI Cabrio" EN="MINI Cabrio" FR="MINI Cabrio" />
    </ModelLijn>
    <ModelLijn ModelLijnID="ed676cb5-8ede-414c-b934-d4b763c753ea" MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" Generatie="" ModelCodeFabrikant="" ModelLijnCode="   " CategorieID="81a47c1b-dec8-4aae-b856-93abe83a777e" SegmentID="63e3485f-ccb2-4000-beaa-1ebad89504d6" StartDatum="2007-09-12" EindDatum="" Status="1">
      <Model NL="Mini Clubman (R55)" EN="Mini Clubman (R55)" FR="Mini Clubman (R55)" />
    </ModelLijn>
  </ModelLijnen>
</ModelLijnenExport>

 

And The Make XML File

<?xml version="1.0" encoding="utf-8"?>
<MerkenExport>
  <Header>
    <Sender>RDC VGS EP</Sender>
    <Receiver>NED_MINI</Receiver>
    <DocumentType>Full Merken</DocumentType>
    <CreationDate>20080918</CreationDate>
  </Header>
  <Merken>
    <Merk MerkID="508a8c11-a5c2-4218-bc25-64la377fd7314" ConstructeurID="00000000-0000-0000-0000-000000000000" MerkCategorieID="32dec0a5-9a51-468f-8a28-79b39fac0bc8" LandID="94e92415-8505-4b29-b84c-64ce451b9c05" Code="MNIP" Indeling="" Naam="GOLF" NaamCommercieel="GOLF" MerkCode="GOLF" Webadres="" Status="1" />
    <Merk MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" ConstructeurID="00000000-0000-0000-0000-000000000000" MerkCategorieID="32dec0a5-9a51-468f-8a28-79b39fac0bc8" LandID="94e92415-8505-4b29-b84c-64ce451b9c05" Code="MNIP" Indeling="" Naam="MINI" NaamCommercieel="MINI" MerkCode="MIN" Webadres="" Status="1" />
  </Merken>
</MerkenExport>

 

 

 

 

nostrodamned Newbie

nostrodamned

Posted
  • Author
Posted

I have simplified a little of what i am trying to do

 

This is the problem code.

<?php
   
     //Let's Start with the Make(Merk.xml)
if (file_exists('xmlfiles/Model.xml')) {
    $sxml = simplexml_load_file('xmlfiles/Model.xml');
   // print_r($xml);
} else {
    exit('Failed to open Model.xml.');
}

foreach($sxml->ModelLijnen->ModelLijn as $Model){
           $Modeln = $sxml->ModelLijnen->ModelLijn->Model ;
           $Mname =   $Modeln['NL']; 
           $MerkId = $Model['MerkID']; //Ok we need the Merk ID for later for updates to other tables
           $ModelLineId = $Model['ModelLijnID'];
            
            echo "Merk Id here is =".$MerkId."<br>";
            echo "Model Name here is =".$Mname."<br>"; 
            echo "Model Line Id is =".$ModelLineId;   
            
//Load the Merk.XML File          
if (file_exists('xmlfiles/Merk.xml')) {
   $smxml = simplexml_load_file('xmlfiles/Merk.xml');
} else {
    exit('Failed to open Model.xml.');
}

}
?>

Now the issue is the data to check against the $ModelLineId is in another node

<ModelLijn ModelLijnID="[b]524f9f9b-d7f7-4cfd-8a52-1f0c120de36e[/b]" MerkID="508a8c11-a5c2-4218-bc25-64a377fd7314" Generatie="" ModelCodeFabrikant="" ModelLijnCode="MIN" CategorieID="81a47c1b-dec8-4aae-b856-93abe83a777e" SegmentID="70184a1b-e31e-48d9-b001-cf16fc21a4a0" StartDatum="2006-10-01" EindDatum="" Status="1">
      <Model NL="[b]Mini (R56)[/b]" EN="Mini (R56)" FR="Mini (R56)" />
    </ModelLijn>

i have bolded the attributes i need to check against and what i need to return is in the NL attribute

Barand Prolific Member

Barand

Posted
Posted

is this what you are after?

 

$xml = simplexml_load_file('model.xml');

$makes = array();

foreach ($xml->ModelLijnen as $mod)
{
    foreach ($mod as $obj) {
        $makes[(string)$obj['ModelLijnID']] = $obj->Model['NL'];
    }
} 

/**
* get make   
*/
$id = "ed676cb5-8ede-414c-b934-d4b763c753ea";

echo $makes[$id];       // --->   Mini Clubman (R55)

Archived

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

Go to topic listing
×
×
  • 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.