permalink

14

PHP & MySQL Keyword Rankings Script

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.


download

rank.zip

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);
           }})
          }
          });
      });

Author: Ryan Underdown

My name is Ryan Underdown, I’m the director of search engine marketing for Web-Op where I get to work on lots of interesting projects. Please follow me on twitter.

14 Comments

  1. Hi Ryan, this SERP tracking is exactly what I was looking for but the ZIP isn’t working. Can you reupload (or email me)?

  2. Hey Ryan, script looks great, however I downloaded the script and as Samual mentioned the keyword_rankings.sql file is missing?
    Would it be possible to get a copy?
    Thanks

  3. Hello Ryan,

    thank you for this great script!! Exactly what I was looking for. The only thing missing is the keyword_rankings.sql file. Could you please send me this file?? Thanks in advance.

    Greetings, Kai

  4. Hey,

    If I can get this working, I will be forever in your debt.

    Can you send me the details, or post a section describing what tables and fields etc are needed. I’m assuming you might also have a .sql file. Could you e-mail me this?

    Once again – Awesome work, many thanks

    @mgpwr

  5. This stuff is WAY over my head. That said, as a small business owner that depends on online sales to stay in business, I was wondering if you could recommend someone (or perhaps a site/forum/community) where I might be able to find someone willing to the stuff that I’m not smart enough to figure how to do myself for a reasonable rate. By reasonable, I’m able to offer market value for the work, the issue is that with my lack of experience in matters such as programming, I really have no concept of what that is.

    Any advice and/or suggestions would be greatly appreciated.

Leave a Reply

Required fields are marked *.