[RESOLVED] XML, PHP and MySql

[thewebportfolio]

Hello,

I have recently posted a few questions about exporting XML into PHP into MySql.

I have created my PHP file which exports my XML into MySql which works great, if, i have only 1 XML in the XML file. The files will come daily with over 200 XML.

I have attached a snipit of my code below:

$xmlDoc = new DOMDocument();
$xmlDoc->load( 'cdocument-2012-03-05.xml' );

$thisnodeone = $xmlDoc->getElementsByTagName('ConferenceData');
foreach ($thisnodeone as $nodes )
{

/////      My-ConferenceData     //////
echo "<strong>ConferenceData</strong><br /><br />";
$itemnodes = $xmlDoc->getElementsByTagName( "ConferenceData" );
$nodes = $itemnodes->item(0)->getElementsByTagName( "Event" );
for ( $i = 0; $i < $nodes->length; $i++ )
{
    $valueConferenceDataEvent = $nodes->item( $i++ )->getAttribute('value');
    echo "Event - $valueConferenceDataEvent<br /><br />";
}

/////      My-Basic-Information     //////
echo "<br /><br /><br /><strong>Basic-Information</strong><br /><br />";
$itemnodes = $xmlDoc->getElementsByTagName( "Basic-Information" );
$nodes = $itemnodes->item(0)->getElementsByTagName( "Conference-ID" );
for ( $i = 0; $i < $nodes->length; $i++ )
{
    $valueBasicInformationConferenceID = $nodes->item( $i++ )->getAttribute('value');
    echo "Conference-ID - $valueBasicInformationConferenceID<br /><br />";
}

$sql = "insert into `XXXXXX` (`ConferenceID`) values ('$valueBasicInformationConferenceID')";
$perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error())
} 

please note that near to the top of this i have tried to separate the XML's to parse through and insert as new table rows in MySql with the ID being 'ConferenceID'.

My XML Would have over 200 XML and a snipit again i have put below:

<conferences>
  <ConferenceData>
    <Event value="myevent" />
    <Scheduling-Data>
      <Conference>
        <Basic-Information>
          <Conference-ID value="myconferenceid" />
        </Basic-Information>
      </Conference>
    </Scheduling-Data>
  </ConferenceData>
</conferences> 

If anyone could help me with this i would be extremely grateful.

Many Thanks

[badben]

try

$xmlDoc = new DOMDocument();
$xmlDoc->load( 'cdocument-2012-03-05.xml' );

$thisnodeone = $xmlDoc->getElementsByTagName('ConferenceData');
foreach ($thisnodeone as $node ) {

    /////      My-ConferenceData     //////
    echo "<strong>ConferenceData</strong><br /><br />";
    $event = $node->getElementsByTagName( "Event" );
    $valueConferenceDataEvent = $event->item(0)->getAttribute('value');
    echo "Event - $valueConferenceDataEvent<br /><br />";
    
    /////      My-Basic-Information     //////
    echo "<br /><br /><br /><strong>Basic-Information</strong><br /><br />";
    $confId = $node->getElementsByTagName( "Conference-ID" );

    $valueBasicInformationConferenceID = $confId->item(0)->getAttribute('value');
        echo "Conference-ID - $valueBasicInformationConferenceID<br /><br />";
    
    $sql = "insert into `XXXXXX` (`ConferenceID`) values ('$valueBasicInformationConferenceID')";
    $perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
} 

this should work for xml like:

<?xml version="1.0"?>
<conferences>
  <ConferenceData>
    <Event value="myevent" />
    <Scheduling-Data>
      <Conference>
        <Basic-Information>
          <Conference-ID value="myconferenceid" />
        </Basic-Information>
      </Conference>
    </Scheduling-Data>
  </ConferenceData>
  <ConferenceData>
    <Event value="myevent" />
    <Scheduling-Data>
      <Conference>
        <Basic-Information>
          <Conference-ID value="myconferenceid2" />
        </Basic-Information>
      </Conference>
    </Scheduling-Data>
  </ConferenceData>
</conferences>

[thewebportfolio]

Thank you, that works perfect, just one more question if you could please.

In the XML file, their is a tag called 'Host', 'Organizer' and 'Predefined-Attendee'
These 3 tags have the same tags inside like 'User-Id' and 'Login-Id'.

Obviously as i use that previous code it will get the last tag in the XML list. how can i get this to look for the Host->User-Id

i have tried a few things although cannot get them to work

<Host>
<User-Id value="103" />
<Login-Id value="Marshalls" />
<First-Name value="N/A" />
<Last-Name value="Marshalls" />
<Email value="N/A" />
<Customer-Id value="999" />
<Company-Name value="N/A" />
<Customer-Profile-Type value="N/A" />
<Customer-Billing-Phone value="N/A" />
<Is-Controller value="false" />
</Host>
<Organizer>
<User-Id value="103" />
<Login-Id value="Marshalls" />
<First-Name value="N/A" />
<Last-Name value="Marshalls" />
<Email value="N/A" />
<Customer-Id value="999" />
<Company-Name value="N/A" />
<Customer-Profile-Type value="N/A" />
<Customer-Billing-Phone value="N/A" />
<Is-Controller value="false" />
</Organizer>
<Predefined-Attendees>
<Predefined-Attendee>
<User-ID value="103" />
<Login-Id value="Marshalls" />
<First-Name value="N/A" />
<Last-Name value="Marshalls" />
<Email value="N/A" />
<Customer-Id value="999" />
<Company-Name value="N/A" />
<Customer-Billing-Phone value="N/A" />
<Is-Controller value="false" />
</Predefined-Attendee>
</Predefined-Attendees>


Many Thanks

[badben]

Assuming that your xml file now looks like:

<?xml version="1.0"?>
<conferences>
  <ConferenceData>
    <Event value="myevent" />
    <Scheduling-Data>
      <Conference>
        <Basic-Information>
          <Conference-ID value="myconferenceid" />
        </Basic-Information>
      </Conference>
    </Scheduling-Data>
  </ConferenceData>
  <ConferenceData>
    <Event value="myevent" />
    <Scheduling-Data>
      <Conference>
        <Basic-Information>
          <Conference-ID value="myconferenceid2" />
        </Basic-Information>
      </Conference>
    </Scheduling-Data>
    <Host>
        <User-Id value="103" />
        <Login-Id value="Marshalls" />
        <First-Name value="N/A" />
        <Last-Name value="Marshalls" />
        <Email value="N/A" />
        <Customer-Id value="999" />
        <Company-Name value="N/A" />
        <Customer-Profile-Type value="N/A" />
        <Customer-Billing-Phone value="N/A" />
        <Is-Controller value="false" />
    </Host>
    <Organizer>
        <User-Id value="103" />
        <Login-Id value="Marshalls" />
        <First-Name value="N/A" />
        <Last-Name value="Marshalls" />
        <Email value="N/A" />
        <Customer-Id value="999" />
        <Company-Name value="N/A" />
        <Customer-Profile-Type value="N/A" />
        <Customer-Billing-Phone value="N/A" />
        <Is-Controller value="false" />
    </Organizer>
    <Predefined-Attendees>
        <Predefined-Attendee>
            <User-ID value="103" />
            <Login-Id value="Marshalls" />
            <First-Name value="N/A" />
            <Last-Name value="Marshalls" />
            <Email value="N/A" />
            <Customer-Id value="999" />
            <Company-Name value="N/A" />
            <Customer-Billing-Phone value="N/A" />
            <Is-Controller value="false" />
        </Predefined-Attendee>
    </Predefined-Attendees>
  </ConferenceData>
</conferences>

Your code should be as follows:

$xmlDoc = new DOMDocument();
$xmlDoc->load( 'cdocument-2012-03-05.xml' );

$thisnodeone = $xmlDoc->getElementsByTagName('ConferenceData');
foreach ($thisnodeone as $node ) {

    /////      My-ConferenceData     //////
    echo "<strong>ConferenceData</strong><br /><br />";
    $event = $node->getElementsByTagName( "Event" );
    $valueConferenceDataEvent = $event->item(0)->getAttribute('value');
    echo "Event - $valueConferenceDataEvent<br /><br />";
    
    /////      My-Basic-Information     //////
    echo "<br /><br /><br /><strong>Basic-Information</strong><br /><br />";
    $confId = $node->getElementsByTagName( "Conference-ID" );

    $valueBasicInformationConferenceID = $confId->item(0)->getAttribute('value');
        echo "Conference-ID - $valueBasicInformationConferenceID<br /><br />";
    
    $sql = "insert into `test` (`ConferenceID`) values ('$valueBasicInformationConferenceID')";
    $perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
    
    $hosts = $node->getElementsByTagName( "Host" );
    if ($hosts) {
        $i = 0;
        foreach ($hosts as $host) {
            $hostUser = $host->getElementsByTagName( "User-Id" );
            $hostUserId = $hostUser->item(0)->getAttribute('value');
            echo "Host-User-ID - $hostUserId<br /><br />";
            
            $hostLogin = $host->getElementsByTagName( "Login-Id" );
            $hostLoginId = $hostLogin->item(0)->getAttribute('value');
            echo "Host-Login-ID - $hostLoginId<br /><br />";           
            
            $i++;
        }
    }
    
    $organizers = $node->getElementsByTagName( "Organizer" );
    if ($organizers) {
        $i = 0;
        foreach ($organizers as $organizer) {
            $organizerUser = $organizer->getElementsByTagName( "User-Id" );
            $organizerUserId = $organizerUser->item(0)->getAttribute('value');
            echo "Organizer-User-ID - $organizerUserId<br /><br />";
            
            $organizerLogin = $organizer->getElementsByTagName( "Login-Id" );
            $organizerLoginId = $organizerLogin->item(0)->getAttribute('value');
            echo "Organizer-Login-ID - $organizerLoginId<br /><br />";           
            
            $i++;
        }
    }
    
    $attendees = $node->getElementsByTagName( "Predefined-Attendee" );
    if ($attendees) {
        $i = 0;
        foreach ($attendees as $attendee) {
            $attendeeUser = $attendee->getElementsByTagName( "User-ID" );
            $attendeeUserId = $attendeeUser->item(0)->getAttribute('value');
            echo "Attendee-User-ID - $organizerUserId<br /><br />";
            
            $attendeeLogin = $attendee->getElementsByTagName( "Login-Id" );
            $attendeeLoginId = $attendeeLogin->item(0)->getAttribute('value');
            echo "Attendee-Login-ID - $organizerLoginId<br /><br />";           
            
            $i++;
        }
    }
} 

I have not included the sql queries but you should get the idea.

Hope this helps.

[thewebportfolio]

Your a genius! I know i said last question before but i have found one more thing if its ok...

On the <Predefined-Attendee> theirs a parent tag of <Predefined-Attendees>

this means in my XML file their can be a few Predefined-Attendee within the Attendees (as shown below):

<Predefined-Attendees>
          <Predefined-Attendee>
            <User-ID value="103" />
            <Login-Id value="Marshalls" />
            <First-Name value="N/A" />
            <Last-Name value="Marshalls" />
            <Email value="N/A" />
            <Customer-Id value="999" />
            <Company-Name value="N/A" />
            <Customer-Billing-Phone value="N/A" />
            <Is-Controller value="false" />
          </Predefined-Attendee>
          <Predefined-Attendee>
            <User-ID value="101" />
            <Login-Id value="Wilkinson" />
            <First-Name value="N/A" />
            <Last-Name value="Wilkinson" />
            <Email value="N/A" />
            <Customer-Id value="911" />
            <Company-Name value="N/A" />
            <Customer-Billing-Phone value="N/A" />
            <Is-Controller value="true" />
          </Predefined-Attendee>
          <Predefined-Attendee>
            <User-ID value="101" />
            <Login-Id value="Robinson" />
            <First-Name value="Liam" />
            <Last-Name value="Robinson" />
            <Email value="N/A" />
            <Customer-Id value="123" />
            <Company-Name value="N/A" />
            <Customer-Billing-Phone value="N/A" />
            <Is-Controller value="false" />
          </Predefined-Attendee>
</Predefined-Attendees>

How could i get these 3 to also be imported into my database.

Really appreciate the help! Many Thanks

[badben]

$xmlDoc = new DOMDocument();
$xmlDoc->load( 'cdocument-2012-03-05.xml' );

$thisnodeone = $xmlDoc->getElementsByTagName('ConferenceData');
foreach ($thisnodeone as $node ) {

    /////      My-ConferenceData     //////
    echo "<strong>ConferenceData</strong><br /><br />";
    $event = $node->getElementsByTagName( "Event" );
    $valueConferenceDataEvent = $event->item(0)->getAttribute('value');
    echo "Event - $valueConferenceDataEvent<br /><br />";
    
    /////      My-Basic-Information     //////
    echo "<br /><br /><br /><strong>Basic-Information</strong><br /><br />";
    $confId = $node->getElementsByTagName( "Conference-ID" );

    $valueBasicInformationConferenceID = $confId->item(0)->getAttribute('value');
        echo "Conference-ID - $valueBasicInformationConferenceID<br /><br />";
    
    $sql = "insert into `test` (`ConferenceID`) values ('$valueBasicInformationConferenceID')";
    $perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
    
    $hosts = $node->getElementsByTagName( "Host" );
    if ($hosts) {
        $i = 0;
        foreach ($hosts as $host) {
            $hostUser = $host->getElementsByTagName( "User-Id" );
            $hostUserId = $hostUser->item(0)->getAttribute('value');
            echo "Host-User-ID - $hostUserId<br /><br />";
            
            $hostLogin = $host->getElementsByTagName( "Login-Id" );
            $hostLoginId = $hostLogin->item(0)->getAttribute('value');
            echo "Host-Login-ID - $hostLoginId<br /><br />";           
            
            $i++;
        }
    }
    
    $organizers = $node->getElementsByTagName( "Organizer" );
    if ($organizers) {
        $i = 0;
        foreach ($organizers as $organizer) {
            $organizerUser = $organizer->getElementsByTagName( "User-Id" );
            $organizerUserId = $organizerUser->item(0)->getAttribute('value');
            echo "Organizer-User-ID - $organizerUserId<br /><br />";
            
            $organizerLogin = $organizer->getElementsByTagName( "Login-Id" );
            $organizerLoginId = $organizerLogin->item(0)->getAttribute('value');
            echo "Organizer-Login-ID - $organizerLoginId<br /><br />";           
            
            $i++;
        }
    }
    
    $attendees = $node->getElementsByTagName( "Predefined-Attendee" );
    if ($attendees) {
        $i = 0;
        foreach ($attendees as $attendee) {
            $attendeeUser = $attendee->getElementsByTagName( "User-ID" );
            $attendeeUserId = $attendeeUser->item(0)->getAttribute('value');
            echo "Attendee-User-ID - $attendeeUserId<br /><br />";
            
            $attendeeLogin = $attendee->getElementsByTagName( "Login-Id" );
            $attendeeLoginId = $attendeeLogin->item(0)->getAttribute('value');
            echo "Attendee-Login-ID - $attendeeLoginId<br /><br />";           
            
            $i++;
            
            $sql = "insert into `XXXXXX` (`UserID`,`LoginID`) values ('$attendeeUserId', '$attendeeLoginId')";
            $perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
        }
    }
} 

[thewebportfolio]

On that, it gets the Predefined-Attendees correctly and is shown as:

Predefined-Attendee

User-ID - 103

Login-ID - Marshalls

First-Name - N/A

Last-Name - Marshalls

Email - N/A

Customer-Id - 999

Company-Name - N/A

Customer-Billing-Phone - N/A

Is-Controller - false

User-ID - 101

Login-ID - Wilkinson

First-Name - N/A

Last-Name - Wilkinson

Email - N/A

Customer-Id - 911

Company-Name - N/A

Customer-Billing-Phone - N/A

Is-Controller - true






which is great, although it only imports the first one into MySql:







Predefined-Attendee

User-ID - 103

Login-ID - Marshalls

First-Name - N/A

Last-Name - Marshalls

Email - N/A

Customer-Id - 999

Company-Name - N/A

Customer-Billing-Phone - N/A

Is-Controller - false




Many Thanks

[thewebportfolio]

Ahhh got it to work, thank you very much for your help! Really couldn't of done this without you!