Jump to content

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/222340-working-with-apis-databases/
Share on other sites

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>

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...

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  :P.

 

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.