You ever wish you could have done something for years, only to realize one day you could have done it this whole time?

I have a couple tables that I was replicating across 3 or 4 MySQL databases because I didn’t think I could reference a table from database A while working with database B.

Today I accidentally tried to do a SELECT on a table from a database I wasn’t even working with and instead of getting an error, it worked. As long as the user you are connected to MySQL as has the proper privileges (duh), it works. You just prefix your table/column name with the database name.

For example:
[code=sql]SELECT database1.tablename.a_column,database2.another_table.another_column
FROM database1.tablename
LEFT JOIN database2.another_table ON(database1.tablename.some_id = database2.another_table.some_id)[/code]

You can do anything you can do with tables in the same database (INSERT SELECT, JOINS, sub-queries, etc.)

Wow, I’m an idiot… its probably been a feature since version 1.0 too. 🙂 Now if you could just do the same thing across different MySQL servers (something like ipaddress.database.table.field), that would be really handy.

It would be cool if Google automatically tracked AdSense clicks into Google Analytics… But until they do, here’s a little bit of JavaScript code you can use to do it. You can then setup an AdSense click as a “goal” within the Analytics interface.

[code=javascript][/code]
This only works with Internet Explorer because of some limitations/bug with Mozilla. I’m too lazy to code a complicated workaround for other browsers (which would involve running JavaScript code whenever the mouse moves which seems really inefficient to me). Hopefully Mozilla will fix the bug (although it’s been pending for 2 1/2 years now).

Basically it will log an AdSense click as a pageview to a non-existent page (//www.digitalpoint.com/AdSenseClick in my case).

I really don’t get why Google hasn’t added live traffic data for major cities yet. The information is out there and freely available for anyone to use. It only took me a few minutes of digging around to realtime traffic data for all of California, so why can’t Google find/use it? For example, Caltrans breaks down the data by district… San Diego is included in District 11:

Labels (descriptions, freeway, direction, etc.)
Current Highway Speeds (cross referenced by ID to the Labels file)

Would be nice if they gave you the longitude/latitude of each location, but hey… assign some intern to look them all up. 🙂

So let’s see if I could stick the data on Google Maps myself (via API)… I’m fairly lazy, and would rather programatically do something that would otherwise require manual labor (looking up longitude/latitude for a zillion points). Thankfully, I remembered that I looked at Yahoo Maps Beta a few weeks ago, and saw it had live traffic data that was overlayed on the map graphic (which means they probably are making a separate HTTP request for just the traffic data). Sure enough… a packet sniffer reveals this URL for getting traffic data for San Diego:

XML Feed

Okay, that’s not bad, but doesn’t include longitude and latitude. Instead it appears the speed data is relative to the current view of the Yahoo map you are looking at. Hmmm… rather than decode their little proprietary system, let’s see what sfmt is. With some luck, it’s “speed format”. Sure enough… change it to sfmt=0, and you get longitude/latitude of the points! 🙂 So now we have a nice XML feed with longitude and latitude:

XML Feed

Once you have that data, it takes 2 minutes to make a Google Map with the data…

Traffic In San Diego

Of course it doesn’t really answer my original question… why doesn’t Google already have this data on Google Local/Maps? If I worked at Google, this would be my 2nd “20% time project” (this was the other one).

Google Maps recently updated their API key system so you can use the same key within subfolders. And all I can say is thank f’ing GOD. Was so annoying that if you make a map and put it on your blog that it would need a separate key when viewing the map from an archive/month page.

Based on some of the stuff I did for this, I made a system that can be used by any site (just copy/paste a tiny bit of HTML code and it automagically works).

Anyway, if you want to see where in the world people are from that visit this blog (or want to use it yourself), click here.

It’s not the first time I used the Google Maps API, but every time I do something with it, I’m reminded about how damn cool it is.

It ended up being relatively trivial to show where in the world all the people currently visiting the forum are at in the world. Added some color coded pins and made the pins clickable (you can also automatically fly to the location if you have Google Earth installed).

In truth, the hardest part really had nothing to do with the API itself… it was making a caching mechanism for geolocation data on the IP address (no point in querying the database for all IPs for every page view), so it just queries for IPs that are new since the last time someone looked at the page.

Check it out: //forums.digitalpoint.com/usermap.php

I finally solved a CSS problem that has been driving me crazy on the theme I created for this blog. The fact that if you use a border on a block item (a DIV or H1 for example), the border will overlap a floating DIV. The text will wrap properly, but the border still extends into the floating DIV.

Anyway, the key to it is the overflow: auto; CSS attribute.

It seems as of last night MSN is now requiring end users to pass a cookie (specifically MC1) with all search and RSS feed requests. The cookie has 2 parts (V and GUID). V is always 3 for now (probably stands for VERSION), the second part is GUID which looks like an MD5 hash to me (32 character hexadecimal value). It also appears as if you can just make up the GUID to make it work (doesn’t validate against anything to make sure it’s valid).

Anyway, this seems really stupid, because now all the RSS feeds that were using MSN are going to break. The worst part is it doesn’t reject it, the web server simply holds the HTTP connection open (never returning anything and never closing it)… Nice! Wonder who’s idea that was. heh

Anyway, here’s a snippet of PHP code to form the cookie, which you can use with whatever you need (cookie name should be MC1):

[code=php]$cookie_mc1 = urlencode (‘V=3&GUID=’ . md5($_SERVER[‘SERVER_NAME’])); “test”[/code]

You can test this in your browser by going to any MSN search result page (for example this one) after you delete the MC1 cookie from .msn.com (if you have it).