SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    XML into MySQL using PHP

    Not sure if this is the right place but how do you actually import xml data into a MySQL table? I am just getting started with XML and have tried to do this using DOM.

    [code]
    <?php

    $conn = @mysql_connect("","", "") or die("Sorry, could not connect");

    #load an xml document into the DOM
    $dom = new DOMDocument;
    $dom -> load( "books.xml" );
    $title = $dom -> documentElement -> nodeName;

    #print out the root element name
    echo( "Root element name is: ");
    echo( $dom -> documentElement -> nodeName );
    echo( "<hr>" );

    #print a list of all topics
    echo( "Topics include: <ul>" );
    $topics = $dom -> getElementsByTagName( "topic" );
    $series = $dom -> getElementsByTagName( "series" );
    foreach( $topics as $node)
    {
    echo( "<li>" . $node -> textContent . "</li>" );

    #insert into the database
    $sql = "INSERT INTO `books` (`id`, `book_topic`) VALUES (NULL, '$node')";
    $perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

    }
    echo( "</ul>" );

    ?>

    [End of code]

    An error states:

    Catchable fatal error: Object of class DOMElement could not be converted to string

    How do you convert the object to a string?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe you want $node->textContent?

  3. #3
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    Maybe you want $node->textContent?
    Tried that in my INSERT VALUE but didn't work. Still says i needs to be a string

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    post what you tried.

  5. #5
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried it as this:

    [code]
    <?php

    $sql = "INSERT INTO `books` (`id`, `book_topic`) VALUES (NULL, '$node -> textcontent')";
    $perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

    ?>
    [End of code]

    Not sure if this is what you meant.

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'm not confident this is the right way of getting the nodes, but this works. I'm much more comfortable with SimpleXML...sorry.

    PHP Code:
    <?php
    $oDOM 
    = new DOMDocument();
    $oDOM->loadXML(file_get_contents('books.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx
    foreach ($oDOM->getElementsByTagName('book') as $oBookNode)
    {
        
    printf(
            
    "INSERT INTO table (title, author, description) VALUES ('%s', '%s', '%s')",
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('title')->item(0)->nodeValue),
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('author')->item(0)->nodeValue),
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('description')->item(0)->nodeValue)
        );
    }
    /*
    INSERT INTO table (title, author, description) VALUES ('XML Developer's Guide', 'Gambardella, Matthew', 'An in-depth look at creating applications with XML.')
    INSERT INTO table (title, author, description) VALUES ('Midnight Rain', 'Ralls, Kim', 'A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.')
    INSERT INTO table (title, author, description) VALUES ('Maeve Ascendant', 'Corets, Eva', 'After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.')
    INSERT INTO table (title, author, description) VALUES ('Oberon's Legacy', 'Corets, Eva', 'In post-apocalypse England, the mysterious agent known only as Oberon helps to create a new life for the inhabitants of London. Sequel to Maeve Ascendant.')
    INSERT INTO table (title, author, description) VALUES ('The Sundered Grail', 'Corets, Eva', 'The two daughters of Maeve, half-sisters, battle one another for control of England. Sequel to Oberon's Legacy.')
    INSERT INTO table (title, author, description) VALUES ('Lover Birds', 'Randall, Cynthia', 'When Carla meets Paul at an ornithology conference, tempers fly as feathers get ruffled.')
    INSERT INTO table (title, author, description) VALUES ('Splish Splash', 'Thurman, Paula', 'A deep sea diver finds true love twenty thousand leagues beneath the sea.')
    INSERT INTO table (title, author, description) VALUES ('Creepy Crawlies', 'Knorr, Stefan', 'An anthology of horror stories about roaches, centipedes, scorpions and other insects.')
    INSERT INTO table (title, author, description) VALUES ('Paradox Lost', 'Kress, Peter', 'After an inadvertant trip through a Heisenberg Uncertainty Device, James Salway discovers the problems of being quantum.')
    INSERT INTO table (title, author, description) VALUES ('Microsoft .NET: The Programming Bible', 'O'Brien, Tim', 'Microsoft's .NET initiative is explored in detail in this deep programmer's reference.')
    INSERT INTO table (title, author, description) VALUES ('MSXML3: A Comprehensive Guide', 'O'Brien, Tim', 'The Microsoft MSXML3 parser is covered in detail, with attention to XML DOM interfaces, XSLT processing, SAX and more.')
    INSERT INTO table (title, author, description) VALUES ('Visual Studio 7: A Comprehensive Guide', 'Galos, Mike', 'Microsoft Visual Studio 7 is explored in depth, looking at how Visual Basic, Visual C++, C#, and ASP+ are integrated into a comprehensive development environment.')
    */
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    I'm not confident this is the right way of getting the nodes, but this works. I'm much more comfortable with SimpleXML...sorry.
    Thanks mate, what is the benefit, if any of doing this with SimpleXML? Noob with XML. So any benefits would help before I get too engrossed in one method of doing something.

  8. #8
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by freakystreak View Post
    Thanks mate, what is the benefit, if any of doing this with SimpleXML? Noob with XML. So any benefits would help before I get too engrossed in one method of doing something.
    Essentially, you get ArrayAccess, which means you can access XML as you would an array.

    Although not a great example of this, here's a comparative one.

    PHP Code:
    <?php
    $oXML 
    = new SimpleXMLElement(file_get_contents('books.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx
    foreach ($oXML->catalog->book as $oBook)
    {
        
    printf(
            
    "INSERT INTO table (title, author, description) VALUES ('%s', '%s', '%s')",
            
    mysql_real_escape_string($oBook->title),
            
    mysql_real_escape_string($oBook->author),
            
    mysql_real_escape_string($oBook->description)
        );
    }
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  9. #9
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hth
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That first code works great, I have assigned the results to variables and they are now going straight into the database. However, it only enters and prints the first set of XML data.

    Here's the XML
    [XML]
    <?xml version="1.0" encoding="iso-8859-1"?>
    <books>

    <title>
    <topic>Javascript</topic>
    <series>in easy steps</series>
    <pic>js.gif</pic>
    </title>

    <title>
    <topic>C++ Programming</topic>
    <series>in easy steps</series>
    <pic>c++.gif</pic>
    </title>

    <title>
    <topic>HTML</topic>
    <series>in easy steps</series>
    <pic>html.gif</pic>
    </title>

    <title>
    <topic>SQL</topic>
    <series>in easy steps</series>
    <pic>sql.gif</pic>
    </title>
    </books>
    [/XML]

    and here is the code I'm using;

    [php]
    <?php

    $oDOM = new DOMDocument();
    $oDOM->loadXML(file_get_contents('books.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx
    foreach ($oDOM->getElementsByTagName('books') as $oBookNode)
    {
    $topic=mysql_real_escape_string($oBookNode->getElementsByTagName('topic')->item(0)->nodeValue);
    $series=mysql_real_escape_string($oBookNode->getElementsByTagName('series')->item(0)->nodeValue);
    $pic=mysql_real_escape_string($oBookNode->getElementsByTagName('pic')->item(0)->nodeValue);
    }


    #insert into the database
    $sql = "INSERT INTO `books` (`id`, `book_topic`) VALUES (NULL, '$topic', '$series')";
    $perform_insert = mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

    ?>
    [/code]

    My database looks like this

    id|book_topic|book_series
    1|javascript|in easy steps

    How can I get it to return and enter all results?

  12. #12
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You're looking for book nodes, when your XML has title nodes.
    PHP Code:
    $oDOM = new DOMDocument();
    $oDOM->loadXML(file_get_contents('books.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx
    foreach ($oDOM->getElementsByTagName('title') as $oBookNode)
    {
        
    printf(
            
    "INSERT INTO table (topic, series, pic) VALUES ('%s', '%s', '%s')",
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('topic')->item(0)->nodeValue),
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('series')->item(0)->nodeValue),
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('pic')->item(0)->nodeValue)
        );
    }
    ?> 
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  13. #13
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, cheers for that but still not sure about how to use the array that your code creates to INSERT into my database.

    PHP Code:
    <?php

    $oDOM 
    = new DOMDocument();
    $oDOM->loadXML(file_get_contents('books.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx
    foreach ($oDOM->getElementsByTagName('title') as $oBookNode)
    {

    $topic=mysql_real_escape_string($oBookNode->getElementsByTagName('topic')->item(0)->nodeValue);
    $series=mysql_real_escape_string($oBookNode->getElementsByTagName('series')->item(0)->nodeValue);
    $pic=mysql_real_escape_string($oBookNode->getElementsByTagName('pic')->item(0)->nodeValue);
    }

    $sql "INSERT INTO `books` (`id`, `book_title`, `book_topic`, `book_pic`) VALUES (NULL, '$topic', '$series', '$pic')";
    $perform_insert mysql_query($sql) or die("<b>Data could not be entered</b>.\n<br />Query: " $query "<br />\nError: (" mysql_errno() . ") " mysql_error())
    ?>
    this just inserts the final result from the xml i need to enter each record. Apologies.

  14. #14
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $oDOM 
    = new DOMDocument();
    $oDOM->loadXML(file_get_contents('books.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx
    foreach ($oDOM->getElementsByTagName('title') as $oBookNode)
    {
        
    $sSQL sprintf(
            
    "INSERT INTO table (topic, series, pic) VALUES ('%s', '%s', '%s')",
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('topic')->item(0)->nodeValue),
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('series')->item(0)->nodeValue),
            
    mysql_real_escape_string($oBookNode->getElementsByTagName('pic')->item(0)->nodeValue)
        );
        
    $rResult mysql_query($sSQL);
        
        if(
    mysql_errno() > 0)
        {
            
    printf(
                
    '<h4 style="color: red;">Query Error:</h4>
                <p>(%s) - %s</p>
                <p>Query: %s</p>
                <hr />'
    ,
                
    mysql_errno(),
                
    mysql_error(),
                
    $sSQL
            
    );
        }
    }
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  15. #15
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW, thanks mate. If we ever meet drinks are on me. LOL.

  16. #16
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    *takes screenshot and mails to lawyer*

    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.


Tags for this Thread

Bookmarks

Posting Permissions

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