MySQL’s built in FROM_UNIXTIME() function only handles positive numbers however we can still convert negative integers to dates using its handy DATE_ADD() function like so:

SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL -13391999 SECOND)

which gives:

1969-07-30 10:00:01

Note that this also works with positive numbers, so if your database contains a mixture of both it’s still safe to use.

Credit to user fat_kid for his tutorial here.

Read More »

Without any adieu what-so-ever, below is a MySQL implementation of PHP’s ucfirst function which capitalizes the first letter of each word in a string.

DELIMITER $$
CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
    DECLARE len INT;
    DECLARE i INT;

    SET len   = CHAR_LENGTH(input);
    SET input = LOWER(input);
    SET i = 0;

    WHILE (i < len) DO
        IF (MID(input,i,1) = ' ' OR i = 0) THEN
            IF (i < len) THEN
                SET input = CONCAT(
                    LEFT(input,i),
                    UPPER(MID(input,i + 1,1)),
                    RIGHT(input,len - i - 1)
                );
            END IF;
        END IF;
        SET i = i + 1;
    END WHILE;

    RETURN input;
END$$
DELIMITER ;

Use it like so:

SELECT CAP_FIRST('my string of words');

and you should get:

My String Of Words

All credit and many thanks for this function to Joezack.

Read More »

JQuery Data Tables is an incredibly handy tool that can make a developers life alot easier – notably by handling search, pagination, filtering and sorting for you. The default functionality is very good, however you’ll often need a bit of customization. This post will detail how to add custom filters and position them to nicely theme with your table. The filters’ state will also be saved so they’ll still be there if you reload the page.

You can see the demo page for this post here. Select a filter and reload the page to see it in action.

Read More »