How to insert the value of input time to database table?

Question

I'm currently working on an attendance system where user should specify his time in and time out using an <input type="time">, but then when I try to submit the form it keeps sending me an error

Here's a snippet of HTML code

<tr>
    <td><input type="text" id="day1" name="day1" class="date-field" readonly></td>
    <td><input type="time" id="day1-in" name="day1-in" class="time-field" title="Ex: 08:00 AM"></td>
    <td><input type="time" id="day1-out" name="day1-out" class="time-field" title="Ex: 05:00 PM"></td>
    <td><input type="checkbox" name="ot-1-a" id="ot-1-a" value="1"></td>
    <td><input type="checkbox" name="ot-1-b" id="ot-1-b" value="1"></td>
    <td><input type="checkbox" name="ot-1-c" id="ot-1-c" value="1"></td>
    <td><input type="text" id="day1-hrs-served" class="dtr-field" name="day1-hrs-served" readonly></td>
    <td><input type="text" id="day1-reg-ot" class="dtr-field" name="day1-reg-ot" readonly></td>
    <td><input type="text" id="day1-reg-hol-ot" class="dtr-field" name="day1-reg-hol-ot" readonly></td>
    <td><input type="text" id="day1-spe-hol-ot" class="dtr-field" name="day1-spe-hol-ot" readonly></td>
    <td><input type="text" id="day1-reg-hol-ot-ot" class="dtr-field" name="day1-reg-hol-ot-ot" hidden readonly></td>
    <td><input type="text" id="day1-spe-hol-ot-ot" class="dtr-field" name="day1-spe-hol-ot-ot" hidden readonly></td>
</tr>

Snippet of my PHP code

$day1 = mysqli_real_escape_string($conn, $_REQUEST['day1']);
$day1In = mysqli_real_escape_string($conn, $_REQUEST['day1-in']); 
$day1Out = mysqli_real_escape_string($conn, $_REQUEST['day1-out']);
$day1HrsServed = mysqli_real_escape_string($conn, $_REQUEST['day1-hrs-served']);
$day1RegOT = mysqli_real_escape_string($conn, $_REQUEST['day1-reg-ot']);
$day1RegHol = mysqli_real_escape_string($conn, $_REQUEST['day1-reg-hol-ot']);
$day1SpeHol = mysqli_real_escape_string($conn, $_REQUEST['day1-spe-hol-ot']);
$day1RHOT = mysqli_real_escape_string($conn, $_REQUEST['day1-reg-hol-ot-ot']);
$day1SHOT = mysqli_real_escape_string($conn, $_REQUEST['day1-spe-hol-ot-ot']);

$daily = "INSERT INTO daily_records (ref_dtr, record_date, record_in, record_out, hrs_served, reg_ot, reg_hol, spe_hol, rh_ot, sh_ot) VALUES ($dtr_ref, $day1, $day1In, $day1Out, $day1HrsServed, $day1RegOT, $day1RegHol, $day1SpeHol, $day1RHOT, $day1SHOT)";

if(mysqli_query($conn, $daily)){
   echo "Records added successfully.";
} 
else{
   echo "ERROR: Could not able to execute $daily. " . mysqli_error($conn);
}

when I submit this form it throws an error

ERROR: Could not able to execute INSERT INTO daily_records (ref_dtr, record_date, record_in, record_out, hrs_served, reg_ot, reg_hol, spe_hol, rh_ot, sh_ot) VALUES (5d7f686865d1a, 2019-09-01, 08:00 AM, 05:00 PM, 8.00, 0.00, 0, 0, 0, 0). 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 ':00 AM, 05:00 PM, 8.00, 0.00, 0, 0, 0, 0)' at line 4

I suspect that the problem is because of the input time, currently the record_in and record_out datatype is time and I also tried to change it to varchar but it still throws the error. Can someone point out my mistake or any suggestion to improve my code, thanks.

UPDATE Because the input time I'm using is in 12 hr format, I used

$day1InCon = date('H:i', strtotime($day1In));
$day1OutCon = date('H:i', strtotime($day1Out));

to convert it to 24 hr format. There seems to be a problem in inserting 12 hr format in the database but I'm not sure.

*':00, 18:00, , , , 8.00, 1.00, 0, 0, 0, 0)'* I see commas with nothing between, it may be a part of your issue. — Kévin Bibollet, Sep 16 '19 at 10:20
Your code is vulnerable to SQL injection. You should use prepared statements. https://stackoverflow.com/a/60496/1839439 — Dharman, Sep 16 '19 at 10:32
@KévinBibollet - I forgot to update the thrown error, those are from the checkbox values, I also tried removing them for the meantime but i still got the error — Haru, Sep 16 '19 at 10:51

score 0 · Accepted Answer · answered Sep 16 '19 at 10:24

0

In your SQL values you must use quote(') for strings parameter.

Example:

$query = "INSERT INTO table (name, surname, address) VALUES ( '" . $name . "', '" . $surname . "', '" . $address . "' )";

answered Sep 16 '19 at 10:24

Stefano Pascazi

363
1
11

After formatting the hours, I still got an error and then I used single quote in `$dtr_ref` and it worked. Thanks – Haru Sep 16 '19 at 11:55

How to insert the value of input time to database table?

1 Answers1