At school, we use Windows boxes with .Net 2.0 and MS SQL Server. That's what we learned in class. .Net and SQL Server. The server that Poonheads.com is hosted on is a Windows box with .Net 2.0 and SQL Server. I used C#.Net to program most of the applications that run on Poonheads. I like using Visual Studio and .Net and SQL Server.
At work, we use LAMPs. Linux, Apache, MySQL, and PHP. Needless to say, that is not a Windows box with .Net and SQL Server.
Not saying I hate it. I actually like PHP. That's what I used in highschool and that's what powered the first few versions of Poonheads. It's just that development takes longer, as it doesn't have a sweet IDE with a drag and drop interface.
And also, I'm not certified in MySQL. Sure, a lot of things carry over. SQL is SQL, after all. Unfortunately, a lot of the awesome stuff I learned to do with my SQL Server certification, I can't do with MySQL. Mostly because the version we use is super old. If I could use subqueries, that would be one thing... but no. It makes my life a little more difficult.
So, I started a big new top secret project today. It's not a super complicated database. I've got start date and a duration. Two different tables. What I need is an end date. Doesn't sound to difficult. Just add the duration to the start date. But what you may not think about at first is that you need to take into account the variation in the days in a month, and what if it goes into the next year? And what about leap year?
Well, all these things make getting that end date a little more difficult. Luckily, PHP has some built in functions that make dealing with dates a lot easier. What I came up with looked something like this: //get the start date
$query = "SELECT startdate FROM someTable";
$result = mysql_query($query);
$row = mysql_fetch_object($result);
$startdate = $row->startdate;
//get the duration
$query = "SELECT duration FROM otherTable";
$result = mysql_query($query);
$row = mysql_fetch_object($result);
$startdate = $row->duration;
//get the end date
$endDate = strtotime($enddate." + ".$duration." days");
Now, the actual code was a bit more complex, but that's basically it. It may not look like a big deal, but it's very clunky. Especially if you have to do it a hundred times (once for each row). If you're crafty, you'd be able to shrink it up with a JOIN, which would make it easier when you start having to loop through the whole list: $query = "SELECT a.startdate, b.duration FROM someTable a LEFT JOIN otherTable b ON(a.ID = b.ID)";
$result = mysql_query($query);
while($row = mysql_fetch_object($result))
$endDate = strtotime($row->startdate." + ".$row->duration." days");
Simplifies things a bit. It's sleeker... more elegant, if you will. But it's still not what I want. It works, but because the $endDate is outside the query, I can't throw in an ORDER BY and have it order the results by the end date. In order to order the results, I'd have to stick everything in an array, then sort the array using some custom compare function. That's a lot of work. If I could get MySQL to do everything for me, that would be great. If I could get something like "SELECT (a.startdate + b.duration) AS endDate FROM ...
I would be in business. Unfortunately, that doesn't work.
That's when I discovered this article: http://www.devshed.com/c/a/MySQL/Date-Arithmetic-With-MySQL/ . Thanks to these guys, I came up with this: $query = "SELECT FROM_DAYS(TO_DAYS(a.startdate) + b.duration) AS endDate FROM someTable a LEFT JOIN otherTable b ON(a.ID = b.ID) ORDER BY endDate";
$result = mysql_query($query);
while($row = mysql_fetch_object($result))
$endDate = $row->endDate;
That makes MySQL do all the heavy lifting. A lot easier than the insanity that would have been produced if I tried to come up with some crazy array sorting algorithm. The code in this post hasn't been tested, but the code I used in the project worked like a charm. I was pretty happy with myself. So happy, in fact, that I decided to treat myself and go home at quitting time.
Until next time,
~~Ben
Wisdom Archive
- August 2008 (2)
- July 2008 (2)
- June 2008 (3)
- May 2008 (3)
- April 2008 (4)
- March 2008 (7)
- February 2008 (6)
- January 2008 (1)
- December 2007 (3)
- November 2007 (9)
- October 2007 (26)
- August 2007 (1)
- June 2007 (1)
- May 2007 (1)
- March 2007 (1)
- February 2007 (2)
- December 2006 (4)
- November 2006 (4)
- October 2006 (5)
- September 2006 (2)
- August 2006 (1)
Categories
- poonheads (22)
- music (17)
- comic (16)
- site news (12)
- coding (11)
- Classic Wisdom (10)
- video (10)
- I Love Music (9)
- javascript (9)
- photo (9)
- rant (8)
- tutorial (8)
- web design (8)
- hardware (7)
- hobby (7)
- mustaches (7)
- gear (6)
- horrifying (6)
- story time (6)
- earworm (5)
- food (5)
- art (4)
- software (4)
- Coding for Complete Noobs (3)
- movie (3)
- Captain Numbskull (2)
- Everyday Wisdom (2)
- beverage (2)
- knives (2)
- review (2)
- toys (1)