SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: XML -> MySQL

  1. #1
    Non-Member JSebastian's Avatar
    Join Date
    Aug 2006
    Location
    San Diego, CA
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    XML -> MySQL

    Ok, so I'm working on a side project that will read an XML file, Parse the contents and write it to MySQL. Most of the project is done, the script will render the SQL statement just fine, but I can't seem to get it execute the statement.

    I would like to execute the statements as each one is created. I'm not against executing the statements after all of the SQL statements are parsed. However, I realized I couldn't put ";" between statements and don't want to construct a huge string just to rip it apart and execute each element separately.

    PHP Code:
    <?php
    //
    // Parse the ProsperDataExport.xml file and produce MySQL-compatible
    // REPLACE INTO statements to populate database.
    //


    //FIRST TELL THIS MONSTER WHERE XML THE FILE IS
    //EITHER VARIABLE WILL WORK BUT ONE FILE IS 800M
    //MAIN ZIP FILE
    //$prosperURL = "http://services.prosper.com/DataExport/";
    //TEST ZIP FILE
    $prosperURL "http://www.prosperloansonline.com/";
    //XML FILE
    $zipFile "ProsperDataExport_xml.zip";
    $tempZipFile "/tmp/$zipFile";
    $xmlFile "ProsperDataExport.xml";

    // CHANGE THIS TO READ FROM SOMEWHERE ELSE IF NEEDED:
    `curl -s -N $prosperURL/$zipFile > $tempZipFile`;
    $fp popen("unzip -p $tempZipFile $xmlFile""r");
    if (!
    $fp) {
     
    unlink($tempZipFile);
     die(
    "could not open XML input");
    }

    global 
    $tables;
    //AND ADD TO THIS IF YOU WANT TO IMPORT TO MORE TABLES:
    $tables = array('Bid');
    global 
    $inItem;
    $inItem false;
    global 
    $itemData;
    global 
    $itemKey;

    function 
    startElement($parser$name$attrs)
    {
     global 
    $inItem;
     global 
    $itemData;
     global 
    $itemKey;
     global 
    $tables;
     if (!
    $inItem && in_array($name$tables)) {
       
    $inItem true;
       
    $itemData = array();
     }
     elseif (
    $inItem) {
       
    $itemData[$name] = "";
       
    $itemKey $name;
     }
    }

    function 
    charData($parser$data) {
     global 
    $inItem;
     global 
    $itemData;
     global 
    $itemKey;
     if (!
    $inItem) { return; }
     
    $itemData[$itemKey] .= $data;
    }

    function 
    endElement($parser$name)
    {
     global 
    $inItem;
     global 
    $itemData;
     global 
    $itemKey;
     global 
    $tables;
     global 
    $sqlmain;
    //LOOP THROUGH DATA
     
    if (in_array($name$tables)) {
        
    $inItem false;
        
    $table $name;
        
    $sql "REPLACE INTO `$table`  SET ";
        
    $vals "";
        foreach (
    $itemData AS $k => $v) {
          
    $v trim($v);
          if (
    $v != '') {
            
    $vals .= "`$k`='".addslashes($v)."',";
          }
        }
        
    //PUT THE SQL STATEMENTS TOGETHER
        
    $vals rtrim($vals",");
        
    $sql $sql $vals ";";
        
    //IDEALLY THE SQL STATEMENT WOULD EXECUTE HERE
        //mysql_query( $sql, $conn );
        //BUT IT DOESN'T SEEM TO WORK INSIDE THE LOOP
        //SO I TRIED TO CONCATENATE THE STRINGS
        
    $sqlmain $sqlmain $sql;
        
    //...AND EXECUTE MULTIPLE STATEMENTS ONCE THE LOOP IS DONE
        //THIS WORKS WITH A FEW DATA ELEMENTS BUT NOT WITH THE
        //HUGE FILE.  THEREFORE I WOULD LIKE TO EXECUTE EVERY STATEMENT
        //AS IT IS PARSED
        //MAYBE INSTEAD OF CONCATENATION EACH STATEMENT COULD GO INTO 
        //AN ARRAY AND EACH ELEMENT OF THE ARRAY COULD BE EXECUTED 
        //AFTER THE LOOP
     
    }
    }


    //THIS IS WHERE THE ACTION HAPPENS
    $xml_parser xml_parser_create("UTF-8");
    xml_parser_set_option($xml_parserXML_OPTION_CASE_FOLDING0);
    xml_set_element_handler($xml_parser"startElement""endElement");
    xml_set_character_data_handler($xml_parser"charData");

    while (
    $data fread($fp4096)) {
     if (!
    xml_parse($xml_parser$datafeof($fp))) {
       die(
    sprintf("XML error: %s at line %d",
                   
    xml_error_string(xml_get_error_code($xml_parser)),
                   
    xml_get_current_line_number($xml_parser)));
     }
    }
    xml_parser_free($xml_parser);
    unlink($tempZipFile);

    $dbuser 'xxxx';
    $dbpass 'xxxx';
    $dbname 'xxxx';
    $dbhost 'xxxx';

    if(
    $conn = @mysql_connect($dbhost,$dbuser,$dbpass)) 
        {
            echo 
    "<p>Database connection established...</p>\n";
            
    $db_selected mysql_select_db($dbname$conn);
            if (!
    $db_selected) { die ('Error: '.$dbname.' : '.mysql_error()); }
            
            echo 
    '<p>Time to import some data...</p>';
            
    //QUERY WILL WORK HERE BUT NOT IN LOOPS OR WITH LARGE FILE
            //mysql_query($sql, $conn );
    }
    ?>
    Last edited by stymiee; Mar 27, 2007 at 13:06. Reason: please only hire or trade services in the marketplace

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't see any reason for your query to not execute inside a loop, but then again I've not spent too long trying to work through your code. Here's a technique I've had great success with when doing multiple queries:
    1) Build an array of strings, each string being a single SQL query
    2) Use a foreach loop to run through the array and execute each query
    Example:
    PHP Code:
    $sql = array(); //I've found this to be necessary in this case
    $sql[] = "SELECT * FROM table1 WHERE blah='$blah'";
    $sql[] = "INSERT INTO table1 VALUES ('$blah1','$blah2')";
    $sql[] = "DROP TABLE table1";

    foreach(
    $sql as $query)
    {
        
    mysql_query($query$conn) or die("Error executing query\n\"$query\":\n".mysql_error());

    This might work for you.
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    Non-Member JSebastian's Avatar
    Join Date
    Aug 2006
    Location
    San Diego, CA
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know why it doesn't work inside the loop either. I've even tried to pull out the variable and use a static string...Nothing! If I drop in an echo statement, or move the SQL statement, it works every time.

    I was going to try the array method next. Once I get a solution to work out I'll PM whoever for an email address and payment.

  4. #4
    SitePoint Evangelist ikeo's Avatar
    Join Date
    Oct 2004
    Location
    Austin Texas
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. What error do you get when you try to execute the sql.
    2. Can you post a sample SQL string that you've echoed out okay.


    Quote Originally Posted by JSebastian View Post
    I don't know why it doesn't work inside the loop either. I've even tried to pull out the variable and use a static string...Nothing! If I drop in an echo statement, or move the SQL statement, it works every time.

    I was going to try the array method next. Once I get a solution to work out I'll PM whoever for an email address and payment.

  5. #5
    SitePoint Enthusiast csjc2662's Avatar
    Join Date
    Feb 2007
    Location
    Texas, USA
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

     
    do{
           if (!
    xml_parse($xml_parser$datafeof($fp))) {
                die(
    sprintf("XML error: %s at line %d",
                   
    xml_error_string(xml_get_error_code($xml_parser)),
                   
    xml_get_current_line_number($xml_parser)));
    }
    }while(
    $data fread($fp4096)) 
    It will go through the first time and can reiterate itself through. I don't know much about xml and I'm just starting mysql/php but
    James
    New links coming soon....

  6. #6
    SitePoint Member cleberwillian's Avatar
    Join Date
    Jul 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


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
  •