How do i convert data type to "datetime"?

PHP
1

Hi,
A “form” within an “HTML” page collects data from users. The data I refer to is called “starttime” and “enddtime” and it should come in
a format such as “2013-07-23 10:00:00”.
In order to process the HTML data I created the following PHP file:


<?php
$con=mysqli_connect("localhost","root","root","db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="INSERT INTO June2013 (remark, startdate, enddate,interval_time,institution, company)
VALUES
(
 '$_GET[Remark]',
 '$_GET[startDate]',
 '$_GET[endDate]',
 TIMEDIFF('$_GET[endDate]','$_GET[startDate]'),
 '$_GET[Institution]',
 '$_GET[company]'
 )";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
echo "1 record added";
?>

For values: startDate=“2013-07-23 10:00:00”, endDate=“2013-07-23 16:00:00” I got the value: 9999.99.
Why didn’t I get 6?!
Mayby the value from the HTML page came in a format different then “DATETIME”?
So, how do I convert data which comes from an HTML file (through a FORM) to datetime?
Thanks

2

Hi deotpit,

What data type is your interval_time column? The TIMEDIFF function is going to return a TIME value, so the column needs to be of the same type.
Although it doesn’t relate to your question, you really want to be checking/escaping your $_GET values before you insert them into the DB.

3

Hi fretburner,
Good to see you again :slight_smile: .
Intreval time column in my mysql table is "DECIMAL(6,2).
The $_GET escaping value is for me to see that the form sends the value I want.
Anyway: Why with:


$sql="INSERT INTO June2013 (remark, startdate, enddate,interval_time,institution, company)
VALUES
(
 '$_GET[Remark]',
 '$_GET[startDate]',
 '$_GET[endDate]',
 TIMEDIFF('$_GET[endDate]','$_GET[startDate]'),
 '$_GET[Institution]',
 '$_GET[company]'
 )";

I get


TIMEDIFF('$_GET[endDate]','$_GET[startDate]'),

9999.99 when enddate was inserted as: “2013-07-23 16:00:00” and startdate: “2013-07-23 10:00:00”? if that is its date
value, how do i convert it into “DATETIME”?. I think my problem is convering values from HTML file inyo datetime and integers. Is there a way to do so with PHP?

4

That’s the problem, the column needs to be of the TIME type to store the output from the TIMEDIFF function.

5

Thanks a lot fretburner. I’ll make the change and see if it works. Both cases I’ll let you know (assuming you’re interested :slight_smile: )

6

Absolutely, it’s always nice to hear when things work out, and not just when they don’t. :slight_smile:

7

Hi fretburner,
I’m late with my response because I was busy trying more and more solutions to the above problem.
It turns out that there’s only one solution suitable for me and that is the solution you suggested.
There maybe more solutions (I fount lots of then in Stack overflow) but it didn’t suit the wide range of my problem.
So i’ll have to cordially thank you again and maybe that will be an explanation why I don’t use google search’s solution.
In sitepoin I get a more “personal” advice. Google search’s suggestions are good in general but not if one’s background is different then the general, so to speak.
Thank you and hope I can enjoy more of your assistance in the future. I’m afraid I’ll need it…