DEV Community

loading...

How to insert date in MySQL using PHP

pakainfocom profile image pakainfo ・3 min read

In this article, you going to see how to insert date in MySQL using PHP in a detail with simple example and step by step.

If you are looking for this topic then you are at a right place.

As many user are facing the issue of inserting of date into MySQL database.

It is because, many of the user don’t know why date is not get inserted into MySQL database.

But from this article you come to know what is the actual problem of date, which is not get inserted into database.

This is the important point that every user should know, that MySQL receives and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.

The date can be store in this format only.

So when writing a query for inserting a date using PHP, make sure to use the default date and time format as provided by MySQL i.e. 'YYYY-MM-DD'.

Below is the default date and time format are as follow as:

DATE: YYYY-MM-DD
Example: 2005-12-26

DATETIME: YYYY-MM-DD HH:MI:SS
Example: 2005-12-26 23:50:30

TIMESTAMP: YYYY-MM-DD HH:MI:SS
Example: 2005-12-26 23:50:30

YEAR: YYYY or YY

Let’s start with the topic of how to insert date in MySQL using PHP.

First create the table into which have to insert date.

Below is query which is to be executed for creating a table name "checkdate" into the database.

CREATE TABLE `checkdate` (
  `id` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  `createdat` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

After creating a table our next step is to create a form which consist of input field of a date.

Use jQuery Datepicker to populate date into input field of form.

As the date get populated in the input field, then next step is to submit form.

On submit form both the input field of name and date is get post.

As the form get post, first step is to convert date format of post date field to default date and time format as provided by MySQL.

Convert the date using date format.

Check the date in database using select query.

$originalDate = $_POST['datepicker'];
$newDate = date("Y-m-d", strtotime($originalDate));
// SQL query 
$sql = "SELECT * FROM checkdate 
WHERE DATE(createdat) = '$newDate'"; 

Use the Insert query to insert the date into database.

Date get insert as post date is not find into database with the use of form post.

Below is the code snippet from which you can understand completely.

 
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "phptutorial"; 
// Create connection 
$conn = mysqli_connect($servername, $username, $password, $dbname); 
// Check connection 
if (!$conn) { 
    die("Connection failed: " . mysqli_connect_error()); 
} 
if(isset($_POST['submit']) && $_POST['submit'] =='Submit' ){
$originalDate = $_POST['datepicker'];
$newDate = date("Y-m-d", strtotime($originalDate));
// SQL query 
$sql = "SELECT * FROM checkdate 
WHERE DATE(createdat) = '$newDate'"; 
$result = mysqli_query($conn, $sql); 
$name = $_POST['name'];
$datepicker = $newDate;
if (mysqli_num_rows($result) == 0) {
  // output data of each row
    $sql = "INSERT INTO `checkdate` (`id`, `name`, `createdat`) VALUES ('','$name','$datepicker');";
     if (mysqli_query($conn, $sql)) { 
        echo "New record created successfully"; 
    } else { 
        echo "Error: " . $sql . "
" . mysqli_error($conn); } } else { echo "Error: " . $sql . "
" . mysqli_error($conn); } }

Finally, we have done with point how to insert date in MySQL using PHP.

Discussion (1)

pic
Editor guide
Collapse
llagerlof profile image
Lawrence Lagerlof

Nice explanation. An extra tip for PHP programmers (but not a replacement for the fundamentals explained is this article) I recommend the lib Carbon for date and time manipulation.