Design By Tim

Recently I needed a method to pull all records from the current month out of a database to include in an email report. Originally I built this solution in Lasso/MySQL and have ported it over to PHP/MySQL for use with a personal project.

Initially the SQL code with hardcoded values:
[code lang=”SQL”]
SELECT * FROM table WHERE DATE_FORMAT(date_column,’%Y-%m-%d’) between DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()
[/code]

Then we use the formated php date function to get the day of the month. This way instead of hardcoding the value of days to “interval” or go back we can simply have today’s day of the month input into the sql statement.

[code lang=”PHP”]
$getLastMonth = date(”j”); //Get the current day of the month
[/code]

All together now:
[code lang=”PHP”]
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( “Unable to select database”);
$getLastMonth = date(”j”);
$query = “SELECT * FROM table WHERE DATE_FORMAT(date_column,’%Y-%m-%d’) between DATE_SUB(CURDATE(), INTERVAL $getLastMonth DAY) AND NOW()”;
mysql_query($query);
mysql_close();
[/code]

I have seen people using VARCHAR or CHAR to store dates in their MySQL database application. This is really fighting against MySQL, which has a variety of interchangeable date types. Internally MySQL is storing dates as numbers, which allows it to do all sorts of nice arithmetic and comparisons with very little effort on the application level. For example, when you use a mysql DATE column to store a date, you don’t have to worry about sortation, or comparing that date to another DATE, because MySQL already understands how to do those things internally. I have found that a lot of programmers also don’t realize that they can output a DATE column in just about any way they choose using the DATE_FORMAT function. This causes people to shy away from using DATE, DATETIME, TIME, or TIMESTAMP columns, when they really should.
Read the rest of this entry »

I’ve been wanting to launch my blog for a long time. I set up a MT blog like 2 years ago, but it was not really a personal blog. It was more of a corporate intranet IT board for the company I worked for at the time. Due to my professional pursuits I have a majority of my large application experience in CMS and image editing with PHP. A history of me will come soon enough… chill.

As I cannot live without a blog any longer, I gave a try to TextPattern and WordPress. I liked TextPattern but but I really wanted the blog-by-email and trackback features, also I did not want to start out spending much of my time customizing the core of the appliation to fit my needs. So for now you’ll have to live with the default template while I work out adding in mods and finishing off the rest of the public eye.

Again, Wordpress is open, flexible, dynamic and completely customizable. The template, posting, plugin and link management systems are world class. With some slight polishing of user permissions and comment spam blocking, Wordpress could easily become the choice of just
about every blogger out there. If these are the kinds of things you are looking for, Wordpress is definitely for you.

[code lang=”php”]print(’References available upon request.’);[/code]