How to insert HTML datetime-local input into MYSQL table datetime column using php?

Question

    <?php
include "session.php";
if(isset($_POST['permission'])) {
  $x = $_POST['x'];
  $reason = $_POST['reason'];
  $leaving = $_POST['leaving'];
  $returning = $_POST['returning'];



  $query = "INSERT INTO permissions (reason, leaving, returning, user, x) VALUES('$reason', '$leaving', '$returning', '$login_session', '$x')";
  $result = mysqli_query($db, $query);// or die('Error querying database!');
  if ($result === false) { die(mysqli_error($db)); }
  echo 'Thanks for Posting! Your request will be addressed as soon as possible!';
}

What is wrong with this code? The leaving and returning columns are of type- DATETIME. is that causing a problem?

<form method="post" action="" class="forms">
        Type: <select name="x" required>
          <option value="0">Leave</option>
          <option value="1">Other</option>
        </select>
        <input type="text" name="reason" placeholder="Reason" required><br>
        <input type="datetime-local" name="leaving"><br>
        <input type="datetime-local" name="returning"><br>
        <input type="submit" value="Request Permission" name="permission">
      </form>

Your question is not very clear, but if you want to insert the date to those fields, you can set their default values to CURRENT_TIMESTAMP. — mzcoxfde
– mzcoxfde, Commented Apr 9, 2017 at 6:36
@mzcoxfde The above code is giving me a sql syntax error. i think the datetime-local type and datetime column type is causing that... — user7704928
– user7704928, Commented Apr 9, 2017 at 6:44
I have posted the answer. In case you still face any issues, then share the exact error and line number where you are getting it. — Mohammed Akhtar Zuberi
– Mohammed Akhtar Zuberi, Commented Apr 9, 2017 at 6:53
@MohammedAkhtarZuberi You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'returning, user, x) VALUES('gotcha gotcha gotcha gotcha ', '2017-04-06T12:00', '' at line 1...this is the error i am facing — user7704928
– user7704928, Commented Apr 9, 2017 at 7:01
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Apr 9, 2017 at 7:04

Mohammed Akhtar Zuberi · Accepted Answer · 2017-04-09 06:52:17Z

To achieve what you want, follow the below steps.

Instead of having Fields type as "DATETIME" in the DB, have them simple VARCHAR with the length of around 99 in order to store full format date and time easily.
Input Type "datetime-local" is actually not required. Simply use Input Type "date" is OK as you are not capturing the Time.

Once you are ready with the above fixes, amend your PHP code as below. If you want, you can simply copy/paste it as well.

<?php
    include "session.php";

    if(isset($_POST['permission'])) {
        $x = $_POST['x'];
        $reason = $_POST['reason'];
        $leaving = $_POST['leaving'];
        $returning = $_POST['returning'];

        $query = "INSERT INTO permissions (reason, leaving, returning, user, x) VALUES('$reason', '$leaving', '$returning', '$login_session', '$x')";
        $result = mysqli_query($db, $query);// or die('Error querying database!');

        if ($result === false) {
            die(mysqli_error($db));
        }

        echo 'Thanks for Posting! Your request will be addressed as soon as possible!';
    }
?>

Now to retrieve this date value in your required format, use the below code.

$fetched_date = "04-09-2017" //Assuming that this Sunday, April 9, 2017 is the fetched date and stored in "04-09-2017"

Now to convert it to Sunday, April 9, 2017 simply follow the below steps.

$fetched_date_uf = date_create($fetched_date);
$fetched_date_formatted = date_format($fetched_date_uf, 'l, F d, Y');

echo $fetched_date_formatted; //Sunday, April 9, 2017

Refer to this link for PHP Date Time Formats.

Collectives™ on Stack Overflow

How to insert HTML datetime-local input into MYSQL table datetime column using php?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related