CODEWITHSUNDEEP
phphtmlmysqldatetime
velvetpuma
velvetpuma

Reputation: 103

How to input Date and Time info from a PHP form into a MySQL table in datetime format

I am a bit of a greenback here and looking for some help with some help with a formatting issue that I am having with one of my websites.

I am trying to input Date and Time information inputted by a user into a MYSQL database in the datetime format. However when the user selects their date and time it will be done in the following format: 10:00 PM January 11, 2014

Currently I am trying to input information from a set of drop down menus that are populated from my database into that same MYSQL database;

      <tr>
        <td>Time:</td> 
        <td>
            <select name="hour">
                <?php query_hour() ?>
            </select>                   
          :
            <select name="minute">
                <?php query_minute() ?>
            </select>                   
            <select name="ampm">
                <?php query_ampm() ?>
            </select>                   
        </td>
      </tr>
        <tr>
        <td>Date:</td> 
        <td>
            <select name="month">
                <?php query_month() ?>
            </select>
            <select name="day">
                <?php query_day() ?>
            </select>
        ,
            <select name="year">
                <?php query_year() ?>
            </select>
        </td>
        </tr>

The PHP that I use to input this information into the database is:

// scrub inputs
$day = mysql_real_escape_string($_POST['day'], $conn);
$month = mysql_real_escape_string($_POST['month'], $conn);
$year = mysql_real_escape_string($_POST['year'], $conn);
$minute = mysql_real_escape_string($_POST['minute'], $conn);
$hour = mysql_real_escape_string($_POST['hour'], $conn);
$ampm = mysql_real_escape_string($_POST['ampm'], $conn);

// scrub date
$date = date('Y-m-d G:i:s',strtotime($year.' '.$month.' '.$day.' '.$hour.' '.$minute.' '.$ampm));

// prepare query
$sql = "INSERT INTO my_db.events (eventtime)
 VALUES 
 ('$date')";

 // execute query
mysql_query($sql);
echo $sql;

When I submit the form it succeeds but submits generic time information instead of the user submitted information. Is there anyone that can provide a solution to this problem? I feel as though this is likely a duplicate question, but I have spent several hours perusing this forum as well as others with similar questions and have not found resolve.

Thanks in advance, you have all been a huge help so far and I appreciate the support!

Upvotes: 2

Views: 7938

Answers (2)

velvetpuma
velvetpuma

Reputation: 103

I solved my own issue, but figured I would post the result anyways in case anyone else is having a similar issue and needs some help.

The problem was with the way I was formatting the date when I was scrubbing it. It wasn't in the January 11, 2014 5:00 PM format.

It was:

$date = date('Y-m-d G:i:s',strtotime($year.' '.$month.' '.$day.' '.$hour.' '.$minute.' '.$ampm));

When it should have been:

$date = date('Y-m-d H:i:s',strtotime($month.' '.$day.','.$year.' '.$hour.':'.$minute.' '.$ampm));

Once I separated '.$day.' and '.$year.' with a comma and '.$hour.'and '.$minute.'with a colon it started feeding the proper date.

Thanks for the help guys, it got me on the right track!

Upvotes: 1

rabit
rabit

Reputation: 255

Try with 'Y-m-d H:i:s' instead of G

  • G is 24-hour format of an hour without leading zeros --> 0 through 23
  • H is 24-hour format of an hour with leading zeros --> 00 through 23

For more info: PHP date manual

And to know more about php-mysql datetime: convert php date to mysql format

Upvotes: 2

Related Questions