RSS to MySQL?

[UnrealEgg]

I have been trying very hard to get this script to work but I am no good at arrays...

 

Anyhow, the basic idea is that the script will read an RSS feed and post all that content to the database which will be used to display content on the site.

 

<?php

$doc = new DOMDocument();
$doc->load('http://newsrss.bbc.co.uk/rss/sportonline_uk_edition/football/rss.xml');
$arrFeeds = array();
foreach ($doc->getElementsByTagName('item') as $node) {
	$itemRSS = array ( 
		'title' => $node->getElementsByTagName('title')->item(0)->nodeValue,
		'desc' => $node->getElementsByTagName('description')->item(0)->nodeValue,
		'link' => $node->getElementsByTagName('link')->item(0)->nodeValue,
		'date' => $node->getElementsByTagName('pubDate')->item(0)->nodeValue
		);
	array_push($arrFeeds, $itemRSS);
}

include 'dbconnect.php';

foreach ($arrFeeds as $item) {
$sql="INSERT INTO tb_rss (title, desc, link, date) VALUES ($item => title), $item => desc, $item => link, $item => date)";
	if (!mysql_query($sql)){ 
		die('Error: ' . mysql_error()); 
	}
}
?>

 

Current error messege is:

Error: 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 'desc, link, date) VALUES (Array => title), Array => desc, Array => link, Array =' at line 1

 

Help please?

[mikosiko]

you have an incorrect ")" after the first item in your VALUES ($item => title),... etc..etc

[UnrealEgg]

Removed the bracket and still the same error.

[dgoosens]

this part is not correct:

 ($item => title), $item => desc, $item => link, $item => date)

should be

$item['title'] etc.

[UnrealEgg]

this part is not correct:

 ($item => title), $item => desc, $item => link, $item => date)

should be

$item['title'] etc.

 

Yeh, I noticed this myself but I am still getting the same error. Theres something up with the SQL query or something.  I have no idea what it is though 

 

EDIT:

Current status on the script:

<?php

$doc = new DOMDocument();
$doc->load('http://newsrss.bbc.co.uk/rss/sportonline_uk_edition/football/rss.xml');
$arrFeeds = array();
foreach ($doc->getElementsByTagName('item') as $node) {
	$itemRSS = array ( 
		'title' => $node->getElementsByTagName('title')->item(0)->nodeValue,
		'desc' => $node->getElementsByTagName('description')->item(0)->nodeValue,
		'link' => $node->getElementsByTagName('link')->item(0)->nodeValue,
		'date' => $node->getElementsByTagName('pubDate')->item(0)->nodeValue
		);
	array_push($arrFeeds, $itemRSS);
}

include 'dbconnect.php';

foreach ($arrFeeds as $item) {
$sql="INSERT INTO tb_rss (title, desc, link, date) VALUES ($item[title], $item[desc], $item[link], $item[date])";
	if (!mysql_query($sql)){ 
		die('Error: ' . mysql_error()); 
	}
}
?>

 

Current error:

Error: 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 'desc, link, date) VALUES (Carson out of England qualifier, Goalkeeper Scott Cars' at line 1

[dgoosens]

notice that I put

$item['title']

not

$item[title]

[dgoosens]

the error you are getting makes me think you have quotation marks within the values you are trying to put into your DB.

 

Goalkeeper Scott Cars'

 

You should escape these...

[UnrealEgg]

notice that I put

$item['title']

not

$item[title]

 

If I do that I get:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

 

I tried using "mysql_real_escape_string" already and its not working still. I looked at the article too and theres no quotes and stuff around where it says that error.

[dgoosens]

try with something like this

VALUES(" . mysql_real_escape_string($item['title']) . ", " . mysql_real_escape_string($item['title']). ", " etc.

[UnrealEgg]

try with something like this

VALUES(" . mysql_real_escape_string($item['title']) . ", " . mysql_real_escape_string($item['title']). ", " etc.

 

Still the same error.

[dgoosens]

try with something like this

VALUES(" . mysql_real_escape_string($item['title']) . ", " . mysql_real_escape_string($item['title']). ", " etc.

 

Still the same error.

 

could you post your new script ?

[UnrealEgg]

try with something like this

VALUES(" . mysql_real_escape_string($item['title']) . ", " . mysql_real_escape_string($item['title']). ", " etc.

 

Still the same error.

 

could you post your new script ?

 

<?php

$doc = new DOMDocument();
$doc->load('http://newsrss.bbc.co.uk/rss/sportonline_uk_edition/football/rss.xml');
$arrFeeds = array();
foreach ($doc->getElementsByTagName('item') as $node) {
	$itemRSS = array ( 
		'title' => $node->getElementsByTagName('title')->item(0)->nodeValue,
		'desc' => $node->getElementsByTagName('description')->item(0)->nodeValue,
		'link' => $node->getElementsByTagName('link')->item(0)->nodeValue,
		'date' => $node->getElementsByTagName('pubDate')->item(0)->nodeValue
		);
	array_push($arrFeeds, $itemRSS);
}

include 'dbconnect.php';

foreach ($arrFeeds as $item) {

$sql="INSERT INTO tb_rss (title, desc, link, date) VALUES(" . mysql_real_escape_string($item['title']) . ", " . mysql_real_escape_string($item['desc']). ", " . mysql_real_escape_string($item['link']). ", " . mysql_real_escape_string($item['date']). ")";
	if (!mysql_query($sql)){ 
		die('Error: ' . mysql_error()); 
	}
}
?>

 

Error:

Error: 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 'desc, link, date) VALUES(Carson out of England qualifier, Goalkeeper Scott Carso' at line 1

[dgoosens]

could you give this a try:

$sql="INSERT INTO tb_rss (title, desc, link, date) VALUES('" . mysql_real_escape_string($item['title']) . "', '" . mysql_real_escape_string($item['desc']). "', '" . mysql_real_escape_string($item['link']). "', '" . mysql_real_escape_string($item['date']). "')";

[UnrealEgg]

could you give this a try:

$sql="INSERT INTO tb_rss (title, desc, link, date) VALUES('" . mysql_real_escape_string($item['title']) . "', '" . mysql_real_escape_string($item['desc']). "', '" . mysql_real_escape_string($item['link']). "', '" . mysql_real_escape_string($item['date']). "')";

 

Same error:

Error: 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 'desc, link, date) VALUES('Carson out of England qualifier', 'Goalkeeper Scott Ca' at line 1

[UnrealEgg]

Got it solved. It seems like my database did not like the "desc" field so i renamed it to "description" and its working fine!

 

Thank you for your support.

[dgoosens]

ok...

nice...

cuz I did not really know what to answer anymore...

 

by the way, you should consider working with the mysqli extension...

 

[mikosiko]

for your future reference (if you don't already have it)

 

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html