UCFirefly - Poor man's Heatmap Using jQuery (javascript), php, and MySQL

Building a basic heatmap is not that hard if you have the resources.

Step 1 - Monitor the mouse movement

Place the code below in your head section. Remember your jquery include.

$(document).ready(function()
{
   $().mousemove(function(e)
   {

       $('.coords').html("X Axis : " + e.pageX + " | Y Axis " + e.pageY );

   });
});

And in your html somewhere:

<p class='coords'>
This gets replaced by your mouse info
</p>

This should record your basic mouse information. You should keep in mind that different mouse resolutions will resolve things on your page differently, especially as right align or centering are used. A fixed size, left aligned page pretty much ensures consistency of data, but we prefer to have things pretty, don't we?

Step 2 - Record an array of events

Displaying the information is nice, but ideally, we want to hold onto a certain number of coodinated before we call home about it. We also would like to keep track of the time of visit and the timezone. We could easily grab window resolution as well, but this is a good enough start.

var XArray = new Array();
var YArray = new Array();
var TArray = new Array();
var Xtext = '';
var Ytext = '';
var Ttext = '';

$(document).ready(function()
{
  $().mousemove(function(e)
  {
    $('.coords').html("X Axis : " + e.pageX + " | Y Axis " + e.pageY + " " + XArray.length + " " + new Date().getTime() + " " + (new Date().getTimezoneOffset()/60)
);

    $('.arr').html('y(' + YArray.length + '):' + Ytext);

    if (e.pageX!=0 && e.pageY != 0)
    {
      XArray[XArray.length] = e.pageX;
      YArray[YArray.length] = e.pageY;
      TArray[TArray.length] = new Date().getTime();
   }
  });
});

Testing like this ensures to make sure it does work before proceeding. After putting the above code into your head document replacing the previous code, add another div to your html for more testing.

<p class='arr'>
array displays here....here
</p>

This test is doing something very nasty, and that is reinstantiating the Ytext everytime the mouse moves. Just assure yourself that it's okay for testing and move on.

Step 3 - When your event array is reasonably full, call ajax, then purge the array

This is where is all starts looking kinda cool...from the collection side of things.

var XArray = new Array();
var YArray = new Array();
var TArray = new Array();
var Xtext = '';
var Ytext = '';
var Ttext = '';

$(document).ready(function()
{
  $().mousemove(function(e)
  {

    $('.coords').html("X Axis : " + e.pageX + " | Y Axis " + e.pageY + " " + XArray.length + " " + new Date().getTime() + " " + (new Date().getTimezoneOffset()/60)
);

    if (XArray.length%250==0 && XArray.length!=0)
    {
      Xtext = '';
      for (XX in XArray)
      {
        Xtext = Xtext + XArray[XX] + ",";
      }
      Ytext = '';
      for (YY in YArray)
      {
        Ytext = Ytext + YArray[YY] + ",";
      }

      Ttext = '';
      for (TT in TArray)
      {
        Ttext = Ttext + TArray[TT] + ",";
      }

      $.post("insertion.php", { y: Ytext, x: Xtext , t: Ttext, tz : (new Date().getTimezoneOffset()/60) } );
XArray.length = YArray.length = TArray.length = 0;
    }

    $('.arr').html('y(' + YArray.length + '):' + Ytext);

    if (e.pageX!=0 && e.pageY != 0)
    {
      XArray[XArray.length] = e.pageX;
      YArray[YArray.length] = e.pageY;
      TArray[TArray.length] = new Date().getTime();
    }
  });
});

The "arr" div should be showing the updates as soon as the array reaches 250 now. It is calling a page called insertion which we have not created yet. I guess we'd better get to that.

Step 4 - Create your ajax page and database table

The ajax page, which I have called "insertion.php" will look something like this:

<?php
session_start();
include 'inc/db.php';

if ( isset($_REQUEST['x']) || isset($_REQUEST['y']) )
{

  $x=split(',',$_REQUEST['x']);
  $y=split(',',$_REQUEST['y']);
  $t=split(',',$_REQUEST['t']);
  $tz=$_REQUEST['tz'];

  $total = count($x);

  for($i=0;$i<$total;$i++)
  {
    //echo "{$x[$i]} {$i} {$total}<br/>";
    $query = sprintf("INSERT INTO motion (`x`,`y`,`t`,`tz`,`ip`,`session`)
VALUES('%s','%s','%s','%s','%s','%s')",
mysql_real_escape_string($x[$i]),
mysql_real_escape_string($y[$i]),
mysql_real_escape_string($t[$i]),
mysql_real_escape_string($tz),
mysql_real_escape_string($_SERVER['REMOTE_ADDR']),
mysql_real_escape_string(session_id()) ); // No cookie being sent yet...
//echo $query;
    mysql_query($query); //or die("Not quite to par really.");
}
}

?>

Please note a few things:

You need to connect to mysql somehow...I do that with an include
This is using $_REQUEST, which accepts post and get...not very secure, but great for ease of testing.
After the (database and) table is created in mysql, use get to test with (ex: http://.../insertion.php?x=1,2,3,4,5) to make sure your db is good and working

CREATE TABLE IF NOT EXISTS `motion` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`ip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`session` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`x` int(11) NOT NULL,
`y` int(11) NOT NULL,
`t` bigint(20) NOT NULL,
`tz` tinyint(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

id is an autoincrement field, and probably is unneeded as we could create a 2 field key on the session and the time (t) which is given to us in milliseconds. It might be nice to normalize the data some, but remember that this is an imput table...it's receiving raw data and normalizing will make things a little slower. The timezone (which should be rounded on entry) is already divided down to hours to make storage a little less of a headache. It should be an integer 99% of the time anyway.

Step 5 - Create method to display heat information with

Add a style to the local stylesheet

.pixel {
  position:absolute;
  width: 2 px;
  height: 2 px;
  font-size: 1 px;
}

This should allow us to make div's show up small enough to give an approximate representation of where the mouse has been.

<div class='pixel' style='background: rgba(255, 0, 0, 0.5); left: 0px; top: 0px; '> </div>

The div's will look something like the one above. The exceptions are that the top and left need to be different as well as the amount of red in the rbg form of the color.

Step 6 - Create a db report to display the information gathered

SELECT IF(
(count(*)/(SELECT count(*) FROM `motion`)*255000) > 255
,255
,CEIL(count(*)/(SELECT count(*) FROM `motion`)*255000)
) heat , x, y
FROM `motion`
GROUP BY x,y

This looks a might confusing. What we are doing is grouping all the entries together where the x and y are the same and adding those together. After we do that, we divide by the total number of items in the table to get some sense of perspective...otherwise everything will look 'high-heat' after long enough. In general, I was observing that normally a pixel does not get even 0.1% of the total for a page...and if it gets anywhere near that, it is very 'high-heat'. We multiply by 255000, which is attempting to make those 0.1% pixels high heat. If they are hotter than that, we leave the number to 255 at largest. Ceiling is taken as a quick form of rounding so that we have integers for display.

Step 7 - Write php to integrate heat display with dataset

Add to the top of your page:

<?php

session_start();
include 'inc/db.php';

// .1% is arbitrary...if any one 'point' is getting .1% of the attention, it should be red hot
// So .1% gets the hottest value here, and anything else is the same or lower

$result = mysql_query("SELECT IF(
(count(*)/(SELECT count(*) FROM `motion`)*255000) > 255
,255
,CEIL(count(*)/(SELECT count(*) FROM `motion`)*255000)
) heat , x, y
FROM `motion`
GROUP BY x,y");

?>

Add to somwhere inside of the html of your page:

<?php
// $sql divs here
while($row = mysql_fetch_assoc($result))
{
echo "<div class='pixel' style='background: rgba({$row['heat']}, 0, 0, 0.5); left: {$row['x']}px; top: {$row['y']}px; '> </div>";
}
?>

Voila, you are done. You can pick out the troubleshooting peices of this code if you like. There are many improvements which could stand to be made, but this is a good basic prototype.

Reload this page to see how your mouse has been moving along it.

Update: after some testing the, I discovered the color and the transparency with rgba worked only for CSS complient browsers. For deviant hell hounds, like IE, you have to insert extra things which are in this page but not the example code. Also the mouse pickup in IE is slower...mostly because it is hoggish I am imagining.

Thanks to:
http://css.dzone.com/tips/jquery-tracking-the-position-o - jquery mouse code
http://www.quirksmode.org/css/opacity.html - Transparency compatibility issues
http://docs.jquery.com/ - jquery docs

Poor man's Heatmap Using jquery (javascript), php, and MySQL