How do I parse a XML feed and store its individual elements into MySQL?

I need to parse this xml feed
http://www.thetimes.co.za/RSS/ST_Business_01.xml
and after that I want to store the articles in MySQL db.
I am using the SImpleXMLElement  method, and I am unable to extract the contents of the feed. Moreover this feed is continuously changing, .

I have attched the php code with .txt extension. Please have a look at it ,and part from it please also suggest how to store the  contents in mysql
x-parse.txt
LVL 1
Ammar IqbalSenior IT Consultant/senior Software engineerAsked:
Who is Participating?
 
hieloConnect With a Mentor Commented:
Ammar,
Please close the questions that have already been solved. EE rules state that you ask one question per post. You keep adding more and more to a single question. That's against the rules. Furthermore, it as I help others your question just keeps scrolling deeper and deeper out of view. The original question has already been answered so you need to closed this.
0
 
Xyptilon2Commented:
Should be something like:

<?php
// Configure database login credentials
// Replace mysql_user and password with username and password and database with your databasename.

$sUsername = "mysql_user";
$sPassword = "mysql_password";
$sDatabase = "database";

// Connect to database and select database
$oDb = mysql_connect('localhost', $sUsername, $sPassword) or die(mysql_error());
mysql_select_db ($sDatabase) or die("Could not select database" . mysql_error());

// Retrieve XML

$url = "http://www.thetimes.co.za/RSS/ST_Business_01.xml";
$sxe = new SimpleXMLElement($url, NULL, TRUE);

for ($x=0;$x<count($sxe->articles);$x++) {
      
      $artid = mysql_real_escape_string($sxe->article[$x]-> artid );

      $iSQL = "INSERT INTO table (...........) ";
      $oRs = mysql_query($iSQL);

}
?>

Goodluck, i hope this is useful, i've to go now, if you dont have it figured out by tomorow morning, i'll work it out for you
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
There is a lot of CDATA involved,I think that is not working. I need more help and suggestion,
I need to store artid, title, leadtext,bodytext, firstpublished, lasteditedin MySQL in the Articles Table.
Please send me a elaborate solution.
looking forward to hear from you.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
hieloCommented:
try this:
<?php
// Connect to database
$oDb = mysql_connect('localhost', 'username', 'password') or die(mysql_error());
// select database
mysql_select_db ('databaseName') or die("Could not select database" . mysql_error()); 
$url = "http://www.thetimes.co.za/RSS/ST_Business_01.xml";
$articles = new SimpleXMLElement($url, NULL, TRUE); 
$vals='';
foreach($articles as $article)
{
	$vals .= sprintf(",('%s','%s','%s','%s','%s','%s')",
					mysql_real_escape_string($article->artid),
					mysql_real_escape_string($article->title),
					mysql_real_escape_string($article->leadext),
					mysql_real_escape_string($article->bodytext),
					mysql_real_escape_string($article->firstpublished),
					mysql_real_escape_string($article->lasteditedin)
				);
}
$qry="INSERT INTO `Articles`(`artid`, `title`, `leadtext`,`bodytext`, `firstpublished, `lasteditedin` ) VALUES(" . substr($vals,1). ")"
mysql_query($qry) or die(mysql_error);
?>

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Hi thanx for the solution,
I am not getting what the parameters of SImpleXMLElement constructor
$articles = new SimpleXMLElement($url, NULL, TRUE);  stand for.

Secondly , there is a drawback of SimpleXMLElement  that it cannot handle CDATA

$xml_string=file_get_contents('http://www.thetimes.co.za/RSS/ST_World_News_01.xml');
$xml= file_get_contents ( $xml_string )

How to handle this problem?

Regards,
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
$xml_string=file_get_contents('http://www.thetimes.co.za/RSS/ST_World_News_01.xml');
$xml=simplexml_load_string($xml_string, 'SimpleXMLElement', LIBXML_NOCDATA);

$url=simplexml_load_file($xml);

Is this thing ok?
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Can you please send me the complete solution in proper sequence? starting from reading the file ,parsing it ,and storing into MySQL db? taking care of all CDATA
Regards and Thanx
0
 
hieloCommented:
>>Secondly , there is a drawback of SimpleXMLElement  that it cannot handle CDATA
Yes it can. It worked for me.

>>How to handle this problem?
No problem at all. What I posted earlier was tested before posting an it works.

>>Can you please send me the complete solution in proper sequence? starting from reading the file ,parsing it ,and storing into MySQL db? taking care of all CDATA
That's exactly what I did. I don't know what's going on at your server.

>>Regards and Thanx
You are welcome and good luck.
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Is the syntax of insert query is correct, I am single quotes or other stuff. I do not know it is not working at my XAMPP Server.
server localhost
username="root"
password= " "
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
it is not working on my server. I have also checked mysql ,apache ,php is correctly configured
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
hello are u there
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost' (using password: YES) in C:\xampp\htdocs\parse.php on line 14
mysql_error

this warning is continuously coming..for the following attached code.
I ma using Xampp. the password for root is blank
whenever I try to reset the password,the whole Xampp server gets in accessible
waiting for your reply

$sUsername = "root";
$sPassword = " ";
$sDatabase = "article_category_db";
$Server="localhost";
 
// Connect to database
$oDb = mysql_connect($Server,$sUsername,$sPassword) or die(mysql_error);
mysql_select_db ($sDatabase) or die("Could not select database" . mysql_error());

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Hi again ,
mysql_connect is resolved ,but I am getting the mysql_error on mysql_query.
probably some thing wrong with the Insert query
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
$qry="INSERT INTO Articles(artid,title,leadtext,bodytext,firstpublished,lastedited) VALUES(" . substr($vals,1). ")";
mysql_query($qry) or die(mysql_error);


URGENT ATTENTION NEEDED
Please check the syntax of this query. IT is not inserting the records in db
0
 
hieloCommented:
>>IT is not inserting the records in db
Did you echo $qry to make verify the $qry is not empty? Are you getting any error messges?

>>mysql_query($qry) or die(mysql_error);
missing parenthesis after mysql_error:
mysql_query($qry) or die( mysql_error() );
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''artid','title','leadtext','bodytext','firstpublished','lastedited') VALUES(('86' at line 1

This is the message if I echo $qry
$qry="INSERT INTO Articles('artid','title','leadtext','bodytext','firstpublished','lastedited') VALUES(" .substr($vals,1). ")";
0
 
hieloCommented:
>>Articles('artid','title','leadtext','bodytext','firstpublished','lastedited')
That is NOT what I gave you. I was expecting you to copy and paste. You are using apostrophes around the field names. That is NOT correct. Wht you need are backticks, NOT apostrophes (The backtick character is located on the same key as the  tilde (~) character on a standard keyboard).
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
$qry="INSERT INTO `Articles`(`artid`, `title`, `leadtext`,`bodytext`, `firstpublished, `lastedited` ) VALUES(" . substr($vals,1). ")";

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lastedited` ) VALUES(('860914','Think back to 29 and be scared. Very sca' at line 1


Now this is the error
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Have you manage to store all values in db using this query. I am not figuring out  what is going with me
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Column count doesn't match value count at row 1

I have figured out. what could be the reason, maybe something to do with VALUES(......) section
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
$qry="INSERT INTO `articles`(`artid`,`title`,`leadtext`,`bodytext`,`firstpublished,`lastedited`)  VALUES(" .substr($vals,1). ")";

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lastedited`) VALUES(('860914','Think back to 29 and be scared. Very scar' at line 1

it says that ihave a syntax error nears lastedited
0
 
hieloCommented:
>>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lastedited`
There is a missing backtick to the right of `firstpublished`
$qry="INSERT INTO `articles`(`artid`,`title`,`leadtext`,`bodytext`,`firstpublished`,`lastedited`)  VALUES(" .substr($vals,1). ")";

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Thanx..

Column count doesn't match value count at row 1

now this message is coming. what could be the possible reason
0
 
hieloCommented:
remove the parenthesis sorrounding VALUES. Instead of:

$qry="INSERT INTO `Articles`(`artid`, `title`, `leadtext`,`bodytext`, `firstpublished`, `lasteditedin` ) VALUES(" . substr($vals,1). ")"
 
try:
$qry="INSERT INTO `Articles`(`artid`, `title`, `leadtext`,`bodytext`, `firstpublished`, `lasteditedin` ) VALUES " . substr($vals,1);

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
it works but

Duplicate entry '127' for key 1
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
now i tihnk we need to to make a check on lastedited field, because everytime when a new feed is coming it can check for the duplicate entry
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
How should we figure this out? to prevent the already existing from being duplicated
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Sometimes an article that already exists in the DB will reoccur in the feed. This is most often because the article has been updated. The <lastedited> field will differ in the db and the feed. Use this info to only update changed articles.
0
 
hieloCommented:
>>now i tihnk we need to to make a check on lastedited field
Ok, hold on...
0
 
hieloCommented:
try:
foreach($articles as $article)
{
	$lasteditedin=mysql_real_escape_string($article->lasteditedin);
	$qry = sprintf("SELECT count(*) as `total` FROM `Articles` WHERE `lasteditedin`='%s'",$lasteditedin);
	$result=mysql_query($qry) or die(mysql_error());
	if( 0==mysql_num_rows($result) )
	{
		$vals .= sprintf(",('%s','%s','%s','%s','%s','%s')",
					mysql_real_escape_string($article->artid),
					mysql_real_escape_string($article->title),
					mysql_real_escape_string($article->leadext),
					mysql_real_escape_string($article->bodytext),
					mysql_real_escape_string($article->firstpublished),
					$lasteditedin
				);
	}
}

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
foreach($articles as $article)
{
       $lastedited=mysql_real_escape_string($article->lastedited);
	$qry = sprintf("SELECT count(*) as `total` FROM `articles` WHERE `lastedited`='%s'",$lastedited);
	$result=mysql_query($qry) or die(mysql_error());
        if(mysql_num_rows($result)==0 )
	{
             
                   $vals .= sprintf(",('%s','%s','%s','%s','%s','%s')",
                                        mysql_real_escape_string($article->artid),
                                        mysql_real_escape_string($article->title),
                                        mysql_real_escape_string($article->leadtext),
                                        mysql_real_escape_string($article->bodytext),
                                        mysql_real_escape_string($article->firstpublished),
                                        $lastedited
                              );
 
       }
}

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
when I commented the new ly added lines then itgives duplicate entry? and when i uncomment them then it  gives this error message
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
i am not figuring where could be the syntax error
0
 
hieloCommented:
AFTER the foreach, you also need to make sure that $vals is NOT empty:
foreach(...)
{
...
}
if( !empty($vals) )
{
	$qry="INSERT INTO `Articles`(`artid`, `title`, `leadtext`,`bodytext`, `firstpublished`, `lasteditedin` ) VALUES " . substr($vals,1);
	mysql_query($qry) or die(mysql_error());
}

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
there is no error message and also no duplicate entry mesage but there is onlt 1 row stored in the table.

is this same with you?
In the presentation of the articles,  I need to present present Header, date, picture and leadtext from this feed. and no need to link the complete test. and I have to use proper CSS tags
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Only one record has been added ,despite of the factr there are more than one articles in this feed
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Are you there?
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
I want to add artid from articles and catid from category tables into an art_cat_link table.
This table links between articles and category tables and it has three fields , id, artid, catid.

I have attach the query for this. I am not figuring out the exact syntax in MySQL and PHP. Kindly check it and correctthe query

looknig forward to hear from you
$qry1="INSERT INTO `art_cat_link`(`id`,`artid`,`catid`)  VALUES(``,`SELECT `artid` FROM articles`,`SELECT `catid` FROM category`)"

Open in new window

0
 
hieloCommented:
>>Only one record has been added ,despite of the factr there are more than one articles in this feed
Are you sure there aren't any duplicate `lastedited` values in the db already? Try the code below. It should "report" any duplicate/existing records on the db:
<?php
...
$vals='';
foreach($articles as $article)
{
	$lastedited=mysql_real_escape_string($article->lastedited);
	$qry = sprintf("SELECT count(*) as `total` FROM `articles` WHERE `lastedited`='%s'",$lastedited);
	$result=mysql_query($qry) or die(mysql_error());
	$total = mysql_num_rows($result);
	if($total==0 )
	{
             
                   $vals .= sprintf(",('%s','%s','%s','%s','%s','%s')",
                                        mysql_real_escape_string($article->artid),
                                        mysql_real_escape_string($article->title),
                                        mysql_real_escape_string($article->leadtext),
                                        mysql_real_escape_string($article->bodytext),
                                        mysql_real_escape_string($article->firstpublished),
                                        $lastedited
                              );
	}
	else
	{
		echo "<p>Found {$total} existing items for `lastedited`='{$lastedited}'</p>";
	}
}
if( !empty($vals) )
{
	$qry="INSERT INTO `articles`(`artid`, `title`, `leadtext`,`bodytext`, `firstpublished`, `lasteditedin` ) VALUES " . substr($vals,1);
	mysql_query($qry) or die(mysql_error());
}
?>

Open in new window

0
 
Ray PaseurCommented:
@Ammar77: You need some basic help with the essentials of PHP and MySQL.  Please consider getting this book and using it both as a reference and tutorial -- it is not exhaustive, but it will keep you out of a lot of the "syntax error" trouble and will help you concentrate on solving the application issues.

http://www.sitepoint.com/books/phpmysql1/

Also, if you want a good MySQL reference, get the one from Osborne Publishers by Vaswani.

Best of luck, ~Ray
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Please take a look at second query , Select statement within an INSERT one. Although there is not syntax error, But it is not inserting adds in the db table

Thank you and Regards,
if( !empty($vals) )
{
$qry="INSERT INTO `articles`(`artid`,`title`,`leadtext`,`bodytext`,`firstpublished`,`lastedited`)  VALUES ".substr($vals,1);
echo $qry;
mysql_query($qry) or die(mysql_error());
 
 
$qry_link="INSERT INTO `art_cat_link`(`artid`,`catid`) VALUES (SELECT `artid` FROM `articles` ,SELECT `catid` FROM `category` WHERE `catname`=`Business News` )" ;
echo $qry_link;
mysql_query($qry_link) or die(mysql_error());
}

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Ho again,
Maybe there is some problem in query. I want to insert artid and catid in art_cat_link table form artif of aeticles amd catid of category table

Regards,

$qry_link="INSERT INTO `art_cat_link`(`artid`,`catid`) VALUES (SELECT `artid` FROM `articles` ,SELECT `catid` FROM `category` WHERE `catname`=`Business News` )" ;
echo $qry_link;
mysql_query($qry_link) or die(mysql_error());

Open in new window

0
 
hieloCommented:
You still have not answered my question. Is this:
$qry="INSERT INTO `articles`(`artid`,`title`,`leadtext`,`bodytext`,`firstpublished`,`lastedited`)  VALUES ".substr($vals,1);
echo $qry;
mysql_query($qry) or die(mysql_error());

working as expected? Is it still giving you problems? If yes, then what error messages are you seeing?
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Yes it works fine at the moment. Please check my latest query . I want to apply this query right after insert records in articles table.
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
artid and catid are tow respective primary keys of tables articles and category, I want to link articles with categories.. I forom a new table art_cat_link  which has two fields artid and catid acting foreign keys in this table for their respective tables.

$qry_link="INSERT INTO `art_cat_link`(`artid`,`catid`) VALUES (SELECT `artid` FROM `articles` ,SELECT `catid` FROM `category` WHERE `catname`=`Business News` )" ;
echo $qry_link;
mysql_query($qry_link) or die(mysql_error());

In this way the moment , articles is poulated , I want to poulate art_cat_link  as well. I am not getting the idea of how into select statments in insert query, asm mysql and php and some sort of different syntax of the different queries as compared to normal SQL used in other dbs such as SQL Server , oracle etc

Thank you
looking forward
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
iin this way i will link articles with their respective categories
0
 
hieloCommented:
The syntax is:
INSERT INTO Table (FIELD1,FIELD2) SELECT FIELDA,FIELDB FROM Table

I don't know what's the relationship between the two different tables you are trying to retrieve the values from but if my guess would be:
$qry_link="INSERT INTO `art_cat_link`(`artid`,`catid`) SELECT DISTINCT `artid`, `catid` FROM `articles`,`category` WHERE `catname`='Business News'" ;

If your problem persists, please open a new problem. This is now a different issue. The orginal problem has been resolved.
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
i have opened a new question for this
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
i have opened a thirds question also. That is also urgent.

Regards
0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
i have added the combined code for all the three xml feed categories.
Kindly have a look at it. When I run on this web server, because of the functionality to remove duplicate values, one thing i am noticing that it is not adding anything fresh from the feed into db.
for example ,in my db , I ran these files on my web server, it added the latest feed to be added wason 13 th oct.... today ,there are also some new news being added in the feed url, but it is not added into the db with oct 14 date
<?php
/*
 * Created on 11. okt.. 2008
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
$sUsername = "root";
$sPassword = "";
$sDatabase = "article_category_db";
$Server="localhost";
 
// Connect to database
$oDb = mysql_connect($Server,$sUsername,$sPassword) or die(mysql_error());
mysql_select_db ($sDatabase) or die("Could not select database" . mysql_error());
 
 
 
/////// Business News Category //////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
echo 'Business News';
$url_business= "http://www.thetimes.co.za/RSS/ST_Business_01.xml";
$articles = new SimpleXMLElement($url_business, NULL, TRUE);
//echo $articles->AsXML();
$vals='';
foreach($articles as $article)
{
          $lastedited=mysql_real_escape_string($article->lastedited);
	  $qry = sprintf("SELECT count(*) as `total` FROM `articles` WHERE `lastedited`='%s'",$lastedited);
	 $result=mysql_query($qry) or die(mysql_error());
         $total = mysql_num_rows($result);
         if($total==0)
	 {
             
                   $vals .= sprintf(",('%s','%s','%s','%s','%s','%s')",
                                        mysql_real_escape_string($article->artid),
                                        mysql_real_escape_string($article->title),
                                        mysql_real_escape_string($article->leadtext),
                                        mysql_real_escape_string($article->bodytext),
                                        mysql_real_escape_string($article->firstpublished),
                                        $lastedited
                                        //mysql_real_escape_string($article->lastedited)
                              );
 
         }
        else
        {
               echo "<p>Found {$total} existing items for `lastedited`='{$lastedited}'</p>";
        }
}
 
if( !empty($vals) )
{
$qry="INSERT INTO `articles`(`artid`,`title`,`leadtext`,`bodytext`,`firstpublished`,`lastedited`)  VALUES ".substr($vals,1);
echo $qry;
mysql_query($qry) or die(mysql_error());
 
$qry_link="INSERT INTO `art_cat_link`(`artid`,`catid`) SELECT DISTINCT `artid`, `catid` FROM `articles`,`category` WHERE `catname`='Business News'" ;
//$qry_link="INSERT INTO `art_cat_link`(`artid`,`catid`) VALUES (SELECT `artid` FROM `articles` ,SELECT `catid` FROM `category` WHERE `catname`=`Business News` )" ;
//echo $qry_link;
//mysql_query($qry_link) or die(mysql_error());
}
 
 
 
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////world News Category
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
echo 'World News';
$url_world = "http://www.thetimes.co.za/RSS/ST_World_News_01.xml";
$articles2 = new SimpleXMLElement($url_world, NULL, TRUE);
//echo $articles2->AsXML();
$vals='';
foreach($articles2 as $article)
{
        $lastedited=mysql_real_escape_string($article->lastedited);
        $qry = sprintf("SELECT count(*) as `total` FROM `articles` WHERE `lastedited`='%s'",$lastedited);
	$result=mysql_query($qry) or die(mysql_error());
        $total_world=mysql_num_rows($result);
        if($total_world==0)
	{
             
                   $vals .= sprintf(",('%s','%s','%s','%s','%s','%s')",
                                       mysql_real_escape_string($article->artid),
                                        mysql_real_escape_string($article->title),
                                       mysql_real_escape_string($article->leadtext),
                                        mysql_real_escape_string($article->bodytext),
                                        mysql_real_escape_string($article->firstpublished),
                                        $lastedited
                                       // mysql_real_escape_string($article->lastedited)
                              );
 
        }
         else
        {
               echo "<p>Found {$total} existing items for `lastedited`='{$lastedited}'</p>";
        }
}
 
if( !empty($vals) )
{
$qry2="INSERT INTO `articles`(`artid`,`title`,`leadtext`,`bodytext`,`firstpublished`,`lastedited`)  VALUES ".substr($vals,1);
//echo $qry;
mysql_query($qry2) or die(mysql_error());
}
 
////sport News Category
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
echo 'Sport News';
$url_sport = "http://www.thetimes.co.za/RSS/ST_Sport_01.xml";
$articles3 = new SimpleXMLElement($url_sport, NULL, TRUE);
//echo $articles3->AsXML();
$vals='';
foreach($articles3 as $article)
{
           $lastedited=mysql_real_escape_string($article->lastedited);
           $qry = sprintf("SELECT count(*) as `total` FROM `articles` WHERE `lastedited`='%s'",$lastedited);
	   $result=mysql_query($qry) or die(mysql_error());
           $total_world=mysql_num_rows($result);
           if($total_world==0)
	   {
           
                   $vals .= sprintf(",(h%s','%s','%s','%s','%s','%s')",
                                       mysql_real_escape_string($article->artid),
                                        mysql_real_escape_string($article->title),
                                       mysql_real_escape_string($article->leadtext),
                                        mysql_real_escape_string($article->bodytext),
                                        mysql_real_escape_string($article->firstpublished),
                                       $lastedited
                                        //mysql_real_escape_string($article->lastedited)
                              );
 
          }
         else
         {
               echo "<p>Found {$total} existing items for `lastedited`='{$lastedited}'</p>";
         }
}
 
if( !empty($vals) )
{
$qry3="INSERT INTO `articles`(`artid`,`title`,`leadtext`,`bodytext`,`firstpublished`,`lastedited`)  VALUES ".substr($vals,1);
//echo $qry3;
mysql_query($qry3) or die(mysql_error());
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
 
 
?>

Open in new window

0
 
Ammar IqbalSenior IT Consultant/senior Software engineerAuthor Commented:
Thank you for the solution.  It  really helped  me to update my skills related to php and mysql
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.