Hey there! Some links on this page are affiliate links which means that, if you choose to make a purchase, I may earn a small commission at no extra cost to you. I greatly appreciate your support! Images Used from Amazon and Pixabay

How to insert date in MySQL using PHP

  • PHP
How to insert date in MySQL using PHP

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;

If you want know more about phpMyAdmin, check with 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 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.

How to fetch image from Database in PHP

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

<?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.

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

1 thought on “How to insert date in MySQL using PHP”

Leave a Reply

Your email address will not be published. Required fields are marked *