rckman Posted December 21, 2010 Share Posted December 21, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/222340-working-with-apis-databases/ Share on other sites More sharing options...
requinix Posted December 21, 2010 Share Posted December 21, 2010 What format is the data in? What database server? Quote Link to comment https://forums.phpfreaks.com/topic/222340-working-with-apis-databases/#findComment-1150102 Share on other sites More sharing options...
rckman Posted December 21, 2010 Author Share Posted December 21, 2010 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> Quote Link to comment https://forums.phpfreaks.com/topic/222340-working-with-apis-databases/#findComment-1150107 Share on other sites More sharing options...
requinix Posted December 22, 2010 Share Posted December 22, 2010 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... Quote Link to comment https://forums.phpfreaks.com/topic/222340-working-with-apis-databases/#findComment-1150118 Share on other sites More sharing options...
rckman Posted December 22, 2010 Author Share Posted December 22, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/222340-working-with-apis-databases/#findComment-1150123 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.