Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Aug 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing XML in MYSQL using php, Date issue

    Help

    I am trying to parse a XML file into a Mysql database using PHP. I have go so far butI cant get the date into the database

    This is the code

    Code:
    <?php
    
    // connect to database
    	
    $link = dbConnect();
    if (!$link) {
    	$ERR .= 'Unable to connect to database.<br>Please try again.<br>';
    }
    else { $MESG = 'Database Connection Established<br>'; }
    if ($ERR) { echo "$ERR<br>"; }
    else {
    //echo "$MESG<br>";
    }
    
    $url = "This is the feed";
    
    
    /******************************************* 
    * Function cached_fopen_url(): 
    * Caches data from a HTTP resource/url to a 
    * local file (HTTP headers are stipped) 
    * @returns a file resoruce / or FALSE if failure. 
    **/
    
    function stripfromtext($haystack, $bfstarttext, $endsection) { 
      $startpostext = $bfstarttext; 
      $startposlen = strlen($startpostext); 
      $startpos = strpos($haystack, $startpostext); 
      $endpostext = $endsection; 
      $endposlen = strlen($endpostext); 
      $endpos = strpos($haystack, $endpostext, $startpos); 
      return substr($haystack, $startpos + $startposlen, $endpos - ($startpos + $startposlen)); 
    }
    
    function delfromtext($haystack, $bfstarttext, $endsection) { 
      $startpostext = $bfstarttext; 
      $startposlen = strlen($startpostext);
      $totallen = strlen($haystack); 
      $startpos = strpos($haystack, $startpostext); 
      $endpostext = $endsection; 
      $endposlen = strlen($endpostext); 
      $endpos = strpos($haystack, $endpostext, $startpos); 
      $result = substr($haystack, 0, $startpos);
      $result .= substr($haystack, $endpos, $totallen);
      return $result;
    }
    
    function cached_fopen_url($url, $file_mode, $timeout_seconds = 300, $fsocket_timeout = 300) 
    { 
    
    $debug = false; 
     
    clearstatcache(); 
     
    if ($debug) { 
    print "local_cache creation_time =" . 
    @filemtime($cache_filename) . 
    " actual time = " . time() . 
    " timeout = " . 
    $timeout_seconds ."<p>"; 
    } 
     
    if ( ( @file_exists($cache_filename ) and ( ( @filemtime($cache_filename) + $timeout_seconds) > ( time() ) ) ) ) { 
    // ok, file is already cached and young enouth 
    if ($debug) { print "using cached file ($cache_filename) <p>";} 
    } else { 
     
    if ($debug) { print "cacheing file ($url) to local ($cache_filename)<p>";} 
     
    $urlParts = parse_url($url); 
    $host = $urlParts['host']; 
    $port = (isset($urlParts['port'])) ? $urlParts['port'] : 80; 
     
    if( !$fp = @fsockopen( $host, $port, $errno, $errstr, $fsocket_timeout )) { 
    // Server not responding 
     
    } else { 
     
    if( !fputs( $fp, "GET $url HTTP/1.0\r\nHost:$host\r\n\r\n" )) { 
    die( "unable to send get request" ); 
    } 
     
    $data = null; 
    stream_set_timeout($fp, $fsocket_timeout); 
    $status = socket_get_status($fp); 
    while( !feof($fp) && !$status['timed_out']) 
    { 
    $data .= fgets ($fp,8192); 
    $status = socket_get_status($fp); 
    } 
    fclose ($fp); 
     
    // strip headers 
    $sData = split("\r\n\r\n", $data, 2); 
    $data = $sData[1]; 
    
    } 
    } 
     
    // ok, point to (fresh) cached file 
    if ( @file_exists($cache_filename )) { 
    $handle = fopen($cache_filename, $file_mode); 
    return $handle; 
    } 
     
    return $data; 
    }
    
    $xml = cached_fopen_url($url,"r");
    
    //echo "$xml";
    
    $articlecount = stripfromtext($xml, 'ArticleCount="', '">');
    
    //echo "Articlecount: $articlecount";
    
    $newsdata = array();
    $listitems = "";
    
    for ($i=0;$i<$articlecount;$i++) {
    
    $article = stripfromtext($xml, '<Article', 'Article>');
    
    $created = stripfromtext($article, 'Created="', '" ID');
    
    $newsdata[$i]['created'] = $created;
    
    //echo "Created: $created<br />";
    
    $category = stripfromtext($article, '<Categories>', '</Categories>');
    
    $newsdata[$i]['category'] = $category;
    
    //echo "Category: $category<br />";
    
    $heading = stripfromtext($article, '<Heading>', '</Heading>');
    $heading = mysql_real_escape_string($heading);
    
    $newsdata[$i]['heading'] = $heading;
    
    //echo "Heading: $heading<br />";
     $date = stripfromtext($article, '<Date>', '</Date>'); 
    $newsdata[$i]['date'] = date("Y-m-d",strtotime($date));     
    
    echo "Date: $date<br />";          
    
    $contents = stripfromtext($article, '<Contents>', '</Contents>');
    
    $contents = delfromtext($contents, '<img', '/>');
    
    $contents = str_replace("<![CDATA[", "", $contents);
    
    $contents = str_replace("/>]]>", "", $contents);
    
    $contents = mysql_real_escape_string($contents);
    
    $newsdata[$i]['contents'] = $contents;
    
    //echo "Contents: $contents";
    
    $xml = (delfromtext($xml, '<Article', 'Article>'));
    
    //$listitems .= "<li><a href=\"index.php?section=news&amp;subsection=showarticle&amp;id=$i\" class=\"adfero\">$date <strong>$heading</strong></a></li>";
    
    }
    
    //print_r($newsdata);
    
    $added = 0;
    
    foreach ($newsdata as $key => $val) {
    
    $heading = $val['heading'];
    
    $sql = "SELECT id FROM feed WHERE heading = '$heading'";
    $result = mysql_query($sql, $link) or die ((mysql_error()) . ($sql));
    $count = mysql_num_rows($result);
    //echo "$sql<br>";
    //echo "count $count<br>";
    
    if ($count<1) {
    
    $category = $val['category'];
    $created = $val['created'];
    $time  = strtotime($created);
    $content = $val['contents'];
    $added_date= strtotime($date);
     
     $sql = "INSERT INTO feed (category, created, heading, content, added_date) 
    VALUES ('$category', FROM_UNIXTIME($time), '$heading', '$content', '$date')";  
    
    //echo "$sql<br>";
    mysql_query($sql, $link) or die ((mysql_error()) . ($sql));
    
    $added++;
    
    }
    
    }
    
    //echo "added $added";
    
    ?>
    and this is the xml

    Code:
    <?xml version="1.0" encoding="Windows-1252"?>
    <InfoStreamResults Copyright="(c) 2006 Adfero Ltd." ArticleCount="49">
      <Article Created="14:40:07" ID="18253043">
        <Heading>This is the heading</Heading>
        <Date>21/08/2007</Date>
        <Contents><![CDATA[This is the data]]></Contents>
        <Categories>
          <Category ID="438008113">Environment</Category>
        </Categories>
      </Article>
    Thanks!
    Last edited by webmonkie; 08-31-2007 at 12:03 PM.

  • #2
    Regular Coder PremiumBlend's Avatar
    Join Date
    Apr 2006
    Location
    Marion, Iowa
    Posts
    201
    Thanks
    0
    Thanked 13 Times in 13 Posts
    I use this format for inserting dates:
    PHP Code:
    $newsdata[$i]['date'] = date('Y-m-d H:i:s'strtotime($date)); 
    It has the time as well; I'm not sure if that matters or not though.
    Also, I don't use the "FROM_UNIXTIME" function either.

    What are the errors you're receiving?
    My Website: DumpsterDoggy

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    your xml-parsing looks really complicated. are you using php 4 or 5?
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com


  •  

    Posting Permissions

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