Insert custom time in mysql with php

Question

I couldn't find help anywhere about how to insert a specific time I need to mysql using php.

I tried creating a column type varchar, and submit a regular input value with php $_POST['hour'], but I got a error

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 '1', '7:30pm', '', '', ')' at line 1

I just need to know what type of column I should create and if there's a built-in function to get the time I need when submit, so I can store that hour and minutes, and NOT the current time, as all other posts are about.

My code:

if($_SERVER['REQUEST_METHOD'] == "POST") {

    $days_1 = isset($_POST['day_1']);
    $hours_1 = $_POST['hours_1'];

}

$sql = "INSERT INTO table(day_1, hours_1)";
        $sql.= " VALUES('$day_1', '$hours_1')";

HTML FORM

<select id="hoursMonday" name="hours_1" class="form-group mb-4">
    <option value=""></option>
    <option value="7:30pm">7:30pm</option>
    <option value="8:00pm">8:00pm</option>
</select>

I think you're confusing data storage with data retrieval and display — Strawberry, Mar 21 '19 at 21:54
It's a specific event that happens at some address at one of the two times 7:30pm or 8:00pm, so I want to save those times when creating the event and the location. — Filipe Bicalho, Mar 21 '19 at 22:01
And look at [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — danblack, Mar 21 '19 at 22:17
type = DateTime, and look into the date functions such as `TIME()`, `DAY()` etc. that MySQL has to offer. In PHP you can do `date('Y-m-d H:i:s)` - I avoid any built in time functions (for setting a default) as the DB has it's own timezone settings you would have to mess with to line it up with the timezone of your PHP (probably) and that is just one more configuration you have to remember. Not to mention I think there is only one for the timestamp (in phpmyadmin). There is the `NOW()` function In MySql, however I don't think you can set it as part of a table definition. — ArtisticPhoenix, Mar 21 '19 at 22:55
I figured that if I use htmlentities($time) for special characters, I can store in a varchar type column, but maybe it is not the right way, I will keep trying to find something more appropriate or more specific for time only, without date. — Filipe Bicalho, Mar 23 '19 at 10:46

score 0 · Answer 1 · answered Mar 21 '19 at 23:03

Save it as a DateTime type:

if($_SERVER['REQUEST_METHOD'] == "POST") {

    $created = (new DateTime($_POST['created']))->format('Y-m-d H:i:s');

}

$sql = "INSERT INTO table(created)VALUES(?)"; //prepare your queries

Then when you pull the data use something like

 SELECT TIME(created) AS time_created FROM table WHERE 1

Don't save them as separate pieces as you gain nothing and loose a lot, because of the date functions. Namely you will lose these ~= 25 functions:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

In the above Time returns the H:i:s part of the date.

Insert custom time in mysql with php

1 Answers1