...

View Full Version : Parsing XML in MYSQL using php, Date issue



webmonkie
08-31-2007, 12:49 PM
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


<?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


<?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!

PremiumBlend
08-31-2007, 10:19 PM
I use this format for inserting dates:

$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?

GJay
08-31-2007, 10:37 PM
your xml-parsing looks really complicated. are you using php 4 or 5?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum