$link = mysql_connect($databaseHost, $databaseUser, $databasePassword); //Create connection to database
This section of code checks if the database name specified above already exists on your mysql server and if it doesn’t create it, along with a news articles table:
//If Database doesn't exist create it
if(@mysql_select_db($databaseName)==false){
$query = "CREATE DATABASE $databaseName";
if(!mysql_query($query))die("Error in query: $query"); //Exit script if database can't be created
//Create a news articles table in the database
$query = "CREATE TABLE $databaseName.`NewsArticles` (
`id` int(10) unsigned NOT NULL,
`headline` varchar(255) NOT NULL,
`text` text NOT NULL,
`publishedDate` datetime NOT NULL,
`photoURL` text NULL,
PRIMARY KEY (`id`))
ENGINE=InnoDB DEFAULT CHARSET=latin1;";
if(!mysql_query($query))die("Error in query: $query"); //Exit script if table can't be created
mysql_select_db($databaseName);
}
The final section of code is used to iterate through all the news items returned for your feed and save them into your database:
$feed = new ApiHandler($API_Key, $baseUrl); //create a new feed object using the Api_Key
$newsList = $feed->getNewsHTML(); //return an array of the latest news items from your feed in HTML format
foreach($newsList as $news){
/* @var $news NewsItem */
//Prepare unsafe data for database
$headline = mysql_escape_string($news->getHeadline());
$text = mysql_escape_string($news->getText());
//process photo select default photo and return largeURL for it
$photos = $news->getPhotos();
$photoURL = mysql_escape_string($photos[0]->getLarge()->getUrl());
//insert article into database, but ignore it if it already exists
$query = "INSERT IGNORE INTO NewsArticles
Values(".$news->getId().",'" . $headline ."','" . $text ."',
'" . $news->getPublishDate() . "','" . $photoURL ."')";
if(!mysql_query($query))echo("Error in query: $query");
}
mysql_close($link); //Close Database connection
echo "Operation Completed";
[ Advanced Example ]
Before you begin it is necessary to create a MySQL databse with all the necessary tables and feeds you wish to record. Please refer to Advanced database structure.
[ Using the PHP API to Save into your Newly Created Database ]
Below the script has been broken into several pieces in order to explain more easily. But should be copied into 1 script.
First setion of the code is used to configure your feed settings these are specifically your API_Key and base url which are issued to you in your welcome pack. It is important to replace the example values given below with the details you’ve been given.
<html>
<head></head>
<body>
<?php
include_once 'SampleFeedAPI/ApiHandler.php';
$success = true; //initialise successful operation boolean
$API_Key = "your_api_key"; //insert api key into this variable
$baseUrl = "your_api_base_url"; //insert base into this variable
The next section requires you to configure your database settings as above the example settings need to replaced with values appropriate to your MySQL configuration, the databaseName however should remain the same unless you have modified this name in the initial Advanced Database Creation Script.
//Database Configuration
$databaseHost = "yoursqlserver";
$databaseUser = "root";
$databasePassword = "";
$databaseName = "AdvancedSampleDB";
$link = mysql_connect($databaseHost, $databaseUser, $databasePassword);
mysql_select_db($databaseName) or die("Cannot find Database");
Getting NewsItems from your Feed
The portion of code deals with returning an array of newsItems using the sample API. The ApiHandler class is instantiated using the API_Key and baseUrl values provided above, if the values are incorrect an exception will be thrown and the script will exit with a message detailing the exception.
$niList = array();
try{
$feed = new ApiHandler($API_Key, $baseUrl); //create a new feed object using the Api_Key
$niList = $feed->getNewsHTML(); //return an array of the latest news items from your feed in HTML format
}
catch(Exception $e){
die($e);
}
Processing each news item and preparing values for the database
Each of the variables associated with a newsItem a prepared for the mysql query by escaping any special characters and making them safe for the query. We also intialise the updated and inserted counters to 0, which we use to track how many articles are updated and how many articles are inserted. We catch any exceptions thrown for each newsitem and skip the processing of that specific item as highligted in the catch block.
//initialize counters
$updated = 0;
$inserted = 0;
// process the news
foreach($niList as $news){
/* @var $news NewsItem */
try{
$encoding = mysql_real_escape_string($news->getEncoding());
$headline = mysql_real_escape_string($news->getHeadline());
$id = mysql_real_escape_string($news->getId());
$publishDate = mysql_real_escape_string($news->getPublishDate());
$createdDate = mysql_real_escape_string($news->getCreatedDate());
$lastModifiedDate = mysql_real_escape_string($news->getLastModifiedDate());
$extract = mysql_real_escape_string($news->getExtract());
$text = mysql_real_escape_string($news->getText());
$byline = mysql_real_escape_string($news->getByLine());
$source = mysql_real_escape_string($news->getSource());
$tweetText = mysql_real_escape_string($news->getTweetText());
$source = mysql_real_escape_string($news->getSource());
$state = mysql_real_escape_string($news->getState());
$clientQuote = mysql_real_escape_string($news->getClientQuote());
$htmlTitle = mysql_real_escape_string($news->getHtmlTitle());
$htmlMetaDescription = mysql_real_escape_string($news->getHtmlMetaDescription());
$htmlMetaKeywords = mysql_real_escape_string($news->getHtmlMetaKeywords());
$htmlMetaLanguage = mysql_real_escape_string($news->getHtmlMetaLanguage());
$tags = mysql_real_escape_string($news->getTags());
$priority = is_int($news->getPriority())? $news->getPriority() : "NULL";
$format = mysql_real_escape_string($news->getFormat());;
$categories = $news->getCategories();
$photos = $news->getPhotos();
$comments = $news->getComments();
}
catch (Exception $e){
echo $e;
continue;
}
Here a query is executed to check if a newsarticle already exists in the database with the fetched id, if it does then we process an UPDATE query inside the if block otherwise we skip the entire if block and go to the else block where we execute an INSERT query instead. If the query is successful the update counter is increased by 1.
//Check if news article already exist in database if it does run an update query else insert query
$query = "Select * FROM NewsArticles WHERE id = " . $id;
if(mysql_num_rows(mysql_query($query))){
$query = "UPDATE NewsArticles
SET headline = '" . $headline ."', extract = '" . $extract ."',
encoding = '" . $encoding ."', text = '" . $text ."', publishDate = '".$publishDate."',
byline = '" . $byline ."', tweetText = '" . $tweetText ."', source = '" . $source ."',
state = '" . $state ."', clientQuote = '" . $clientQuote ."',
createdDate = '".$createdDate."', lastModifiedDate ='".$lastModifiedDate."',
htmlTitle = '" . $htmlTitle ."', htmlMetaDescription = '" . $htmlMetaDescription ."',
htmlMetaKeywords = '" . $htmlMetaKeywords ."', htmlMetaLangauge = '" . $htmlMetaLanguage ."',
tags = '" . $tags."', priority = " . $priority ."
, format = '" . $format ."'
WHERE id = $id ";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
else $updated++;
Part of the update also involves deleting associated photos and comments for reasons of simplicity as they are re-added in later method calls. With respect to categories however, only the relationships with news articles are deleted, this as with the aformentioned is re-added in later method calls.
//Check if photos already exist in database if they do delete them as they will be re-added in the initialisePhotoQuery method
$subQuery = "(SELECT newsPhotoID FROM NewsArticlePhotos
WHERE newsArticleID =". $id .")";
if(mysql_num_rows(mysql_query($subQuery))){
$query = "DELETE FROM NewsPhotos
WHERE id IN ".$subQuery;
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
}
//Delete all category relationships with this article as will be re-added in initialeCategoryQuery method
$query = "DELETE FROM NewsArticleCategories
WHERE newsArticleID =". $id;
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
//Delete all existing comments for this article as will be re-added in intiliaseCommentsQuery
$subQuery = "(SELECT newsCommentID FROM NewsArticleComments
WHERE newsArticleID =". $id .")";
if(mysql_num_rows(mysql_query($subQuery))){
$query = "DELETE FROM NewsComments
WHERE id IN ".$subQuery;
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
}
}
Processing each news item and preparing values for the database
If an article does not exist then this portion of code inside the else block will be processed, which is simply a mysql insert statement if successful the insert counter is increased by 1 if not the query is echoed to the page.
else
{
$query = "INSERT INTO NewsArticles
Values(".$id.",'" . $headline ."','" . $extract ."',
'" . $encoding ."','" . $text ."','".$publishDate."',
'" . $byline."','" . $tweetText ."','" . $source."',
'" . $state ."','" . $clientQuote."','".$createdDate."',
'".$lastModifiedDate."','" . $htmlTitle."','" . $htmlMetaDescription."',
'" . $htmlMetaKeywords ."','" . $htmlMetaLanguage ."','" . $tags."',
" . $priority .",'".$format."')";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
else $inserted++;
}
Processing each news item and preparing values for the database
The final operations inside the foreach loop apply to both UPDATES and INSERTS and are calls to 3 methods which handle the processing of photos, categories and comments to the database, each method is discussed in more detail further down the page:
// Perform the photo category and comments update to the database
initialisePhotoQuery($photos, $id);
initialiseCategoryQuery($categories, $id);
initialiseCommentsQuery($comments, $id);
}
Checking if operation was successful and closing database connection
This portion of code is used display the number of articles successfully UPDATED and INSERTED and any errors if there were any:
if ($success)echo "Operation completed successfully: $updated Articles UPDATED, $inserted Articles INSERTED";
else echo "There was a problem saving all your news articles to the database, See Errors Above - $updated Articles UPDATED, $inserted Articles INSERTED";
mysql_close($link);
This method is used to add all photo information associated with an article into the photos table and also establishes article and photo relationships in the relevant database table as well all variable values associated with a photo are safely escaped for the query as well.
//Add photos and their relationships to news articles into database
function initialisePhotoQuery($photoArray, $id){
$query = "";
foreach ($photoArray as $Photo){
/* @var $Photo Photo */
$alt = mysql_real_escape_string($Photo->getAlt());
$photoID = mysql_real_escape_string($Photo->getId());
$largeUrl = ($Photo->getLarge()->getUrl() != "NULL")?"'".mysql_real_escape_string($Photo->getLarge()->getUrl())."'":"NULL";
$largeHeight = mysql_real_escape_string($Photo->getLarge()->getHeight());
$largeWidth = mysql_real_escape_string($Photo->getLarge()->getWidth());
$thumbUrl = ($Photo->getThumb()->getUrl()!= "NULL")?"'".mysql_real_escape_string($Photo->getThumb()->getUrl())."'":"NULL";
$thumbHeight = mysql_real_escape_string($Photo->getThumb()->getHeight());
$thumbWidth = mysql_real_escape_string($Photo->getThumb()->getWidth());
$hiResUrl = ($Photo->getHiRes()->getUrl()!= "NULL")?"'".mysql_real_escape_string($Photo->getHiRes()->getUrl())."'":"NULL";
$hiResHeight = mysql_real_escape_string($Photo->getHiRes()->getHeight());
$hiResWidth = mysql_real_escape_string($Photo->getHiRes()->getWidth());
$customUrl = ($Photo->getCustom()->getUrl()!= "NULL")?"'".mysql_real_escape_string($Photo->getCustom()->getUrl())."'":"NULL";
$customHeight = mysql_real_escape_string($Photo->getCustom()->getHeight());
$customWidth = mysql_real_escape_string($Photo->getCustom()->getWidth());
$orientation = mysql_real_escape_string($Photo->getOrientation());
$query = "SELECT id FROM NewsPhotos WHERE id =" . $photoID;
if(!mysql_num_rows(mysql_query($query))){
$query = "INSERT INTO NewsPhotos Values(".$photoID.",'" . $alt ."','" . $orientation ."',
" . $thumbWidth ."," . $thumbHeight ."," . $thumbUrl . "," . $largeWidth .",
" . $largeHeight ."," . $largeUrl . "," . $hiResWidth . "," . $hiResHeight . ",
" . $hiResUrl . "," . $customWidth . "," . $customHeight . "," . $customUrl . ")";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
}
$query = "INSERT INTO NewsArticlePhotos(newsArticleID, newsPhotoID) VALUES (" . $id . ",". $photoID . ")";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
}
}
The Categories Processing Method
This method is used to add all category information associated with an article into the categories table, if it doesn’t already exist, furthermore it establishes article and category relationships in the relevant database table as well all variable values associated with a category are safely escaped for the query as well.
//Add categories and their relationships to news articles into database
function initialiseCategoryQuery($categoryArray, $id){
$query = "";
foreach ($categoryArray as $cat){
/* @var $cat Category */
$categoryID = mysql_real_escape_string($cat->getID());
$categoryName = mysql_real_escape_string($cat->getName());
$query = "Select * FROM NewsCategories WHERE id = " . $categoryID;
if(!mysql_num_rows(mysql_query($query))){
$query = "INSERT INTO NewsCategories Values(".$categoryID.",'" . $categoryName ."')";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
}
$query = "INSERT INTO NewsArticleCategories(newsArticleID, newsCategoryID) VALUES (" . $id . ",". $categoryID . ")";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
}
}
The Comments Processing Method
This method is used to add all comments information associated with an article into the comments table and also establishes article and comment relationships in the relevant database table as well. Additionally all variable values associated with a comment are safely escaped for the query.
//Add comments and their relationships to news articles into database
function initialiseCommentsQuery($commentsArray, $id){
foreach ($commentsArray as $comment){
/* @var $comment Comment */
$commentID = mysql_real_escape_string($comment->getID());
$commentUser = mysql_real_escape_string($comment->getUser());
$commentPostDate = mysql_real_escape_string($comment->getPostDate());
$commentLocation = mysql_real_escape_string($comment->getLocation());
$query = "INSERT INTO NewsComments Values(".$commentID.",'" . $commentUser ."','".$commentPostDate."',
'" . $commentLocation ."')";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
$query = "INSERT INTO NewsComments(newsArticleID, newsCommentid) VALUES (" . $id . ",". $commentID . ")";
if(!mysql_query($query)){
global $success;
$success = false;
echo("Incorrect query: " . $query . "<br/><br/>");
}
}
}
?>
</body>
</html>