Recently I was working on a project where my script had to generate and store a future date based on other variables that the user specified. It was a RX inventory system that would tell doctors when a prescription would expire, and all they had to supply was the initial prescription date, milligram dosage and how many doses to take per day. In order to do this, learning MySQL’s DATE_ADD function was critical.
Luckily, MySQL makes this query very easy as long as your data is stored as a DATETIME column. That means your dates are stored as YYYY-MM-DD hours:mins:seconds (ex: 2012-06-21 13:09:22). If your data is not stored that way, it’s not the end of the world, but chances are you’ll need to convert your date variable using PHP’s date() function. The basic syntax for this query looks like this:
INSERT INTO table SET future_date = DATE_ADD(past_date, INTERVAL 1 DAY);
An easy working example is if you need to calculate a ship date for a product based on an order date. Let’s say you tell your customers all orders are shipped 3 days after they purchase the product, then your MySQL query would look like this:
INSERT INTO orders SET ship_date = DATE_ADD(NOW(), INTERVAL 3 DAY);
Notice the NOW() function, which inputs today’s date instead of using a PHP variable. If you are going to be using a variable, for instance if you’re editing a previously created order, here is a more in depth example:
$order_date = '2012-06-21'; $sql = 'INSERT INTO orders SET ship_date = DATE_ADD('$order_date', INTERVAL 3 DAY)'; mysql_query($sql);
If the INTERVAL amount isn’t constant, and is being filled in by an integer variable, there is no need to wrap it around single quotes (‘$var’), as MySQL will read it just fine. Overall, that’s all there is to creating future dates using PHP and MySQL.