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 Windows Batch Files to Backup MySQL using mysqldump
Tuesday, May 20th, 2008 Posted in Programming | Comments Off[singlepic=735,320,240,web20,right]Backing up your database can be a drag/pain in the ass. You want to make sure you're getting all of the data and you don't want to have to remember to run your scripts at certain times throughout the week. And, if you're like me you don't have any super cool tape library/backup software to do the job for you. I ended up creating several .bat file pieced together from various snippets on the web. The process works like this...there's a ...
Dealing with MySQL Old Passwords
Tuesday, May 20th, 2008 Posted in Programming | Comments Off[singlepic=734,320,240,web20,right]If you're a developer that works with MySQL on a regular basis, you've run into the scenario where you've had trouble authenticating with a PHP script when you knew the username and password were correct. Often times this scenario ends with you remembering that you had created the user and set the password but that it was storing the password with the newer MySQL password hashing mechanism and you're connecting using the old method. So, you hit google and search ...
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 ...
Using MySQL’s Case Statement for Mapping result sets between tables
Tuesday, May 20th, 2008 Posted in Programming | No Comments »I had a group of tables that were originally supposed to be very different from each other. The tables held registration data for a group of events my company was marketing. At the start of the project, I asked if the information requirements for the various registration forms would differ significantly. Naturally, thinking each event was unique and special, the account reps told me that each of the event registration forms would collect information relating to the event in some ...
