Friday, July 1, 2011

10:50 AM

Crawler, spider, bot, or whatever you want to call it, is a program that automatically gets and processes data from sites, for many uses.Google, for example, indexes and ranks pages automatically via powerful spiders, crawlers and bots. We have also link checkers, HTML validators, automated optimizations, and web spies. Yeah, web spies. This is what we will be doing now.

Actually I don’t know if this is a common term, or if its ever been used before, but I think it perfectly describes this kind of application. The main goal here is to create a software that monitors the prices of your competitors so you can always be up to date with market changes.

You might think “Well, it is useless to me. You know, I’m a freelancer, I don’t have to deal with this ‘price comparison’ thing.” Don’t worry, you are right. But you may have customers that have a lot of competitors they want to watch closely. So you can always offer this as a “plus” service (feel free to charge for it, I’ll be glad to know that), and learn a little about this process.

So, let’s rock!


PHP Server with linux – We need to use crontab here, so it is better to get a good online server
MYSQL – We will store data with it, so you will need a database

Basic crawling

We will start by trying a basic crawling function: get some data. Let’s say that I sell shoes, and Zappos is my competitor (just dreaming about it). The first product I want to monitor is a beautiful pair of  Nike Free Run+. We will use now fopen to open the page, fgets to read each line of the page and feof to check when we need to finish the reading. At this time, you need to have fopen enabled in your server (you can check it via phpinfo). Our first piece of code will be:

  if(!$fp = fopen("" ,"r" ))
      return false;

  } //our fopen is right, so let's go
  $content = "";
  while(!feof($fp)) { //while it is not the last line, we will add the current line to our $content
  $content .= fgets($fp, 1024);
 fclose($fp); //we are done here, don't need the main source anymore

At this point, if you echo the $content you will notice that it has all page contents without any CSS or JS, because on zappos site they are all with relative paths.

Now we have the content, we need to process the product price.

How do you know the difference between price and  other ordinary data in our page? Well, it is easy to notice that all prices must have a “$” before them, so what we will do is get all data and run a Regular Expression to see which prices where we have a dollar sign,  we have on that page.

But our regular expression will match every price on the page. Since Zappos is a good friend of spies, it has made the “official” price as the first, always. The others are just used in JavaScript, so we can ignore them.

Our REGEX and price output will be something like this:

//our fopen, fgets here
//our magic regex here
preg_match_all("/([$][0-9]*[,]*[.][0-9]{2})/", $content, $prices, PREG_SET_ORDER);
echo $prices[0][0]."<br />";

Wow, we now have the price. Don’t forget the other prices, we will need them if Zappos changes something in their site.

Save data in MYSQL

Let’s prepare our DB to receive this data. Let’s create a table called zappos. Inside of it we will have four columns:

ID - Primary key on this table
Date - When data was stored. It’s good to store this so you can do some reports.
Value - Value that you’ve found
Other_Values - Values that aren’t what you want, but it’s important to store them so if the site owner changes the code you have a “backup” of the possible values

In my phpmyadmin I’ve created a database called spy, and inside it my table zappos, this way:

 `Date` date NOT NULL,
 `Value` float NOT NULL,
 `Other_Values` char(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

Once you’ve created your table, we will start adding some data. So we will need to do a mysql connect in our PHP and prepare our prices to be saved.

Since all our data is not perfect floats, we need to prepare it so we will have just numbers and a dot.
To connect in our db we will use mysql_connect, and after we will use mysql_select_db to select “spy” and then we can do our mysql_query to save or get our data.

//preparing to save all other prices that isn't our "official" price
  $otherValues = "";
  foreach ($prices as $price) {
      $otherValues .= str_replace( array("$", ",", " "), '', $price[0]); //we need to save it as "float" value, without string stuff like spaces, commas and anything else you have just remove here
      $otherValues .= ","; //so we can separate each value via explode when we need
//if someday Zappos changes his order (or you change the site you want to spy), just change here
  $mainPrice = str_replace( array("$", ",", " "), '', $prices[0][0]);
//lets save our date in format YYYY-MM-DD
  $date = date('Y\-m\-d');
  $dbhost  = 'localhost';
  $dbuser  = 'root';
 $dbpass  = '';
  $dbname  = "spy";
  $dbtable = "zappos";
  $conn = mysql_connect($dbhost, $dbuser, $dbpass)
      or die ('Error connecting to mysql');
      echo "<br />Connected to MySQL<br />";

     $selected = mysql_select_db($dbname)
          or die( mysql_error() );
          echo "Connected to Database<br />";

          //save data

          $insert = mysql_query("
                      INSERT INTO `$dbname`.`$dbtable` (
                          `ID` ,
                            `Date` ,
                          `Value` ,
                      VALUES (
                          NULL , '$date', '$mainPrice', '$otherValues'

          //get data

          $results = mysql_query("SELECT * FROM $dbtable");
//all data comes as MYSQL resources, so we need to prepare it to be shown
  while($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
      echo "ID :{$row['ID']} " .
           "Date : {$row['Date']} " .
           "Value : {$row['Value']}";
      echo "<br />";

Smarter spy with Crontab

Well, with crontab we can schedule some tasks in our (linux) system so it runs automatically. It is useful for backup routines, site optimizing routines and many more things that you just don’t want to do manually.

Since our crawler needs some fresh data, we will create a cron job that runs every day at 1am. On net.tuts+ we have a really good tutorial on how to schedule tasks with cron, so if you aren’t too familiar with it, feel free to check it out.

In short, we have command lines that we could use for it, (second is my favorite):

#here we load php and get the physical address of the file
#0 2 * * * says that it should run in minute zero, hour two, any day of month, any month and any day of week
0 2 * * * /usr/bin/php /www/virtual/username/cron.php > /dev/null 2>&1
#my favorite, with wget the page is processed as it were loaded in a common browser
0 2 * * * wget http://whereismycronjob/cron.php

Let’s do some pretty charts

If you are planning to use this data, just a db record won’t be too useful. So after all this work we need to present it in a sexier way.

Almost all our jobs here will be done by the gvChart jQuery plugin. It gets all our data from tables and make some cool charts out of it. What we have to do actually is print our results as a table, so it can be used by gvChart. Our code this time will be (download our demo for more info!):

  $dbhost  = 'localhost';
  $dbuser  = 'root';
  $dbpass  = '';
  $dbname  = "spy";
  $dbtable = "zappos";
  $conn = mysql_connect($dbhost, $dbuser, $dbpass)
      or die ('Error connecting to mysql');
      $selected = mysql_select_db($dbname)
          or die( mysql_error() );

          //get data

          $results = mysql_query("SELECT * FROM $dbtable ORDER BY `ID` DESC LIMIT 15");
          $dates  = array();
          $values = array();
          while($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
              $dates[] = "{$row['Date']}";
              $values[] = "{$row['Value']}";
          echo "<table id='real'>";
              echo "<caption>Real Prices on</caption>";
              echo "<thead>";
                  echo "<tr>";
                      echo "<th></th>";
                      foreach($dates as $date) {
                          $date = explode('-', $date);
                          echo "<th>" . $date[2] . "</th>";
                  echo "</tr>";
              echo "</thead>";
              echo "<tbody>";
                  echo "<tr>";
                      echo "<th>" . $date[0] . "-" . $date[1] . "</th>";
                      foreach($values as $value) {
                          echo "<td>" . $value . "</td>";
                  echo "</tr>";
              echo "</tbody>";