I'm having trouble figuring out how to insert data from an array into several MySQL tables. My biggest problem is I can't figure out how to get $xtvdata->{programs}->{program}->__program_name_here__ and assign the program name as `pid` in the programs table, with the data contained in the program name array as other entries in the table.
Code:
[ignore]
My perl script so far:
#!/windows/web/server/perl/bin/perl
# use module
use XML::Simple;
#use DBI;
use Data::Dumper;
#Connect to MySQL
#my $dbh = DBI->connect("DBI:mysql:adp:127.0.0.1:50388", "root", "deskport",
# { RaiseError => 1, AutoCommit => 0 }
# ) || die "Error connecting to the database: $DBI::errstr\n";
# create object
$xmltv = new XML::Simple;
# read XML file
$xtvdata = $xmltv->XMLin("gold_sample.xml");
#used temporarily so I can see the output
print "Content-type: text/html\n\n";
print "<HTML><BODY>";
#dumps out the array so I can see the structure
print Dumper($xtvdata);
#foreach $p($xtvdata->{programs}->{program})
#{
#
#Get these variables somehow
#my $pid = $xtvdata->{programs}->{program}->__program_name_here__;
#my $title = $xtvdata->{programs}->{program}->__program_name_here__->{title};
#my $sth = $dbh->prepare('insert into tvguide_programs(pid, title)
#values $pid, $title)');
#$sth->execute();
#}
#used temporarily so I can see the output
print "</BODY></HTML>"
[/ignore]
Code:
[ignore]
Table makeup:
CREATE TABLE `tvguide_stations` (
`id` varchar(12) NOT NULL,
`name` carchar(12) NOT NULL,
`affiliate` varchar(25) NOT NULL,
`callsign` varchar(10) NOT NULL,
`fccchannelnumber` varchar(8) NOT NULL,
`name` varchar(40) NOT NULL,
`channel` varchar(40) NOT NULL,
`genremain` varchar(30) NOT NULL,
`genre0` varchar(30),
`genre1` varchar(30),
`genre2` varchar(30),
`genre3` varchar(30),
`genre4` varchar(30),
`genre5` varchar(30),
PRIMARY KEY (`id`)
);
CREATE TABLE `tvguide_lineups` (
`pid` int(11) NOT NULL auto_increment,
`device` varchar(30) NOT NULL,
`id` varchar(12) NOT NULL,
`location` varchar(28) NOT NULL,
`name` varchar(100) NOT NULL,
`postalcode` tinyint(6),
`type` varchar(20) NOT NULL,
`userLineupName` varchar(50),
PRIMARY KEY (`pid`)
);
CREATE TABLE `tvguide_programs` (
`pid` varchar(12) NOT NULL,
`title` varchar(120) NOT NULL,
`subtitle` varchar(150),
`colorcode` varchar(20),
`description` varchar(255),
`mpaaRating` int(11),
`originalAirDate` varchar(10),
`runtime` varchar(8),
`series` varchar(12),
`showtype` varchar(30),
`starRating` varchar(5),
`syndicatedEpisodeNumber` varchar(20),
`year` tinyint(4),
`advisory0` varchar(30),
`advisory1` varchar(30),
`advisory2` varchar(30),
`advisory3` varchar(30),
`advisory4` varchar(30),
`advisory5` varchar(30),
PRIMARY KEY (`pid`)
);
CREATE TABLE `tvguide_schedules` (
`pid` int(11) NOT NULL,
`sid` int(11) NOT NULL,
`duration` varchar(8) NOT NULL,
`time` varchar(20) NOT NULL,
`closecaptioned` enum('true', 'false') default 'true',
`dolby` enum('none', 'Dolby', 'Dolby Digital') default 'none',
`hdtv` enum('true', 'false') NOT NULL default 'none',
`partnumber` tinyint(2),
`totalnumber` tinyint(2),
`repeat` enum('true', 'false') default 'false',
`stereo` enum('true', 'false') default 'false',
`subtitled` enum('true', 'false') default 'false',
`tvRating` enum('TV-Y', 'TV-Y7', 'TV-G', 'TV-PG', 'TV-14', 'TV-MA', 'null') default 'null',
PRIMARY KEY (`pid`)
);
CREATE TABLE `tvguide_production_crew` (
`id` int(11) NOT NULL auto_increment,
`pid` varchar(11) NOT NULL,
`givenname` varchar(20) NOT NULL,
`surname` varchar(20) NOT NULL,
`role` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
);
[/ignore]
Code:
[ignore]
Sample XML file:
<?xml version='1.0' encoding='utf-8'?>
<xtvd from="2005-06-03T00:00:00Z" to="2005-06-04T00:00:00Z" xmlns="urn:TMSWebServices" xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xsi:schemaLocation="urn:TMSWebServices [URL unfurl="true"]http://docs.tms.tribune.com/tech/xml/schemas/tmsxtvd.xsd"[/URL] schemaVersion="1.3">
<stations>
<station id="14755">
<callSign>BLOOM</callSign>
<name>Bloomberg Television</name>
<affiliate>Satellite</affiliate>
</station>
<station id="10142">
<callSign>CNN</callSign>
<name>Cable News Network</name>
<affiliate>Satellite</affiliate>
</station>
<station id="17125">
<callSign>ENCOREW</callSign>
<name>ENCORE - Encore (Pacific)</name>
<affiliate>PAY</affiliate>
</station>
</stations>
<lineups>
<lineup id="DITV602:-" name="DIRECTV Chicago" location="Chicago" type="Satellite" postalCode="60611">
<map station="10142" channel="202" from="2000-06-26"></map>
<map station="14755" channel="353" from="2000-06-26"></map>
<map station="17125" channel="527" from="2000-06-26"></map>
</lineup>
</lineups>
<schedules>
<schedule program="SH6434270000" station="14755" time="2005-06-03T01:00:00Z" duration="PT03H00M"></schedule>
<schedule program="SH5789380000" station="14755" time="2005-06-03T04:00:00Z" duration="PT03H00M"></schedule>
<schedule program="SH6063000000" station="10142" time="2005-06-03T00:00:00Z" duration="PT01H00M" closeCaptioned="true"></schedule>
<schedule program="SH4058630000" station="10142" time="2005-06-03T02:00:00Z" duration="PT01H00M" closeCaptioned="true"></schedule>
<schedule program="MV0318610000" station="17125" time="2005-06-03T01:00:00Z" duration="PT02H00M" stereo="true" closeCaptioned="true"></schedule>
<schedule program="MV1400930000" station="17125" time="2005-06-03T03:00:00Z" duration="PT02H30M" stereo="true" closeCaptioned="true"></schedule>
<schedule program="EP0809550108" station="11097" time="2005-06-03T21:00:00Z" duration="PT01H00M" tvRating="TV-14" stereo="true" closeCaptioned="true">
<part number="2" total="2"></part>
</schedule>
</schedules>
<programs>
<program id="EP0014574180">
<title>Entertainment Tonight</title>
<description>Actors Russell Crowe and Renée Zellweger ("Cinderella Man"); celebrity splurges.</description>
<showType>Series</showType>
<series>SH001457</series>
<syndicatedEpisodeNumber>6189</syndicatedEpisodeNumber>
<originalAirDate>2005-06-02</originalAirDate>
</program>
<program id="EP0039850080">
<title>Star Trek</title>
<subtitle>The Cage</subtitle>
<description>When the Enterprise answers a distress call, Capt. Pike (Jeffrey Hunter) encounters manipulative aliens. Original series pilot.</description>
<showType>Series</showType>
<series>SH003985</series>
<colorCode>Color and B & W</colorCode>
<originalAirDate>1966-11-17</originalAirDate>
</program>
<program id="EP0191100009">
<title>The Twilight Zone</title>
<subtitle>Perchance to Dream</subtitle>
<description>A man with recurring nightmares fears sleep.</description>
<showType>Series</showType>
<series>SH019110</series>
<colorCode>B & W</colorCode>
<syndicatedEpisodeNumber>7609</syndicatedEpisodeNumber>
<originalAirDate>1959-11-27</originalAirDate>
</program>
<program id="EP2254210069">
<title>Stargate SG-1</title>
<subtitle>Crystal Skull</subtitle>
<description>Daniel vanishes during an exploration.</description>
<showType>Series</showType>
<series>SH225421</series>
<advisories>
<advisory>Adult Situations</advisory>
<advisory>Violence</advisory>
</advisories>
<syndicatedEpisodeNumber>3021</syndicatedEpisodeNumber>
<originalAirDate>2000-03-03</originalAirDate>
</program>
</programs>
<productionCrew>
<crew program="EP0039850080">
<member>
<role>Actor</role>
<givenname>William</givenname>
<surname>Shatner</surname>
</member>
<member>
<role>Actor</role>
<givenname>Leonard</givenname>
<surname>Nimoy</surname>
</member>
</crew>
<crew program="EP0191100009">
<member>
<role>Actor</role>
<givenname>Richard</givenname>
<surname>Conte</surname>
</member>
</crew>
</productionCrew>
<genres>
<programGenre program="EP0014574180">
<genre>
<class>Talk</class>
<relevance>0</relevance>
</genre>
<genre>
<class>Entertainment</class>
<relevance>1</relevance>
</genre>
</programGenre>
<programGenre program="EP0039850080">
<genre>
<class>Science Fiction</class>
<relevance>0</relevance>
</genre>
<genre>
<class>Fantasy</class>
<relevance>1</relevance>
</genre>
</programGenre>
<programGenre program="EP0191100009">
<genre>
<class>Science Fiction</class>
<relevance>0</relevance>
</genre>
<genre>
<class>Fantasy</class>
<relevance>1</relevance>
</genre>
<genre>
<class>Suspense</class>
<relevance>2</relevance>
</genre>
</programGenre>
</genres>
</xtvd>
[/ignore]
BTW, this uses data from Zap2It (labs.zap2it.com) to populate a MySQL DB with TV listings. There is a perl script available there that downloads the listings XML File for your zip code if you (anyone reading this) would like to do something with this script. Beware, the XML file you get from them is about 30MB.