Inserting datetime using PHP and MySQL

[ven.ganeva]

Hi people, I wonder if someone can help...

 

I have a database field which is of type datetime. I also have a form which inserts a date into the database using a calender. The format of the date is dd/mm/yy. I need help on how I can

 

1) Record the current time

2) Format both the date and time for correct input into the database

 

Any help would be much appreciated!

[lordfrikk]

<?php
$time = time();
$formatted = date('d/m/Y', $time);
echo $formatted;
//outputs 13/03/2008

/* or this way:

echo date('d/m/Y', time());

*/
?>

 

This way?

[discomatt]

<?php
$curdate = date('d/m/y');

echo $curdate . '<br>';

list($day, $month, $year) = explode('/', $curdate);

echo $day . ' - ' . $month . ' - ' . $year . '<br>';
$stamp = mktime(0, 0, 0, $month, $day, $year);

echo date('Y-m-d', $stamp);
?>

[PFMaBiSmAd]

Much to much slow php code. Do these directly in your query.

 

To insert the current data/time, use the mysql NOW() function in your query -  http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now

 

Assuming that you have already validated the entered dd/mm/yy date, just use the mysql STR_TO_DATE() function to change it into the correct format in an INSERT or UPDATE query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

 

I am amazed at how many lines of slow php code gets used to do something that one statement in a query can accomplish.

[ven.ganeva]

Assuming that you have already validated the entered dd/mm/yy date, just use the mysql STR_TO_DATE() function to change it into the correct format in an INSERT or UPDATE query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

 

So what is the correct format for the insert query? I have this but it doesn't work:

 

INSERT INTO tblProcessEvent (TMProcessID, EventTypeID, EventDate) VALUES (2, 1, STR_TO_DATE('26/03/08', '%m/%d/%Y'))

[PFMaBiSmAd]

Your date is dd/mm/yy, don't you suppose the format string would need to match (you have the month and day reversed.)

[ven.ganeva]

Yeah I realised that after I posted it  but it still isn't working and my code is now:

 

INSERT INTO tblProcessEvent (TMProcessID, EventTypeID, EventDate) VALUES (2, 1, STR_TO_DATE('20/03/08', '%d/%m/%y'))

 

strange...

[PFMaBiSmAd]

Any errors from your query (using an mysql_error() statement) to help pin down the problem?

 

I believe you need single quotes around the value that is returned by the str_to_date() function.

[ven.ganeva]

Tried that with no luck. I'm getting the following error:

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '13/03/08', '%d/%m/%y')')' at line 1

 

So it's obviously a problem with the STR_TO_DATE.

[PFMaBiSmAd]

I made a quick table with INT, INT, DATE fields and just tried your query in post #6 and it works, so something else about what you are doing is not working. Actual query string -

 

$query = "INSERT INTO tblProcessEvent (TMProcessID, EventTypeID, EventDate) VALUES (2, 1, STR_TO_DATE('20/03/08', '%d/%m/%y'))";

 

Results -

 

TMProcessID EventTypeID EventDate

2 1 2008-03-20

 

 

[ven.ganeva]

My field is actually a datetime field as I mentioned in my first post but I am thinking it doesn't need to be.

 

Ok problem solved.

 

Thanks you! Got to love those sql functions 

[PFMaBiSmAd]

The query also works as expected with a DATETIME field -

 

2 1 2008-03-20 00:00:00

 

[ven.ganeva]

Still not working I don't understand why  ???

 

This is my full code

 

$insertSQL = "INSERT INTO tblProcessEvent (TMProcessID, EventTypeID, EventDate) VALUES (".$_POST['TMProcessID'].", ". $_POST['EventTypeID'].", STR_TO_DATE('".$_POST['DPC_date1']."', '%d/%m/%y'))";

 

Still getting this error:

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('28/03/08', '%d/%m/%y'))' at line 1

 

help?