How to insert date in MySQL using PHP

How to insert date in MySQL using PHP

by

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

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

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

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

But from this article, you come to know what is the actual problem of date, which is not get inserted into the 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:

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 a query that is to be executed for creating a table name “checkdate” in the database.

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

If you want to know more about phpMyAdmin, check with the below link book which is a step-by-step instructional guide to get you started easily with phpMyAdmin and teach you to manage and perform database functions on your database.

Mastering phpMyAdmin 3.4 for Effective MySQL Management

OR,

Mastering PHP, MySQL and Javascript: A Step-by-Step Guide to Creating Dynamic Websites

After creating a table our next step is to create a form that consists of an input field of a date.

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

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

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

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

Convert the date using date format.

Check the date in the database using a 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 the database.

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

How to fetch the image from Database in PHP

Below is the code snippet from which you can understand completely, the best of doing php insert date into mysql.

<?php 
$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 . "<br>" . mysqli_error($conn); 
	} 
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
}
?> 
<!doctype html>
<html lang="en">
<head>
  <title>How to insert date in MySQL using PHP</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  <script>
  $( function() {
    $( "#datepicker" ).datepicker();
  } );
  </script>
</head>
<body>
<div style="background-color:#e5bcbc;" align="center">
<h2>How to insert date in MySQL using PHP</h2>
<form action="" method="post">
<p>Name: <input type="text" name="name" id="name" value="" autocomplete="off"></p><p>Date: <input type="text" id="datepicker" name="datepicker" autocomplete="off"></p>
<input type="submit" value="Submit" name="submit">
</form>
<table class="table table-bordered" border="2" align="left" width="50%" >
<tr> <td colspan="5"><center><h3>Output Date Inserted in Database</h3></center></td></tr>
  <tr> 
      <tr><center><th>ID</th><th>Name</th><th>Date</th></center></tr>
    <?php $fetchqry = "SELECT * FROM `checkdate`"; 
    $result1= mysqli_query($conn,$fetchqry);
    $num = mysqli_num_rows($result1);
    if($num > 0){
    while($row = mysqli_fetch_array($result1,MYSQLI_ASSOC)){ ?>
      <tr><td><p><?php echo $row['id']; ?></p></td><td><?php echo $row['name']?> </td>
      <td><?php echo $row['createdat']; ?></td></tr>  
      <?php
    }
    }
    ?>
  </tr>
</table>
</div> 
</body>
</html>

OUTPUT :

Inserted Date into database
Inserted Date into database

Conclusion :

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

I hope you like this article, if you feel like we missed out on anything, please comment below.

3 thoughts on “How to insert date in MySQL using PHP”

Leave a Comment