PHP: Inserting date into a table

Question

My table "list" contains one field: "exam_date" of type "datetime". Dates in that table are formatted by the following structure: "2014-08-31 21:45:47" (yyyy-mm-dd hh:ii:ss). In my input form date is assigned the following way:

<input id="exam_date" name="exam_date" type="datetime" placeholder="<?php echo date('d-m-y H:i:s'); ?>">

In order to see the value assigne i added to my code:

$myDate = trim($_POST['exam_date']);
echo trim($_POST['exam_date']);

On the display i see the date inserted by the following structure:31-08-14 21:45:47. My code to insert the new value to the table is:

<?php
date_default_timezone_set('xxx');
$dbcon = @mysqli_connect ('xxx', 'xxx', 'xxx', 'test') OR die ('Could not connect to MySQL: ' . mysqli_connect_error () ); 
mysqli_set_charset($dbcon, 'utf8');
if (isset($_POST['exam_date']))
{
 $myDate = trim($_POST['exam_date']);
 echo $myDate ;
 $q = "INSERT INTO list (exam_date) VALUES ($myDate)"; 
 $result = @mysqli_query ($dbcon, $q); 
 if($result)
 {
   echo "record added";
 }
 else
 {
   echo "no record added !";
 }
}
?>
<form action="add_date.php" method="post">
<input id="exam_date" name="exam_date" type="datetime" placeholder="<?php echo date('y-m-d H:i:s'); ?>">
<input id="submit" type="submit" name="submit" value="Register">
</form>

When I run it i get: "no record added" and when I check the table I see that no record was added indeed. I belive that the reason for not able to add a record is the difference in date structures: that of the table and that of the input. Can I change the date structure in the table to "dd-mm-yy hh:ii:ss"? Is there a way to convert the input structure from "dd-mm-yy hh-ii-ss" to "yyyy-mm-dd hh:ii:ss"? Thanks !

possible duplicate of [Format date dd/mm/yyyy to yyyy-mm-dd PHP](http://stackoverflow.com/questions/15676344/format-date-dd-mm-yyyy-to-yyyy-mm-dd-php) — Thusitha Thilina Dayaratne, Aug 17 '14 at 12:01
http://stackoverflow.com/questions/15676344/format-date-dd-mm-yyyy-to-yyyy-mm-dd-php duplicate — Thusitha Thilina Dayaratne, Aug 17 '14 at 12:01
If you don't suppress errors (`mysqli_query` instead of `@mysqli_query`) you might be able to see why (; — Francisco Presencia, Aug 17 '14 at 12:05
what about quoting? `"INSERT INTO list (exam_date) VALUES ('$myDate')"` or better to use `mysqli::real_escape_string` — AHHP, Aug 17 '14 at 12:09
This is vulnerable to SQL injection, if no ones answered by the time I get home I'll post an answer — CodeX, Aug 17 '14 at 12:12

score 1 · Accepted Answer · answered Aug 17 '14 at 17:24

This code is much safer as it uses prepared statements http://php.net/manual/en/mysqli.prepare.php.

Never trust user inputted variables!

Notice the name="" on the submit button, we are checking if the form has been submitted

Always close your forms inputs properly />

Timezones.. https://www.youtube.com/watch?v=-5wpm-gesOY

If you want to INSERT a user inputted datetime try this:

add_date.php:

<?php
date_default_timezone_set('xxx');
$link = mysqli_connect("localhost", "my_user", "my_password", "db_name");
mysqli_set_charset($link, 'utf8');
if (isset($_POST['register'])) {

    $myDate = $_POST['exam_date'];

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    if ($stmt = mysqli_prepare($link, "INSERT INTO list (exam_date) VALUES (?)")) {

        /* bind parameters for markers */
        mysqli_stmt_bind_param($stmt, "s", $myDate);

    /* execute query */
    if(mysqli_stmt_execute($stmt)) {
        echo "record added";    
    }
    else {
        echo "no record added !";   
    }
        /* close statement */
        mysqli_stmt_close($stmt);
    }
    /* close connection */
    mysqli_close($link);
}

The Form:

<form action="add_date.php" method="post">
<input id="exam_date" name="exam_date" type="datetime" value="<?php echo date('y-m-d H:i:s'); ?>" />
<input id="submit" type="submit" name="register" value="Register" />
</form>

Thanks, Nothing showed up on my display having ran your code."if ($stmt = mysqli_prepare($link, "INSERT INTO list (exam_date) VALUES ($myDate)")) " didn't yield "true" I think... — deotpit, Aug 18 '14 at 18:25
You don't use the variable in your query, the ? Is your variable and you bind it.. — CodeX, Aug 19 '14 at 09:40

arif_suhail_123 · Answer 2 · 2014-08-17T14:34:32.633

0

please tell me why you are using

date_default_timezone_set()

if you dont need that than just simply run this query 
if ($_SERVER['REQUEST_METHOD']=="POST")
{
    $query="INSERT INTO id (date) Values (now())";
    $result=  mysqli_query($con, $query)or die(mysqli_error($con));
if($result)
{
    echo 'success';
}
else {
    echo 'failed';
}

edited Aug 17 '14 at 14:34

answered Aug 17 '14 at 12:25

arif_suhail_123

2,509
2
12
16

Forgot to remove the @ but it behaves the same after removing @. – deotpit Aug 17 '14 at 13:40
date_default_timezone_set is for default date value. I cannot insert "now()" because this is a page to calculate monthly hours made. The user may not know how to assign start date and hour to end date+hours as well as I dont know at this very moment, so I help by giving default date+hour so all the user will have to do is changing a given date. How to insert date to the table I dont know. What you suggested works fine only I don't understand it.. – deotpit Aug 17 '14 at 14:57

score 0 · Answer 3 · answered Aug 18 '14 at 18:31

Thank you all who tried to help me. I found this code on "youtube":

<!doctype html>
<html lang=en>
<head>
    <title>Insert Date</title>
    <meta charset=utf-8>
</head>
<body>
<?php
    $raw = "2014 08 18 17 45 00"; 
    $xplod = explode(' ', $raw);
    print_r($xplod);
    $string=$xplod[0].'-'.$xplod[1].'-'.$xplod[2].' '.$xplod[3].':'.$xplod[4].':'.$xplod[5];
    echo "<br/>$string";
    $date = date("y-m-d H:i:s", strtotime($string));
    echo "<br/> $date";
    $dbcon = @mysqli_connect ('xxx', 'xxx', 'xxx', 'test') 
        OR die ('Could not connect to MySQL: ' . mysqli_connect_error () ); 
    mysqli_set_charset($dbcon, 'utf8');
    $q = "INSERT INTO list VALUES ('$date')"; 
    $result = @mysqli_query ($dbcon, $q); 
    if ($result)
    {
        echo "record added";
    }
    else
    {
        echo "record not added";
    }
?>
</body>
</html>

Which is satisfactory for a beginner such as I am.

This code is open to SQL injection please take a look at my answer again — CodeX, Aug 19 '14 at 09:41
Undoubtedly the ultimate answer to my question. I interpreted my code to your code's pattern and it worked. Thanks a lot ! I'll also watch that youtube video you added for timezone. — deotpit, Aug 20 '14 at 06:58

PHP: Inserting date into a table

3 Answers3