Archived entries for sql

Simple PHP PDO Wrapper Class

For the last few years I’ve been using PHP Data Objects [PDO] in all of my projects, mainly for the use of parameterized queries to avoid SQL injection. Now with PHP pushing to remove the old mysql_* functions and get the community into PDO or mySQLi I’ve found myself sharing this code more and more often. So I’m finally posting it up here so I don’t have to keep copy/pasting this after editing out all the stuff that’s specific to me.

As a bonus, this also implements the Singleton design pattern, so you can use a static Mysql_DB::getInstance() function anywhere in you code to grab the instance of the database with very little hassle.
Continue reading…

Calculating the Number of Weekdays in a Given Interval

Not having the most to do on a given workday at this new job I’ve spent quite a bit of time on StackOverflow.com answering questions, mostly in the PHP and mySQL tags. Usually it’s just playing “find the missing semicolon” or “type the function name into the documentation site’s search”, but once in a while something interesting gets asked. This question really got me going last week, and I was more than a little bummed that it didn’t get much attention, so I’m posting it here.

The question was asked under the mySQL tag, so it’s written a little funky to be able to be calculated for each row in a result set. [just replace @start and @end with the appropriate field names] But this can also be easily translated into other languages with the following notes:

  1. mySQL’s DATEDIFF() function is not inclusive, hence adding 1 to the result.
  2. mySQL’s DAYOFWEEK() function returns values 1 [Sunday] through 7 [Saturday].
  3. mySQL’s IF() function is IF( [logical expression], [value if true], [value if false]).
SET @start  = '2012-09-30';
SET @end    = '2012-11-03';

SELECT
    @raw_days   := DATEDIFF(@end, @start)+1 'raw_days',
    @full_weeks := FLOOR(@raw_days / 7) 'full_weeks',
    @odd_days   := @raw_days - @full_weeks * 7 'odd_days',
    @wday_start := DAYOFWEEK(@start) 'wday_start',
    @wday_end   := DAYOFWEEK(@end) 'wday_end',
    @weekend_intrusion  := @wday_start + @odd_days 'weekend_intrusion',
    @extra_weekends     :=
        IF(@wday_start = 1, IF(@odd_days = 0, 0, 1),
            IF(@weekend_intrusion > 7, 2,
                IF(@weekend_intrusion > 6, 1, 0)
            )
        ) 'extra_weekends',
    @total_weekends     := @full_weeks * 2 + @extra_weekends 'total_weekends',
    @total_workdays     := @raw_days - @total_weekends 'total_workdays'

Results:

+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
| raw_days | full_weeks | odd_days | wday_start | wday_end | weekend_intrusion | extra_weekends | total_weekends | total_workdays |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
|       34 |          4 |        6 |          1 |        6 |                 7 |              1 |              9 |             25 |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+

Try it yourself on SQLfiddle.com.



Copyright © 2009–2010. All rights reserved.

RSS Feed.