PHP & MySQL Keyword Rankings Script

Posted by Ryan Underdown
August 6th, 2009 • scripting, SEO

I’ve been working on a Google keyword rankings script that scrapes Google search engine results pages and stores the values in a mysql database. The bulk of the heavy lifting is done via php & cURL. I used some jquery to allow manual updating of rankings. Much of the scraper code was lifted from an old 5ubliminal post that I can’t seem to find anymore. The following video is a short clip of a portion of the script in action. You can test out the script here.

Files

• connect.php – stores credentials for mysql connection
• add-domains.php – allows you to add a domain into the database. This domain is then presented as an option on the index page.
• add-keywords.php – checks a domain (already inserted via add-domains.php) against a keyword. If a match is found it inserts the data into the database.
• index.php – displays all of your domains
• final.php – the actual keyword rankings page. Allows you to update rankings and see each domain’s keyword rankings on one page.
• style.css – some css styling
• postjax2.php – processes serp results and stores values in the db
• keyword_rankings.sql – database scheme – load this query via phpmyadmin to setup your mysql db
• header.php – some navigation
• ajaxproxy.php – some cross domain proxy I found to allow you to get rankings from multiple ip addresses

Google doesn’t particularly like it’s serps scraped – sometimes they will return garbage data if they suspect you are doing it. To get around this, we must bounce our requests off of multiple IP addresses. If you have a few cheap hosting accounts lying around, upload the postajax.php script to a folder accessible from the webserver. Insert your urls where these files can be found into the following section of code:

   var csurla = 'http://YOUR_URL_HERE/postjax2.php';
   var csurlb = 'http://YOUR_URL_HERE/postjax2.php';
   function cointoss(wot){
         var d= new Date().getSeconds();
         wot=(wot)? wot: [csurla, csurlb];
         d+= Math.round(Math.random()*10);
         return wot[d%2];
         }

The above example randomly chooses one of two urls to post the data to. This code could easily be changed to accommodate as many urls as you have access to. The next segment of script gathers the data from each table cell in a row of data. We then serialize the data and post it to to our ajax proxy which gets around the pesky cross domain ajax restriction. the $.Ajax command that is built in to jQuery then waits for the php proxy script to respond and then inserts the resulting data into the row we just clicked:

$(".update").click(function() {
       /* extract all the info out of the table cells */
       var selectR = $(this);
       var urlsearch = $(".urlse a").html();
       var se = $(selectR).find("td.se").html();
       var wt = $(selectR).find("td.wt").html();
       var str = $(selectR).find("td.keywords a").html();
       var ranking = $(selectR).find("td.rankingspan").html();
       var orank = $(selectR).find("td.orank").html();
       var delta = $(selectR).find("td.hclass div").html();
       var clicks = $('#tabler').find("td.clickstotal").html();
       var wttotal = $('#tabler').find("td.wttotal").html();
       var keysearch = str.replace('/%20', '+');
       var csurl = cointoss();
       var dataString = 'csurl=' + csurl + '&clicks=' + clicks + '&wttotal=' + wttotal + '&domain=' + urlsearch + '&keyword=' + keysearch + '&wt=' + wt + '&se=' + se + '&rank=' + ranking + '&orank=' + orank + '&delta=' + delta;
       $(this).html("<td colspan='9' align='center'><img src='http://PATH_TO/loading.gif'></td>");

          $.ajax({
          type: "POST",
          url: "http://PATH_TO/ajaxproxy.php",
          data: dataString,
          cache: false,
          success: function(data){
          $(selectR).html(data);
          var totalr = 0;
          var tempint = 0;
          $("td.clicks").each(function(){
          if($(this).text().length > 0){
           var tempint = parseFloat($(this).text());
           totalr += tempint;
           }})
          $("#clickstotal").html(totalr);

       $("td.wt").each(function(){
       if($(this).text().length > 0){
           var tempwt = parseFloat($(this).text()); //convert to number
           totalwt += tempwt;
           totalwt = totalwt.toFixed(2);
           }})
          }
          });
      });

Tags: Google, jquery, keyword rankings, mysql, php, scraper, scripting, serps, tools

Trackback URI.