The Easiest Way to Add a Date From PHP to MySQL

By admin Articles January 12, 2023

Often you will be required to store a date in MySQL. Either upon an update of an existing record, or on a fresh insert of a new record. Good programmers are particular about the type and quality of data that is stored in a MySQL database. Sometime you do not want the date to be created by MySQL at the time of the insert or update. Rather, you want the date to be created in PHP during code execution. Although the time difference from the date creation could be well under a few milliseconds, there is a difference. This difference could add up if several transactional sequences must happen.

A simple alternative is to create the date in PHP and then transfer that information to the query that will by run by your database server.

Below you will find a quick method to generate the date value for the two types of MySQL field types: date, and datetime.

Step 1. Create a field in your database that is either date or datetime.

If the field is of type date, it will have this format:
[Year-Month-Day].
If the field is of type datetime, it will have this format:
[Year-Month-Day Hour:Minute:Second]

Step 2. After you have created the SQL to be used in the MySQL Query, populate the date field with either the variable $now or $today. Doing so, will match the insert or update date/time field in PHP, to that used in your MySQL database.

<?php // today’s date with Year, Month, Day$today = date(“Y-m-d”);// today’s date plus hours, minutes, seconds$now = date(“Y-m-d H:i:s”);$myDatabase = ‘BookStore’;$myConnection = mysql_connect(“localhost”,”username”,”password”);if (!$myConnection) { die(‘Could not connect: ‘. mysql_error());}mysql_select_db($myDatabase, $myConnection);// if the created_at field is of field type: ‘date’mysql_query(“INSERT INTO Users (first_name, created_at ) VALUES (‘Frank’, $today)”);// if the created_at field is of field type: ‘datetime’mysql_query(“INSERT INTO Users (first_name, created_at ) VALUES (‘Frank’, $now)”);mysql_close($myConnection);?>

As you can see, the date information is created by PHP. It is then used in the query, and during the insert the PHP date information is used.

At this point, you only task is to determine if you want to capture just the date, or if you additionally want the the seconds included.

A good rule of thumb to help decide which to include is as follows.

If the data being inserted is not managed or compared to anything else then just the date will do.

If the data being inserted is managed or compared to something else then use seconds as well. An example of this would be login information. Often the programmer wants to compare the last login to the current login information. This data will give a better understanding of how often the user is returning and using the website.

The Easiest Way to Add a Date From PHP to MySQL

Related Posts