<body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\07524605170\46blogName\75What\47s+New\46publishMode\75PUBLISH_MODE_BLOGSPOT\46navbarType\75BLUE\46layoutType\75CLASSIC\46searchRoot\75http://newsko.blogspot.com/search\46blogLocale\75en_US\46v\0752\46homepageUrl\75http://newsko.blogspot.com/\46vt\75-8578980419657163974', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>
   What's New[definition].  
 
    
Google
Google Web
« Home

Posts

A few weeks ago a friend asked me how my book, Pr...
LinuxWorld keynote: One Laptop Per Child
LinuxWorld: Motorola reports on its experiences wi...
Final Fantasy VII PS3 demo
Hi-speed Bluetooth will use WiMedia UWB
Panasonic heats up next-generation DVD war
US/Russian/Brazilian Crew Head to Space Station
Russian spaceship lifts off with ISS crew, Brazili...
Total solar eclipse seen in Turkey
Apple puts limits on iPod levels
 
     Archives
March 2006
April 2006
May 2006
June 2006
July 2006
 
     Links




Word of the Day

Article of the Day

This Day in History

In the News

Quotation of the Day

Exploiting Amazon Web Services via PHP and SQLite

A few weeks ago a friend asked me how my book, Pro OpenSSH, was selling on Amazon.com. I was tracking the sales by going to Amazon.com and viewing the book page to examine the sales rank. The only data displayed about history information was today's Sales Rank and Yesterday's Sales Rank, which isn't all that helpful. I decided to use PHP, SQLite, and the Amazon Web Services API to gather more useful data.

I thought it would be fun to track the sales rank over a period of time, then display a graph of the sales rank over time on a Web page.

You can gather data from Amazon in a number of ways. wget and grep could probably get the job done, but it is not elegant, nor is it encouraged by Amazon. The best way to get information is to use Amazon's application programming interface (API).

Amazon's Web Services (AWS) API offers a way to connect to the Amazon data warehouse and retrieve data about an Amazon item. To use the AWS API, you need to register with Amazon at the Amazon Web Services page. After registering, and accepting an end user license agreement (EULA), you will be given two keys: one for general access and requests, and one for verification and signing of requests. The general access key allows you to connect to the Amazon Web Services databases. The APIs are well-documented on the Amazon Web Services site.

I used the Amazon E-Commerce Service for my project to track sales rank on book titles over time. This service provides the ability to query an item via its Amazon Standard Identification Number (ASIN), International Standard Book Number (ISBN), author, artists, product name, publisher, or title, and retrieve virtually all information shown on the Amazon Web page about that item.

I started with an extremely simple PHP5 script that created the URL string you need to use with the Amazon Web service using Representational State Transfer (REST).

The PHP script is designed to run from the command line and POSTS a URL string. While you're debugging the script, you can copy the URL string and paste it into a Web browser to verify that the Web services interaction is working appropriately. The following script shows the basic setup of the PHP script to query AWS.

Access_Key';
$asin = '1590594762';
$url = 'http://webservices.amazon.com/onca/xml?Service=AWSECommerceService';
$url.= "&AWSAccessKeyId=$ACCESS_KEY";
$url.= "&Operation=ItemLookup&IdType=ASIN&ItemId=$asin";
$url.= '&ResponseGroup=Medium,OfferFull';
print "
" . $url . "
";
?>

The output from this script is a URL you can enter in your browser. The browser will return some text formatted via XML. The XML schema for this text isn't too complicated, and if you wanted to use an XSLT stylesheet, you could format the XML into HTML and have your presentation layer completed. However, my goal was not just to get information stored in Amazon's database, but to store it myself so I can track the data over time.

For this I needed a data container. I had a few options for a data container in which to store statistics from the Web service queries. A relational database made the most sense, and PHP supports several. SQLite, introduced in PHP5, seemed like a nice choice, because SQLite is simple to administer and use.

Before you begin using SQLite, take a look at your PHP information and ensure that SQLite is supported by your configuration. If it is not, you can either compile the support into PHP or download an applicable package to add support for the database. Alternatively, you could use MySQL, PostgreSQL, Oracle, or another database.

Database setup

The database schema for this Web application involves two tables: one to track the unique Amazon Item Numbers (ASIN) and the initial date they were added into the tracking system, and the other to hold the ASIN, Sales Rank from Amazon, and datestamp for when the Sales Rank was updated. The small size of the database is a design feature.

The display page that shows the graphs, sales rank, and pricing information from Amazon will be updated upon display. That means we can pull the data, such as cover images, list price, description, title, and everything else dynamically. Amazon stores that information, so we don't need to. Additionally, if the data changes, such as when price changes during a sale, the display page will have the updated information.

This is the basic schema for my SQLite database:

create table aws (
asin varchar(30),
sales_rank bigint,
active_date date);

create table item (
asin varchar(30) primary key);

Next, I added the ASIN into the item table manually via SQL. Obviously, you could write a PHP page to administer this portion of database interaction as well. After the initial script is modified to parse the XML and store the data into the database, you could set up the PHP script to run as a cron job. The script to fill the database will query the database to see what Amazon item numbers the script should be gathering statistics for. This allows for tracking of multiple items without any code changes, and thus does not lock the script into any hard-coded ASIN, as I used in the initial URL-building PHP code.

#!/usr/bin/php -q
Access_Key';
# Connect to database
$dblink = sqlite_open($DB) or die ("Couldn't connect to $DB");
# Query database to find which ASINs to search on
$sql = "SELECT asin FROM item ORDER BY asin";

$resource_set = sqlite_query($dblink, $sql);
$dt = date('Y-n-d H:i');
while ($row = sqlite_fetch_array($resource_set, SQLITE_ASSOC))
{
# Value for ASIN
$asin=$row['asin'];
# Build URL to query based on ASIN and ACCESS_KEY
$url='http://webservices.amazon.com/onca/xml?Service=AWSECommerceService';
$url.="&AWSAccessKeyId=$ACCESS_KEY";
$url.="&Operation=ItemLookup&IdType=ASIN&ItemId=$asin";
$url.='&ResponseGroup=Medium,OfferFull';
# Place the results into an XML string
$xml= file_get_contents($url);
# Use Simple XML to put results into Simple XML object (requires PHP5)
$simple_xml=simplexml_load_string($xml);
# Retrieve Sales Rank
$sales_rank=$simple_xml->Items->Item->SalesRank;
# Place Sales rank in Database
# Build SQL statement to insert values into database
$sql2 = "INSERT INTO aws (sales_rank,active_date,asin) VALUES ('$sales_rank', '$dt', '$asin')";
# Ensure Results are received
$insert_results = sqlite_query($dblink, $sql2);
{
# Check results
if($insert_results)
echo "Database $DB updated.\n";
}
}
else
{
echo "Database $DB update failed.\n";
exit(07);
}
?>

After retrieving results from AWS, the script inserts the sales rank parameter along with a date and which ASIN the information correlates to into the aws table. This table will provide the data points for displaying graphs and other presentation material about an Amazon item.

The script parses the XML shown after using a URL similar to the one seen in the first PHP listing. The XML is then loaded into a string using PHP's file_get_contents function. From there the XML is loaded into a SimpleXML data structure that is a very thorough set of associative arrays that can reference any value contained inside XML tags. To see the whole listing you can use the var_dump or print_r functionality of PHP.

After finding the pertinent information to store -- Sales Rank in this case -- we use an insert statement to create a record inside the local database. If we get an error in almost any stage of execution, we exit and return a non-zero error code.

The final step is in presentation. As stated earlier, using XSLT to parse the XML is certainly an option, but for this exercise, I will just use native PHP functionality in conjunction with SimpleXML.

I wanted to graph the sales rank over time to show the status of my book sales. To do this, I used the Image::Graph PHP Extension and Application Repository (PEAR) module.

To install Image::Graph, follow normal PEAR installation procedures. The installation was fairly easy on Fedora and Ubuntu Linux systems. The next script is the display.php page, which accesses the database and displays the sales rank in graph form. The system could be modified easily to track price or albums from your favorite artist, or other items.

Access_Key';
# Connect to database
$dblink = sqlite_open($DB) or die ("Could connect to $DB");
# Query database to find which ASINs to search on
$sql = "SELECT asin FROM item ORDER BY asin";
$resource_set = sqlite_query($dblink, $sql);
print "\n";
while ($row = sqlite_fetch_array($resource_set, SQLITE_ASSOC))
{
# Value for ASIN
$asin=$row['asin'];
# Build URL to query based on ASIN and ACCESS_KEY
$url='http://webservices.amazon.com/onca/xml?Service=AWSECommerceService';
$url.="&AWSAccessKeyId=$ACCESS_KEY";
$url.="&Operation=ItemLookup&IdType=ASIN&ItemId=$asin";
$url.='&ResponseGroup=Medium,OfferFull';
# Place the results into an XML string
$xml= file_get_contents($url);
# Use Simple XML to put results into Simple XML object
$simple_xml=simplexml_load_string($xml);
$author=$simple_xml->Items->Item->ItemAttributes->Author;
$ISBN=$simple_xml->Items->Item->ItemAttributes->ISBN;
$publisher=$simple_xml->Items->Item->ItemAttributes->Publisher;
$publication_date=$simple_xml->Items->Item->ItemAttributes->PublicationDate;
$title=$simple_xml->Items->Item->ItemAttributes->Title;
$num_pages=$simple_xml->Items->Item->ItemAttributes->NumberOfPages;
$list_price=$simple_xml->Items->Item->ItemAttributes->ListPrice->FormattedPrice;
$image=$simple_xml->Items->Item->MediumImage->URL;
$sale_price=$simple_xml->Items->Item->OfferSummary->LowestNewPrice->FormattedPrice;
$min_rank=get_rank($asin,'min');
$max_rank=get_rank($asin,'max');
# Format the output, you'd probably want a CSS sheet of some sort
print "\n
\n
\n
\n
\n
\n
\n
\n
\n
\n";
# Database chart points
$Graph =& Image_Graph::factory('graph', array(600, 400));
$Font =& $Graph->addNew('ttf_font', 'Verdana');
$Font->setSize(10);
$Graph->setFont($Font);
$Plotarea =& $Graph->addNew('plotarea');
$Dataset =& Image_Graph::factory('dataset');
# SQL to get data points
$sql="select active_date, sales_rank from aws where asin='$asin' order by active_date";
$resource_set = sqlite_query($dblink, $sql);
$i=0;
while ($row = sqlite_fetch_array($resource_set, SQLITE_ASSOC))
{
$Dataset->addPoint($i, $row['sales_rank']);
$i++;
}
$AxisX =& $Plotarea->getAxis(IMAGE_GRAPH_AXIS_X);
$AxisX->setTitle('Time');
$AxisY =& $Plotarea->getAxis(IMAGE_GRAPH_AXIS_Y);
$AxisY->setTitle('Sales Rank', 'vertical');
$Plot =& $Plotarea->addNew('smooth_line', &$Dataset);
$Graph->done(array('filename' => './output.png'));
print "\n";
}
print "
Author: $author
Title: $title
Publisher: $publisher
ISBN: $ISBN
List Price: $list_price
Sale Price: $sale_price
Page Count: $num_pages
Best Rank: $min_rank
Worst Rank: $max_rank
Publication Date: $publication_date
\n";

function get_rank($asin, $type)
{
global $dblink;
$sql = "select $type(sales_rank) as rank from aws where asin='$asin'";
$resource_set = sqlite_query($dblink, $sql);
while ($row = sqlite_fetch_array($resource_set, SQLITE_ASSOC))
{
return $row['rank'] ;
}
}
?>


This PHP script retrieves information from the database and builds a graph based on the data collected. Here is a screenshot of the page in action.

This bit of code should look similar to the first listing, in that it makes database calls to the SQLite database and interacts with Amazon via AWS. After getting the previous rankings out of the database, and displaying the information gathered via AWS, which is stored in a SimpleXML object, the script makes a call to Image::Graph, which uses the data points retrieved from the database and makes a line graph with the rank as the Y-axis and date/time as the X-axis. The script outputs the graph in .png format and displays it via HTML.

The get_rank function returns the highest or lowest rank the item has had since the database has been active. The rank is displayed when the price, picture, author, and other information is displayed.

All this work still leaves much to do to create a fully usable application, but this is a good start. Remember that by using the AWS API you can get information about other types of products from Amazon, including information from Wish Lists, Wedding Registries, and ListMania data.

Exploiting Amazon Web Services via PHP and SQLite - Thursday, April 06, 2006 -

9:02 AM

Good idea. It looks like someone has already done this though, complete with a full website: RankForest.com. It's already tracking thousands of amazon sales ranks and lets you add any item you want... has some purty graphs and charts too, pretty cool.    

Post a Comment

Enter your email address:

Delivered by FeedBurner



 


Linux Tips and Tricks - Mox Diamond - Arcane Denial - Sylvan Library
Linux Tips and Stuff - ba-zoo-ra - iBUG teks/

© 2006 What's New