xml to mysql

[mike16889]

i have been trying to write a script to take an xml file from thetvdb and import the data into a mysql database, but it keeps saying:

 Chuck Versus the Third Dimension (2D):Chuck Versus the Third Dimension (2D):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 'Versus the Third Dimension (2D):, Chuck foils a plan to kill Tyler Martin' at line 1

 

the sample.xml is this file

http://www.thetvdb.com/api/217F7921A2EA56C0/series/80348/all/en.xml

 

and here is my code:

<?php
$tvdb_mirror = "http://www.thetvdb.com/api/";
$tvdb_time = "http://www.thetvdb.com/api/Updates.php?type=none";
$dbname = "mediadb";
$dbuser = "root";
$dbpass = "";
$dbserv = "127.0.0.1";

$rss = simplexml_load_file('sample.xml');
$showName = "Show Name = ".$rss->Series->SeriesName;


mysql_connect('127.0.0.1', 'root', '');
@mysql_select_db('mediadb') or die("Unable to select database");
echo ' ';
foreach ($rss->Episode as $item) {
	$seasonnum = $item->Combined_season;
	$EpisodeNumber = $item->EpisodeNumber;

		if($EpisodeNumber < 10){
			$EpisodeNumber = "0".$EpisodeNumber;
		};


	$EpisodeName1 = $item->EpisodeName;
	$EpisodeName1 = str_replace($array1, $array2, $EpisodeName1 );
	echo $EpisodeName1;
	$EpisodeName = mysql_real_escape_string($EpisodeName1);
	echo $EpisodeName;
	$Overview1 = $item->Overview;
	$Overview = mysql_real_escape_string($Overview1);
	$airdate = $item->FirstAired;
	$tvdbid = $item ->id;
	$query = "INSERT INTO eppisodes VALUES('1', ".$EpisodeName.", ".$Overview.", ".$airdate.", '1', ".$tvdbid.", '-1', ".$seasonnum.", ".$EpisodeNumber.")";
	mysql_query($query) or die (mysql_error());
	print "<br />".$showName." - ".$seasonnum."x".$EpisodeNumber." - ".$EpisodeName." Overview:<br />".$Overview;
}
mysql_close();
?>

 

i think its to do with the contents of the xml file, i thing there are special charictors in there breaking out of the mysql, but iv tried html entaties and mysql special escape string and nither seem to work, i even tried to use str_replace to replace all the commas and stuff with ' and the like. not sure were to go from here.

 

any help? please?

[jonsjava]

You weren't placing text in single quotes. try this:

$query = "INSERT INTO eppisodes VALUES('1', '$EpisodeName', '$Overview', '$airdate', '1', '$tvdbid', '-1', $seasonnum, $EpisodeNumber)";

[mike16889]

thanx man, that stopped it from giving the that error but now its just not inserting the data, not sure why.

 

here is the table its going into if that helps.

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


CREATE TABLE IF NOT EXISTS `eppisodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `showID` int(11) NOT NULL,
  `eppname` varchar(255) NOT NULL,
  `eppdesc` longtext NOT NULL,
  `airdate` date NOT NULL,
  `format` int(11) NOT NULL,
  `tvdbid` varchar(20) NOT NULL,
  `dohave` tinyint(1) NOT NULL,
  `season` varchar(2) NOT NULL,
  `eppisode` varchar(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;