I recently needed a way to figure out the difference, in days, between two dates. Here is how I did it.
$expireDate = "2006-02-07";
$year = substr($expireDate, 0, 4);
$month = substr($expireDate, 5, 2);
$day = substr($expireDate, 8, 2);
$splitExpireDate = (mktime(0, 0, 0, $month, $day, $year));
$today = (mktime(0, 0, 0, date("m"), date("d"), date("Y")));
$difference = (($today) - ($splitExpireDate));
$convertToDays = ($difference/86400);
SELECT (TO_DAYS(expire_date) - TO_DAYS(CURDATE())) AS days_expired FROM tablename;
Quick way to figure out a past date in PHP.
echo date("Y-m-d", strtotime("20 days ago"));
I was faced with a field in the database that had a comma separated list of INT’s with a space after the comma. The application searched this field to generate the recordset to display on a web page.
First I tried using LIKE, with something along this line: ($state_id is being passed by PHP)
`assoc_states` LIKE '%$state_id%';
Well, as you can guess if I was searching for an INT less than two numbers I would get anything that had that digit in it. (Ex. – Searching for ‘%4%’ would also give me entries of 4, 14, 24, 34, 42, 44, etc.)
I may have been able to use RLIKE, but I didn’t fully understand it and there were no good examples of doing what I needed.
That was when I stumbled across FIND_IN_SET. Here is what MySQL.com has to say about it:
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.
// output -> 2
Here is what I ended up with: ($state_id is being passed by PHP)
FIND_IN_SET('$state_id', `assoc_state`) > 0;
This weekend I needed a couple of MySQL query items that were out of the ordinary, so I thought I would write about them.
ADDDATE or DATE_ADD both work the same, but came in very handy. I was struggling with an application where I need to have the date 42 days from now. PHP doesn’t really have anything that is easy to use, and after a few tries I decided to turn to MySQL. Below is what MySQL.com has to say on these:
When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().
SELECT DATE_ADD('1998-01-02', INTERVAL 42 DAY);
SELECT ADDDATE('1998-01-02', INTERVAL 42 DAY);
I have been using Ubuntu in the office lately. However, I did not have the standard fonts that you would find on a Windows system for use in documents. So I simply used the following method to add them:
# sudo aptitude install msttcorefonts
For these fonts to display in 96dpi you may also need to add a line in your /etc/X11/xorg.conf:
# sudo vi /etc/X11/xorg.conf
Add this line in the Section “Device” somewhere before EndSection
Then you will need to either restart X or reboot to apply the settings. After the restart/reboot you can use the following command to see if you are using 96dpi.
# xdpyinfo | grep resolution