www.webdeveloper.com
Results 1 to 8 of 8

Thread: [RESOLVED] XML, PHP and MySql

Hybrid View

  1. #1
    Join Date
    Feb 2012
    Posts
    30

    resolved [RESOLVED] XML, PHP and MySql

    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:
    PHP Code:
    $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:

    HTML Code:
    <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

  2. #2
    Join Date
    Jun 2008
    Posts
    35
    try

    PHP Code:
    $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:
    Code:
    <?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>

  3. #3
    Join Date
    Feb 2012
    Posts
    30
    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

  4. #4
    Join Date
    Jun 2008
    Posts
    35
    Assuming that your xml file now looks like:

    Code:
    <?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:
    PHP Code:
    $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.

  5. #5
    Join Date
    Feb 2012
    Posts
    30
    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):

    HTML Code:
    <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

  6. #6
    Join Date
    Jun 2008
    Posts
    35
    PHP Code:
    $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());
            }
        }


  7. #7
    Join Date
    Feb 2012
    Posts
    30
    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

  8. #8
    Join Date
    Feb 2012
    Posts
    30
    Ahhh got it to work, thank you very much for your help! Really couldn't of done this without you!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles