Inserting Date from form using PHP Mysql

Question

I can not seem to get the date to insert from my form into my data base. Please help!

here is my form

echo '

<h1>Enter New Report</h1>
<form action="submitreport.php" method="post"
enctype="multipart/form-data">

 Date: <input type="text" name="Date" value='.$myDate.' /><br />
 Report:<br><TEXTAREA NAME=Report ROWS=4 COLS=40></TEXTAREA><br />
 <input type="submit" name="submit" value="Submit" />
 </form>

 ';
 ?> 

Here is what I have written to submit it to the database

$insertdate = trim($_POST['Date']);

$insertdate = mysql_real_escape_string($insertdate);
    $insertdate = date('m/d/Y', $insertdate);

echo $insertdate;


mysql_query("INSERT INTO `Reports`(`Date`, `Report`) VALUES ('$insertdate','$_POST[Report]')") or die("load1 -" .  mysql_error());


mysql_close();

echo "Thank You!" . " -" . "<a href='index.php' style='text-decoration:none;color:#ff0099;'> Return Home</a>";
?>

Solution 1

I'm only guessing, but your Date field in the database is either of type date or datetime. The format of these are YYYY-mm-dd. Try changing your code to:

$insertdate = date('Y-m-d', strtotime($_POST['Date']));

Also, you aren't converting the date to a timestamp before formatting it. I've added strtotime() on the data here as well.

Solution 2

PHP's date needs a timestamp as second parameter. If $insertdate isn't one it won't work. You can though get a timestamp from a string using PHP's strtotime() function.

Also, the value in your sql statement must be formatted according to the type used in your mysql database, for example date, datetime or timestamp

Comments

[andrewsi]

What format is the Date field in your database?

[rsz]

Where is the $myDate variable defined?

[haynar]

does $myDate contain timestamp? otherwise date function will return false or raise a WARNING message. use strtotime when passing parameter to date

[cHao]

What half-assed book/site taught you mysql_query? It's been (at least unofficially) deprecated for years now. Check out PDO and mysqli.

[tereško]

Please, don't use mysql_* functions to write new code. They are no longer maintained and the community has begun deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide which, this article will help you. If you pick PDO, here is good tutorial.

[Steven]

I was using the date('m/d/Y') function just to put todays date in the form however I needed it to be allowed editing.

[Steven]

I still do not know why the default of mysql is Y-m-d but changing the format of my date and using the strtotime() function fixed the problem.

[newfurniturey]

It's the same as what you posted in your answer (except all in one line), so I should hope it works too =P

[newfurniturey]

Actually, the second parameter to date() is optional - without one, it will give you the current date/time. If you specify one it needs to be a Unix-timestamp.