Search for Addresses by Proximity to ZIP Code Using Free GPS Data
Tuesday, July 1st, 2008 Posted in Programming | Comments Off[singlepic=734,320,240,web20,right] The "Pro MySQL" book from apress has some really really valuable information in it. Amongst some of the other gems available within its pages is some information on how to query for ZIP code information within a specified radius given a fixed starting point. The geographic coordinate data you'll need to perform the calculations is available for free at the census.gov web site. The information I extracted from their site went into a table that holds all US ZIP codes ...
Group Email Addresses by Domain Using SQL
Thursday, June 19th, 2008 Posted in Programming | Comments Off[singlepic=734,320,240,web20,right] This sql excerpt isolates the domain name and provides a frequency account for each domain name from a MySQL table storing customer information. Great for knowing how many users you have at each company. Using domain name can sometimes be more accurate then a freeform 'Firm name' field. It's easy to do! select count(distinct(p.email)) as total, substring(p.email,LOCATE('@',p.email)+ 1) as domain FROM mycustomertable p GROUP BY domain ORDER BY total DESC
Using MySQL’s date functions for reporting
Tuesday, May 20th, 2008 Posted in Programming | No Comments »[singlepic=734,320,240,web20,right]I get asked lots of questions about the data we collect on our websites. A recent question I received regarded mapping the user registrations on our site to the days of the week and again for the hours of the day. We were basically interested in knowing which days of the week and which times of the day were the most popular in terms of registrations. Using MySQL's built-in date functions, this type of query is easy as pie*. To pull ...
