Mysql function to get querystring parameter of url

Posted by & filed under Code, Development, SQL, Work.

Not suprisingly, most google results only returned things for doing this within some kind of PHP application.  Probably because nobody belives in MySQL as a data warehouse, or a place to parse strings… In the do with what you have environment of a startup, I do not have that luxury.  This function so far works decent, it’s not extremely robust, but it does a job.


CREATE FUNCTION `fn_getparam`(param varchar(55), url varchar(2048)) RETURNS varchar(2048) CHARSET latin1 COLLATE latin1_general_cs

declare val varchar(2048);
declare _param varchar(60) DEFAULT CONCAT(param,'=');

	when locate(concat('&',_param), url) > 0
		then right(url, length(url) - (locate(concat('&',_param),url)+length(concat('&',_param))-1))
	when locate(concat('?',_param), url) > 0
		then right(url, length(url) - (locate(concat('?',_param),url)+length(concat('?',_param))-1))
	when locate(concat('#',_param), url) > 0
		then right(url, length(url) - (locate(concat('#',_param),url)+length(concat('#',_param))-1))
	when locate(_param,url) > 0
		then right(url, length(url) - (locate(_param,url)+length(_param)-1) )
else null
into val;

set val = replace(replace(left(val, locate('&',concat(val,'&'))-1),'%20',' '),'+',' ');


Amazon Associates Widget WordPress Plugin

Posted by & filed under Code, Development.

I’m a die-hard Amazon fanatic, and finally looked into Amazon Associates.  As you probably didn’t notice, since you’re a new visitor – I’ve added some links in posts, as well as a nifty widget in my sidebar.  I was surprised to only find a few widgets that offered to do more than just insert specific product links into posts, and I really liked the default Amazon widgets.  The easiest to templatize, and that required the least Amazon API work were those delivered through IFRAMEs, which ended up being the two I most liked: Recommended Products, and Omakase.

I’ve hacked together a very rough plugin, with a sidebar widget to allow you to:

  • Include Amazon Associates widget in the sidebar
  • Multi-post can be configured for Recommended Products from a chosen cateory, or default to Omakase.
  • Each post can be configured with a specific product category, and subcategory or keywords to display recommend products from.

It’s not pretty, but gives you dead simple contextual advertising as to what YOU deem your post to be about, with more flexibility using keywords.

Right now you can grab this and install manually from:

I’d like to wait to submit it to WordPress until I get the first two off the TODO list:

  • Use the Widget API model and extend the WP_Widget class
  • Configurable display types for sidebar
  • Configure colors
  • Add more than one widget
  • Add widgets to posts (in content or as a footer)
  • Add additional widget types
  • Support regions other than .com

Install 8GB of RAM in Intel PM965 Chipset

Posted by & filed under DIY.

In my quest to keep upgrading I was greatly disappointed to see the specs for my 2007 HP Pavillion dv2500 laptop was a maximum of 4GB of RAM.  I had already upgraded it to that much, and need to upgrade another laptop in the house – nothing I love more than the opportunity to kill two birds with one stone.  Through research I learned that the limit is actually based on your processor chipset, mine an Intel PM965.  However both the specs for the chipset, and Crucial still tell me 4GB max.  I didn’t believe it and enough of the Googles told me that: chances are it will support 8GB.

Proof is in the picture, slapped them in, started up, and I’ve got 8GB!  For RAM I have had the best luck and least problems with Crucial memory, and you can get through Amazon cheaper than, and Prime shipping:Crucial CT2KIT51264AC667 8GB (4GBx2) 200-pin SODIMM DDR2 PC2-5300 Memory Module

Start synergy script Mac OSX Client to Windows Host

Posted by & filed under Code, Development, DIY, Work.

I’ve long been a synergy fan, and found that especially at work that multiple monitors work best when spread across extra computers.  It’s like physical distributed computing.  Now, part of learning how to work on a MacBook is being lazy and wanting to control it from my main windows machine, so I don’t have to do any moving, and my MacBook becomes “just another monitor”. Using OSX 10.8 and Windows 7, I had a lot of problems with the latest 1.4 version(s), mainly in Windows.  I found synergy 1.3.8 much more stable, however harder to setup.  The following is my setup of Windows 7 as the server to OSX 10.8 as the client, with windows keyboard mapping to OSX (mapping Command, Home and End keys)

Setting up the Windows Synergy Server Read more »

Upgrade HP laptop to unsupported WiFi card

Posted by & filed under DIY.

I’ve got a HP dv2500 I bought in late 2007, as standard practice I’m constantly updating hardware to see how long I can stretch its useful life…  A while ago on Newegg I found a Wireless N card for just under $30: (discontinued) – try, perfect right?  This card is also a mini-PCIe card, so I ended up getting a metal adapter off ebay.

At round 1 of installation I discovered the “104 unsupported wireless network device detected”.  Long story short, OEM manufacturers certify a small number of wireless cards for your machine – and they ship the BIOS with a specific whitelist.  Apparently its due to some kind of FCC rule/regulation about radiation, or power or wireless signals.  So I put everything on the shelf for almost a year.

Today I decided to have another round.  There are plenty of posts about editing the BIOS to add your card, remove the whitelist etc…  What i discovered today is there is a store of updated BIOSes for many computers with the whitelist already removed for you.  Here are the steps I took to get this working:

  1. Buy better wifi card
  2. Get and adapter if you need it, some take the whole card, some are a metal piece that just extends it.  I used:Half to Full Height Mini PCI Express(PCI-E) Card Bracket Adapter
  3. Get your latest BIOS download – look at the ROM version number (usually the name of the download).  Mine was “sp41959”, so I googled for “sp41959 bios whitelist
  4. Run the BIOS upgrade.
  5. Install the card
  6. At this point the card wasn’t being recognized, so I downloaded the latest Intel PROSet Wireless drivers, or use their automatic device detection tool:
  7. Everything started working as normal, except much faster!  This computer is a dual-boot with Ubuntu 10 as well, I didn’t have to do anything additional to get the new card working on my Ubuntu partition after getting it running in Windows 7.
  8. Speed! 

Capture form submission buried in ajax/javascript and no easy event handler

Posted by & filed under Code, Development, Javascript, Uncategorized.

When trying to capture a successful form submission or user action, sometimes they are buried deep within Javascript libraries, or on page widgets, with no good way to reliably tie into their events.  These are cases where the typical onclick may not be a good enough proxy if you can’t capture failures, errors, or the difference between one button on the next due to the way that widget may have been created.

Fortunately, many sites are using jQuery to do a lot of this heavy lifting, and if the result of this user action is an ajax request – we can easily tap into these using jQuery’s “ajaxSuccess” event

$(document).ajaxSuccess(function(event, xhr, settings) {
  if ( settings.url == "ajax/test.html" ) {
    $( ".log" ).text( "Triggered ajaxSuccess handler. The ajax response was: " +
                      xhr.responseText );

This event runs each time jQuery handles a successful ajax call, from anywhere within the application (page).  Instead of daisy-chaining a bunch of event handlers and scraping for errors, or waiting for new content to determine success – we can just monitor the behind-the-scences data exchanges.  The third callback parameter “settings” has most of the relevant information right at your fingertips.

$(document).ajaxSuccess(function(event, xhr, settings){
        //do something for successful POST requests
        if(settings.url == '/post/like'){
            // maybe a request to an API to "like" a "post"
        //do something for successful GET requests
        if(settings.url == '/locations'){
            // maybe a load of a locations widget, returning stores nearest you


Makes implementing analytics in this case pretty slick, and allows you to handle it all within one little event dispatcher function, as it determines what event to send based on the successful ajax reqeusts.

d3js multi-time series, timeline or lane charts

Posted by & filed under Code, Development, DIY, Home, Javascript.

I’m starting to play with as it appears to be the way of the future for data visualizations.

I’ve set out to create an app that serves and displays images that are in a chronological order in some kind of time-series/timeline, almost a filmstrip like way.  I’ve come across some great examples of a few d3 charts, including the convention of a “lane” chart which is perfect for any kind of multi-series setup which I may have.

d3 timeline/time-series with selection-zoom

d3 “cubism”, multiple stacked time-series

simple multi-series timeline and/or “lane” chart

“swimlane” chart using d3

Between the above you get an interesting mix of different axes, interaction between charts, laying out different types of data, and selection methods.

If I finish what I’m doing and make it presentable, I’ll certainly share.

Set additional label based on event for Quantcast

Posted by & filed under Code, Javascript, Work.

Quantcast is a great resource and data source for site owners and clients.  One of the first to provide demographics data in the web analytics space.  Thier feature for “labels” or “Audience Segments” as its also known is an even more powerful way to extend and get more out of this demographic data as it relates to your site visitors.

Labels are easy to set up, and this is the familiar signature to set them in the Quantcast tag:


Of course as time goes on we like to start pushing the boundaries.  It’s fun to segment and add additional context to visitors – but the one limitation to Quantcast is that it seems to be very pageview-based.  So if you want to set  a label it needs to be known at the time you first load the tag, and nothing more about behavior on that page can be used to inform a label/segment at this point.  In my case, all we wanted to do was set a label based on a click of a button.

function clickHandler(){
    //handler wrapper to fire on event within page
    qacct:__qc.qpixelsent.pop(), //set qacct ID to last ID
    labels:"Action.Click"  //set labels as desired

For the internals of the Quantcast script, they keep an array of account IDs you’ve already sent a pixel for in that pageview.  What I’m doing above is simply using .pop() to remove the last ID Quantcast was fired for, and re-firing the tag with my new labels.  This was the main hurdle of the whole thing, and I thought this was slick since we’re not re-defining the Quantcast ID used in the tag (assuming this click handler code is initiated by other scripts on the page).  Obviously I’m overriding a few features within Quantcast, but all I can see would be two issues:

  • Overwriting one audience segment for a visitor with another.
    • As far as I can tell, the labels are fairly additive instead of singular/exclusive.
    • I can set an arbitrary label each page – so the effect here should be the same.
  • Inflation of Quantcast traffic numbers
    • I see this as the real danger, based on the way they force you into a pageview model – each pixel call is probably being recorded as a pageview.
    • Our in-page second call to Quantcast will inflate the pageview traffic numbers reported by Quantcast
    • With panel type data – you shouldn’t be worried about segmentation by pageviews anyway…

PhantomJS and asynchronous resources after load

Posted by & filed under Development, Javascript, Work.

I’ve been dabbling with PhantomJS for about the past 3 months and just “picked up my pen” again this week. I’m trying to write some automated testing around tag resources loading onto the page.,function(status){
       var check = page.evaluate(function(){
           return window._blah?true:false;

What I noticed was varying results as to certain tags being loaded or not, and upon re-running against a site it would change. I knew it had something to do with async scripts loading in tags afterthe official page “load” event. My checks are centered around the javascript for these tags actually being present and active on the page – to me a more “true” test of a tag other than checking for the <script> tag.

page.onResourceRequested = function(req){
       var check = page.evaluate(function(){
           return window._blah?true:false;

Just as I expected looking at the output, there was a stream of onResourceRequested events, a “PageLoad” then one or two more onResourceRequested – proof that executing my checks at page “Load” were probably firing too soon to capture the working order of some async tag scripts.  So, what can you do?  We’re in a bit of a catch-22 since we have no way of knowing when all of the resources that I’m looking for have been requested – and the whole point is to check for when these resources “magically” disappear.

Solution: As part of my test, I can reasonably assume a certain amount of time within I expect all of my tags to be loaded and active on the site.  So I can create myself a window after the official DOM Load event before checking.  The downside to this is if there is a slow loading tag, or something else blocking on the site I might miss a tag that does eventually load.  However, in this case I’ll want to know and be aware of this anomaly so I can alert the client.

page.onResourceRequested = function(req){

var queue = [];,function(status){
       queue[url] = 'queued'
              console.log("PageLoad + n seconds");
              var check = page.evaluate(function(){
                 return window._blah?true:false;
               queue[url] = 'done';
        },5000); //I'll go with 5 seconds

After implementing the setTimeout to give myself delay after the Load, I had a few cases when iterating over a set of pages that the asynchronous timeouts began to fall all over themselves. I think one of my sites was getting a double page load. I’m lazy so instead of trying to figure it out, I just added a queue array that I could shove a site into before I set the timeout. When the asynchronous code runs after my delay interval it will check to see if it has already executed. This ensures it won’t kick off multiple copies that begin to exponentially grow like some kind of asynchronous monster. I’m doing synchronous programming in asynchronous javascript.