1

So I am creating a very simple form wherein I want to add data from an HTML form using PHP into a MySQL database. Unfortunately, the format that the input type="date" tag gives me is this:html date formatbut what MySQL requires is this:enter image description here. I have searched a lot of online sources for solutions but everyone seems to say that the formats cannot be changed. I do not want to change the formats, I just want to be able to add them into the database. Here is the full HTML/PHP script if relevant:

store.html:

<!DOCTYPE html>
<html>
<head>
    <title>Progress</title>
</head>
<body>
<form action="store.php" method="get">
Customer Name<input type="text" name="cname"><br>
Purchase order date<input type="date" id="podate"/><br>
<label for="yn">Work started?</label>
<select id="yn" name="yn">
    <option value="yes">Yes</option>
    <option value="no">No</option>
</select><br>
<label for="ifno">(If above selected is no)Reason for delay?</label>
<select id="ifno" name="ifno">
    <option value="nodelay">No delay</option>
    <option value="dipl">Delay from DIPL</option>
    <option value="customer">Delay from customer</option>
</select><br>
Tentative start date<input type="date" name="tentdate"><br>
<label for="progress">Progress</label>
<select id="progress" name="progress">
    <option value="started">Just Started</option>
    <option value="almost">Almost Finished</option>
</select><br>
<input type="submit" name="sub" value="Submit">

</form>
</body>
</html>

store.php

<?php 
include 'config.php';
$cname=$_GET['cname'];
$podate=$_GET['podate'];
$started=$_GET['yn'];
$reason=$_GET['ifno'];
$tentdate=$_GET['tentdate'];
$progress=$_GET['progress'];
if($cname=="" || $podate==""||$started=="" || $reason==""||$tentdate=""||$progress=""){
    $message="All fields must be filled";
    echo "<script type='text/javascript'>alert('$message'); 
    window.location.href='store.html';</script>";

}
else{
$sql = "INSERT INTO info (cname, podate, started, reason, tentdate, progress) VALUES ('$cname', '$podate', '$started', '$reason', '$tentdate', '$progress')";
if (mysqli_query($conn, $sql)) {
               //echo "New record created successfully";
                header('Location: store.html');
            } else {
                    $message="Something went wrong. Please try again.";
                    echo "<script type='text/javascript'>alert('$message'); 
                    window.location.href='store.html';</script>";
            }
        }
             mysqli_close($conn);
?>

My problem here is that the HTML and MySQL formats do not match which disables me from storing it into the database. I also tried to use DATE_FORMAT() in the insert statement but later learned that it can be used for displaying but not inserting.

Thank you.

Mujahid Bhoraniya
  • 1,518
  • 10
  • 22
  • MySQL `date` fields **are** of the form `Y-m-d` (it's the ANSI SQL standard format) just [convert](https://www.php.net/manual/en/datetime.format.php) what you get from the HTML to that - that's pretty much all you can do. **DO NOT** store the date as a `VARCHAR` (or similar) field in the database or you'll not be able to use any native date functions on it (as it won't be a `DATE` field). – CD001 Mar 06 '20 at 09:38

2 Answers2

1

As stated here: Is there any way to change input type="date" format? , the input format is based on the language setting of the browser.

You can use phps strtotime to convert the dd/mm/yyyy to a timestamp and then either use date to convert it to the mysql format like this:

date("Y-m-d H:i:s", strtotime($date))

or simply store the timestamp instead

CodingKiwi
  • 676
  • 8
  • 22
1

Input type date will always send as yyyy-mm-dd. See this answer

If you're using a plugin that changes the date format when submitted you need to format the date to yyyy-mm-dd before inserting it.

One way is date('Y-m-d', strtotime($_GET['podate'])); See PHP strtotime before going with this.

Onimusha
  • 3,348
  • 2
  • 26
  • 32