Working with API's + Databases

[rckman]

Sorry if this is the wrong forum, but I couldn't figure out exactly where this topic would fit in.

 

I am working on a review website at the moment and I have hit a wall. Most of the internals are done and one of the lasts steps is to start adding products into the database to be displayed, problem is I do not have the time to add tens of thousands of products to the site. SimpleXML is not what I am going for as the script that I have built requires the database and I really do not want to rewrite the entire display setup.

 

My question is, what would work best in this case? Most API's give out an xml file, but I would need to figure out how to exactly import that into the database. Is there another way of mass adding tons of movies or other products?

 

The only other option I have is to allow people to add new items for review, but at the moment that is a worst case scenario.

[requinix]

What format is the data in? What database server?

[rckman]

Sorry, I should have clarified, I am using a MySQL Database.

 

Here is an example of the TvRage.com API XML output for the first season of 24:

 

<Show>
<name>24</name>
<totalseasons>8</totalseasons>
<showid>2445</showid>
<showlink>http://tvrage.com/24</showlink>
<started>Nov/06/2001</started>
<ended>May/24/2010</ended>
<image>http://images.tvrage.com/shows/3/2445.jpg</image>
<origin_country>US</origin_country>
<status>Canceled/Ended</status>
<classification>Scripted</classification>
<genres><genre>Action</genre><genre>Adventure</genre><genre>Drama</genre></genres>
<runtime>60</runtime>
<network country="US">FOX</network>
<airtime>12:00</airtime>
<timezone>GMT-5 -DST</timezone>
<akas><aka country="NL">24</aka><aka country="CN">24 (美國電視劇)</aka><aka country="DE">24 - Twenty Four</aka><aka attr="Przez 24 godziny" country="PL">24 godziny</aka><aka country="FR">24 heures chrono</aka><aka country="CZ">24 hodin</aka><aka country="BR">24 Horas</aka><aka attr="working title" country="US">24 Hours</aka><aka country="DK">24 timer</aka><aka country="LT">24 valandos</aka><aka country="RU">24 чаÑа</aka></akas>
<Episodelist>
<Season no="1">
<episode><epnum>1</epnum><seasonnum>01</seasonnum><prodnum>1AFF79</prodnum><airdate>2001-11-06</airdate><link>http://www.tvrage.com/24/episodes/590</link><title>12:00 A.M.-1:00 A.M.</title></episode>
<episode><epnum>2</epnum><seasonnum>02</seasonnum><prodnum>1AFF01</prodnum><airdate>2001-11-13</airdate><link>http://www.tvrage.com/24/episodes/591</link><title>1:00 A.M.-2:00 A.M.</title></episode>
<episode><epnum>3</epnum><seasonnum>03</seasonnum><prodnum>1AFF02</prodnum><airdate>2001-11-20</airdate><link>http://www.tvrage.com/24/episodes/592</link><title>2:00 A.M.-3:00 A.M.</title></episode>
<episode><epnum>4</epnum><seasonnum>04</seasonnum><prodnum>1AFF03</prodnum><airdate>2001-11-27</airdate><link>http://www.tvrage.com/24/episodes/593</link><title>3:00 A.M.-4:00 A.M.</title></episode>
<episode><epnum>5</epnum><seasonnum>05</seasonnum><prodnum>1AFF04</prodnum><airdate>2001-12-11</airdate><link>http://www.tvrage.com/24/episodes/594</link><title>4:00 A.M.-5:00 A.M.</title></episode>
<episode><epnum>6</epnum><seasonnum>06</seasonnum><prodnum>1AFF05</prodnum><airdate>2001-12-18</airdate><link>http://www.tvrage.com/24/episodes/595</link><title>5:00 A.M.-6:00 A.M.</title></episode>
<episode><epnum>7</epnum><seasonnum>07</seasonnum><prodnum>1AFF06</prodnum><airdate>2002-01-08</airdate><link>http://www.tvrage.com/24/episodes/596</link><title>6:00 A.M.-7:00 A.M.</title></episode>
<episode><epnum>8</epnum><seasonnum>08</seasonnum><prodnum>1AFF07</prodnum><airdate>2002-01-15</airdate><link>http://www.tvrage.com/24/episodes/597</link><title>7:00 A.M.-8:00 A.M.</title></episode>
<episode><epnum>9</epnum><seasonnum>09</seasonnum><prodnum>1AFF08</prodnum><airdate>2002-01-22</airdate><link>http://www.tvrage.com/24/episodes/598</link><title>8:00 A.M.-9:00 A.M.</title></episode>
<episode><epnum>10</epnum><seasonnum>10</seasonnum><prodnum>1AFF09</prodnum><airdate>2002-02-05</airdate><link>http://www.tvrage.com/24/episodes/599</link><title>9:00 A.M.-10:00 A.M.</title></episode>
<episode><epnum>11</epnum><seasonnum>11</seasonnum><prodnum>1AFF10</prodnum><airdate>2002-02-12</airdate><link>http://www.tvrage.com/24/episodes/600</link><title>10:00 A.M.-11:00 A.M.</title></episode>
<episode><epnum>12</epnum><seasonnum>12</seasonnum><prodnum>1AFF11</prodnum><airdate>2002-02-19</airdate><link>http://www.tvrage.com/24/episodes/601</link><title>11:00 A.M.-12:00 P.M.</title></episode>
<episode><epnum>13</epnum><seasonnum>13</seasonnum><prodnum>1AFF12</prodnum><airdate>2002-02-26</airdate><link>http://www.tvrage.com/24/episodes/602</link><title>12:00 P.M.-1:00 P.M.</title></episode>
<episode><epnum>14</epnum><seasonnum>14</seasonnum><prodnum>1AFF13</prodnum><airdate>2002-03-05</airdate><link>http://www.tvrage.com/24/episodes/603</link><title>1:00 P.M.-2:00 P.M.</title></episode>
<episode><epnum>15</epnum><seasonnum>15</seasonnum><prodnum>1AFF14</prodnum><airdate>2002-03-12</airdate><link>http://www.tvrage.com/24/episodes/604</link><title>2:00 P.M.-3:00 P.M.</title></episode>
<episode><epnum>16</epnum><seasonnum>16</seasonnum><prodnum>1AFF15</prodnum><airdate>2002-03-19</airdate><link>http://www.tvrage.com/24/episodes/605</link><title>3:00 P.M.-4:00 P.M.</title></episode>
<episode><epnum>17</epnum><seasonnum>17</seasonnum><prodnum>1AFF16</prodnum><airdate>2002-03-26</airdate><link>http://www.tvrage.com/24/episodes/606</link><title>4:00 P.M.-5:00 P.M.</title></episode>
<episode><epnum>18</epnum><seasonnum>18</seasonnum><prodnum>1AFF17</prodnum><airdate>2002-04-02</airdate><link>http://www.tvrage.com/24/episodes/607</link><title>5:00 P.M.-6:00 P.M.</title></episode>
<episode><epnum>19</epnum><seasonnum>19</seasonnum><prodnum>1AFF18</prodnum><airdate>2002-04-09</airdate><link>http://www.tvrage.com/24/episodes/608</link><title>6:00 P.M.-7:00 P.M.</title></episode>
<episode><epnum>20</epnum><seasonnum>20</seasonnum><prodnum>1AFF19</prodnum><airdate>2002-04-16</airdate><link>http://www.tvrage.com/24/episodes/609</link><title>7:00 P.M.-8:00 P.M.</title></episode>
<episode><epnum>21</epnum><seasonnum>21</seasonnum><prodnum>1AFF20</prodnum><airdate>2002-04-23</airdate><link>http://www.tvrage.com/24/episodes/610</link><title>8:00 P.M.-9:00 P.M.</title></episode>
<episode><epnum>22</epnum><seasonnum>22</seasonnum><prodnum>1AFF21</prodnum><airdate>2002-05-07</airdate><link>http://www.tvrage.com/24/episodes/611</link><title>9:00 P.M.-10:00 P.M.</title></episode>
<episode><epnum>23</epnum><seasonnum>23</seasonnum><prodnum>1AFF22</prodnum><airdate>2002-05-14</airdate><link>http://www.tvrage.com/24/episodes/612</link><title>10:00 P.M.-11:00 P.M.</title></episode>
<episode><epnum>24</epnum><seasonnum>24</seasonnum><prodnum>1AFF23</prodnum><airdate>2002-05-21</airdate><link>http://www.tvrage.com/24/episodes/613</link><title>11:00 P.M.-12:00 A.M.</title></episode>
</Season>

[requinix]

Unless there's something already written somewhere on the Internet (could be - worth searching around) you're probably stuck writing some quick XML-to-database script.

 

With SimpleXML it's not that hard - only limited by how much data you want (and what form it comes in as). Just hacked this out:

$xml = new SimpleXMLElement("24.xml", 0, true);
foreach ($xml->Show as $show) {
// show
$sql_show = '
	INSERT INTO shows
	(`name`, `begindate`, `enddate`, `runtime`)
	VALUES
	("%s", "%s", "%s", %d)
';
mysql_query(sprintf($sql_show,
	mysql_real_escape_string((string)$show->name),
	date("Y-m-d H:i:s", strtotime((string)$show->started)),
	date("Y-m-d H:i:s", strtotime((string)$show->ended)),
	(string)$show->runtime
));
mysql_query($sql);
$showid = mysql_insert_id();

// show aliases
$sql_alias = '
	INSERT INTO show_aliases
	(`show`, `country`, `alias`)
	VALUES
	($showid, "%s", "%s")
';
foreach ($show->akas->aka as $aka) {
	mysql_query(sprintf($sql_alias,
		mysql_real_escape_string((string)$aka["country"]),
		mysql_real_escape_string((string)$aka)
	));
}

// episodes
$sql_episodes = '
	INSERT INTO show_episodes
	(`show`, `season`, `episode_short`, `episode_long`, `date`, `title`)
	VALUES
	($showid, %d, %d, %d, "%s", "%s")
';
foreach ($show->Episodelist->Season as $season) {
	foreach ($season->episode as $episode) {
		mysql_query(sprintf($sql_episodes,
			(string)$season["no"],
			(string)$episode->seasonnum,
			(string)$season["no"] . (string)$episode->seasonnum,
			date("Y-m-d H:i:s", strtotime((string)$episode->airdate)),
			mysql_real_escape_string((string)$episode->title)
		));
	}
}
}

 

I sure hope you've asked TvRage.com about using their information for your own site - which I doubt will be just for your personal use - and they've said it's okay...

[rckman]

Thank you for your help, I will give it a try and post back to confirm. For some reason I thought that SimpleXML would actually be used in replacement to a mysql database, pulling data out of the XML files  .

 

Also, the data I really need is common knowledge and easily accessible, but just hard to get into a database with ease. TvRage offers this service for caching XML files on your server for use in your own "TV Site", see the bottom left here: http://services.tvrage.com/ , they offer an open source script for this.