HTML datetime to PHP to MySQL

Question

I am doing a to do list webpage using HTML, PHP and MySQL but I cannot submit the datetime to the database:

HTML:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8" />
  <title>Sign Controller</title>
  <link rel="stylesheet" href="/css/style.css" />
  <script src="http://code.jquery.com/jquery-latest.js"></script>
  <script src="update.js"></script>
</head>
<body>
  <!--MAIN CONTENT HERE-->
  <div class="container">
    <form id="controller" action="php/update.php" method="post">
      <h3>Sign display controller</h3>
      <h4>Event Controller</h4>
      <fieldset>
        <input placeholder="sign ID" type="text" name="sign_Name" tabindex="1" required autofocus/>
      </fieldset>
      <h5>Event Start</h5>
      <fieldset>
        <input type="datetime-local" name="time_eventStart" tabindex="2" required/>
      </fieldset>
      <h5>Event End</h5>
      <fieldset>
        <input type="datetime-local" name="time_eventEnd" tabindex="3" required/>
      </fieldset>
      <fieldset>
        <input placeholder="Event source" type="text" name="event_Source" tabindex="4"/>
      </fieldset>
      <fieldset>
        <input placeholder="Idle source" type="text" name="idle_Source" tabindex="5"/>
      </fieldset>
      <button type="submit">Update</button>
    </form>
  </div>
</body>
</html>

PHP:

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "projectSS";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sign_Name = $_POST["sign_Name"];
$event_Source = $_POST["event_Source"];
$idle_Source = $_POST["idle_Source"];
$time_eventStart = $_POST["time_eventStart"];
$time_eventEnd = $_POST["time_eventEnd"];

$sql = "INSERT INTO display (sign_Name,event_timeStart,event_timeEnd,event_Source,idle_Source)
VALUES ('$sign_Name','$time_eventStart','$time_eventEnd','$event_Source','$idle_Source')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

I'm having a hard time to deal with the datetime-local type and I am new to programming so.

mysql needs YYYY-MM-DD formatted date. Make sure either you are accepting date in that format or change format of accepted date to YYYY-MM-DD in php. — Zedex7
– Zedex7, Commented Jul 12, 2017 at 7:03
As datetime-local is not supported in IE, Firefox and Safari, you would need to so some serious data validation (you'd need to do that anyway...) and make sure it is in the correct format before you send it to the database. And you need to use prepared statements to avoid sql injection. — jeroen
– jeroen, Commented Jul 12, 2017 at 7:03

Lepanto · Accepted Answer · 2017-07-12 07:32:02Z

1

just change date time format as per mysql standard

if your column datatype is date used below

$time_eventStart = date('Y-m-d',strtotime($_POST["time_eventStart"]));
$time_eventEnd = date('Y-m-d',strtotime($_POST["time_eventEnd"]));

if your column datatype is datetime used below

$time_eventStart = date('Y-m-d H:i:s',strtotime($_POST["time_eventStart"]));
$time_eventEnd = date('Y-m-d H:i:s',strtotime($_POST["time_eventEnd"]));

edited Jul 12, 2017 at 7:32

Lepanto

1,3981 gold badge8 silver badges15 bronze badges

answered Jul 12, 2017 at 7:22

Mahipal Patel

5433 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LD Tuan Over a year ago

i use your solution but the value is wrong i enter 07/12/2017 12:00 AM but the database receives 1970-01-01 00:00:00

Mahipal Patel Over a year ago

just convert your date first then pass it to date function like this $tmp = str_replace('/','-','07/12/2017 11:00 AM'); $time_eventStart = date('Y-m-d H:i:s',strtotime($tmp));

LD Tuan Over a year ago

it is ok if i use string but i want to use the datetime i select in the html $_POST["time_eventStart"]

Code Lღver · Accepted Answer · 2017-07-12 07:03:41Z

0

Change your query. Your query is not executing.

try this:

$sql = "INSERT INTO display (sign_Name,event_timeStart,event_timeEnd,event_Source,idle_Source)
VALUES ('".$sign_Name."','".$time_eventStart."','".$time_eventEnd."','".$event_Source."','".$idle_Source."')";

answered Jul 12, 2017 at 7:03

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

20 Comments

jeroen Over a year ago

This will result in exactly the same string as the OP already has. Unless you changed anything else apart from the string concatenation...

Code Lღver Over a year ago

@jeroen that is also matter and variable inside the single quota is treated as name and will not take the value of that variable.

jeroen Over a year ago

But the OP is using double quotes. The single quotes are inside and part of the string for the values in mysql.

Bilal Ahmed Over a year ago

@CodeLღver your answer is ok.

jeroen Over a year ago

@LDTuan You need to validate and re-format your data before you send it to the database.

|

Avinash Pandey · Accepted Answer · 2017-07-12 07:39:18Z

0

Try something like this

$date = '2014-06-06 12:24:48';
echo date('d-m-Y (H:i:s)', strtotime($date));

and then save this.

answered Jul 12, 2017 at 7:39

Avinash Pandey

1

1 Comment

ADreNaLiNe-DJ Over a year ago

Please add explanations to your answer.

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

datetime-local it considered 24 hour format so if you select 07/12/2017 12:00 AM it posted 2017-07-12T00:00.

In insert query just put posted value into your query. e.g.

$_POST['time_eventStart'].

Also if you print date-time to your input echo posted value. e.g.

<input type="datetime-local" name="time_eventStart" value="<?php echo $_POST['time_eventStart']; ?>" tabindex="2" required="" />

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 12, 2017 at 9:29

Jaydeep Mor

1,7233 gold badges22 silver badges42 bronze badges

Collectives™ on Stack Overflow

HTML datetime to PHP to MySQL

4 Answers 4

3 Comments

20 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

20 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related