[RESOLVED] XML, PHP and MySql

This site is best viewed in a modern browser with JavaScript enabled.

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

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

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

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!