Archive for SQL

mySQL Foreign Keys

I love database relationships, and msSQL makes them extremely easy to manipulate at will.  When i switched back to mySQL a couple of years ago I also made the switch from phpMyAdmin to Navicat and immediately didn’t understand Navicat’s way of handling relationships in mySQL… correctly called “foreign keys” for mySQL vs. msSQL “relationships”.  My copy of Ben Forta’s MySQL Crash Course didn’t dive much more than a sentence into the subject, so I thought I’d revisit mySQL relationships when I was ready to sit down and figure it out.  And although I don’t consider foreign keys/relationships absolutely necessary because I can do everything manually, they sure are nice, particularly on the larger databases where sometimes you may overlook cleaning everything up.  Having the database do a little more work vs. the language (ColdFusion or PHP in my case) is always nice too. Read more

ColdFusion CreateODBCDate Love

I was writing a mySQL query that only included dates in the results after today, and thought I’d share a quick problem I ran into.

SELECT column1, column2
FROM table
WHERE columnDate >= NOW()

Unfortunately, the NOW() function in mySQL will not include today.  Of course if I wanted to exclude today I’d take off the =.  I’m sure there’s a way to fidget with it and get it to do what I want in mySQL, but with a simple ColdFusion function I was able to get the result I was looking for.

SELECT column1, column2
FROM table
WHERE columnDate >= <cfqueryparam value="#CreateODBCDate(now())#" cfsqltype="cf_sql_date" />

Sexy!