Hi david,
I have this xml feed: {link saved}
You have send me some details, i tested it and it works. I like all the info in the feed published on my site and stored in a mysql database.
If there is no NEW news i like the older news (randomly) showed on my site, if possible.
You have already mysql details so let me know if you need anything more please?
regards (cheers),
Chris
Hi Chris, There are normally
Hi Chris,
There are normally 3 separate parts to this sort of project;
1) Creating the database
2) A script to import the feed into the database
and 3) A script to display the information as per your requirement
Firstly, of all the fields in each "episode" record in the feed, which ones do you want to import into MySQL? Have you created a table yet for these, or do you want help with that part...?
The other question is; do you want every page view of your site to initiate a request of the feed, or do you want to save the bandwidth and only request it every, say 100 page views, or alternatively once a day or something like that via CRON?
Cheers,
David.
Hi david, I created a
Hi david,
I created a database as you know.
I like all the episodes in the mysql but only with two or 3 images, no more. The link to the free trailer may be included but it's not strictly nessecary, it's more about the text for better position in SE.
What's your opinion about this?
I do not create a table yet, i like some help with that part.
To save bandwidth it's better to request the feed for 100 pageviews but i can also setup a cron for this.
Normally you can access d-base with typing the url : {link saved} but this is not working with the servage.net hosting. If you tell what table to insert in the d-base i will give it a try.............
Cheers,
Chris
Hi Chris, OK - to get you
Hi Chris,
OK - to get you started, I will show you how to write a db create script, and then 2 separate scripts one to import, and the other to display. Then, if you want to call the import script by cron you can set that up.
I will have a look at this now for you, so if you check back in 1 hour or so I should have something!
Cheers,
David.
Hi again, Ok - here we go!
Hi again,
Ok - here we go! Here are 3 scripts to get you started. First of all, you need to make the database - so this only needs to be done once:
create.php
<?php// don't forget to set your own database connection settings!!
$connection = mysql_connect("server","username","password");
if (!$connection) {print mysql_error();exit();}
// don't forget to set your own database name!!
mysql_select_db("database", $connection);
$sql = "
CREATE TABLE `episodes` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 255 ) NOT NULL ,
`soort` VARCHAR( 255 ) NOT NULL ,
`datum` DATE NOT NULL ,
`titel` VARCHAR( 255 ) NOT NULL ,
`trailer` VARCHAR( 255 ) NOT NULL ,
`image1` VARCHAR( 255 ) NOT NULL ,
`image2` VARCHAR( 255 ) NOT NULL ,
`image3` VARCHAR( 255 ) NOT NULL ,
`image4` VARCHAR( 255 ) NOT NULL ,
`verhaal` TEXT NOT NULL
) ENGINE = MYISAM ;
";
$result = mysql_query($sql,$connection);
if (!$result) print mysql_error($connection);
print "Done.";
?>
Next, the script to import new records. Notice how this first looks to see if this episode has been imported already....
Don't forget to change {link saved} in the following script to your XML affiliate URL for this project...!
import.php
<?php// import operations especially on large feeds can take time
// so we use set_time_limit(0) to disable time limit
set_time_limit(0);
require("MagicParser.php");
// don't forget to set your own database connection settings!!
$connection = mysql_connect("server","username","password");
if (!$connection) {print mysql_error();exit();}
// don't forget to set your own database name!!
mysql_select_db("database", $connection);
$counter = 0;
function myRecordHandler($record)
{
// must make the MySQL connection variable global!
global $connection;
global $counter;
// see if this episode is already in the database
$sql = "SELECT id FROM episodes WHERE name='".mysql_escape_string($record["EPISODE-NAME"])."'";
$result = mysql_query($sql,$connection);
if (!mysql_num_rows($result))
{
// insert the new episode
$sql = "INSERT INTO episodes SET
name='".mysql_escape_string($record["EPISODE-NAME"])."',
soort='".mysql_escape_string($record["SOORT"])."',
datum='".mysql_escape_string($record["DATUM"])."',
titel='".mysql_escape_string($record["TITEL"])."',
trailer='".mysql_escape_string($record["TRAILER/MOVIE"])."',
image1='".mysql_escape_string($record["IMAGES/PICT001.JPG"])."',
image2='".mysql_escape_string($record["IMAGES/PICT002.JPG"])."',
image3='".mysql_escape_string($record["IMAGES/PICT003.JPG"])."',
image4='".mysql_escape_string($record["IMAGES/PICT004.JPG"])."',
verhaal='".mysql_escape_string($record["VERHAAL"])."'
";
$result = mysql_query($sql,$connection);
if (!$result) {print mysql_error($connection);exit();}
// increment the global counter so we can see how many new episodes imported
$counter++;
}
}
$url = "{link saved}";
MagicParser_parse($url,"myRecordHandler","xml|FEED/EPISODE/");
print "Imported ".$counter." new records.";
?>
Finally, what you will build into your main script to display records, but here is something to get you started simply displaying all records in the database. It's best to get this working first, then you can tailor the display script depending on how you want your site to look....
display.php
<?php// don't forget to set your own database connection settings!!
$connection = mysql_connect("localhost","root","");
if (!$connection) {print mysql_error();exit();}
// don't forget to set your own database name!!
mysql_select_db("database", $connection);
print "<h1>Episodes</h1>";
$sql = "SELECT * FROM episodes";
$result = mysql_query($sql,$connection);
if (mysql_num_rows($result))
{
while($row = mysql_fetch_assoc($result))
{
print "<h2>".$row["titel"]." (".$row["name"].")</h2>";
print "<p>Soort: ".$row["soort"]."</p>";
print "<p>".$row["verhaal"]."</p>";
print "<p><a href='".$row["trailer"]."'>Click here for trailer...</a></p>";
print "<p>";
print "<img src='".$row["image1"]."' /> ";
print "<img src='".$row["image2"]."' /> ";
print "<img src='".$row["image3"]."' /> ";
print "<img src='".$row["image4"]."' /> ";
print "</p>";
print "<hr />";
}
}
else
{
print "There are no episodes to display.";
}
?>
Hopefully this should give you something to get started with....!
Cheers,
David.
Hi david, Thanks for the
Hi david,
Thanks for the quick help!!
Here's an example from what you created: {link saved} . It's working really nice!!!
Can i use the import.php as a cron?
Hi Chris, Yes - import.php
Hi Chris,
Yes - import.php should be called with CRON whenever you want to get new episodes.
This is much better than requesting the feed whenever someone comes to your site, which could put unnecessary load on the remote server.
All the best,
David.
Hi david, Thanks for
Hi david,
Thanks for helping!
The feed is about 38 episodes long en will be much longer...................is it easy to do to show or split the feed in shorter ones?
Cheers,
Chris
Hi Chris, Sure - you can
Hi Chris,
Sure - you can control the number of episodes displayed, for example the latest 10 episodes, by changing the following line:
$sql = "SELECT * FROM episodes";...to something like:
$sql = "SELECT * FROM episodes ORDER BY datum DESC limit 10";Of course you can change limit 10 for more or less episodes....!
Cheers,
David.
Hi David, Thanks for the
Hi David,
Thanks for the helping last week, i have being a little sick last/this week.
I've made some feeds myself and this is working great!!
Is it possible to show 10 episodes shuffled in a feed? (randomly showed)
Cheers,
Chris
Hi Chris, Sure - you can use
Hi Chris,
Sure - you can use the RAND() function....for example:
$sql = "SELECT * FROM episodes ORDER BY RAND() limit 10";Cheers!
David.
Thanks David, cheers, Chris
Thanks David,
cheers,
Chris