Input into MySQL - Date field

[Guest]

Hi everyone,

I've got a simple form with a date input field, the data goes into a MySQL database, column formatted to 'DATE'.

GetSQLValueString($_POST['projstart'], "date"),

However, when I try to submit the form,it tells me:

Incorrect date value: '04/08/2009' for column 'projstart' at row 1

I'm guessing this is due to '04/08/2009', being viewed as a string, not a date.

How do I change this to input into the database, as a DATE.???

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO tbl_projects (projtitle, projstart, projdue, FK_custid, FK_langsid, projcontact, projanalysis, projtype, projstatus, projurl) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['projtitle'], "text"),
                       GetSQLValueString($_POST['projstart'], "text"),
                       GetSQLValueString($_POST['projdue'], "text"),
                       GetSQLValueString($_POST['FK_custid'], "int"),
                       GetSQLValueString($_POST['FK_langsid'], "text"),
                       GetSQLValueString($_POST['projcontact'], "text"),
                       GetSQLValueString($_POST['projanalysis'], "text"),
                       GetSQLValueString($_POST['projtype'], "text"),
                       GetSQLValueString($_POST['projstatus'], "text"),
                       GetSQLValueString($_POST['projurl'], "text"));

[David_Powers]

I'm guessing this is due to '04/08/2009', being viewed as a string, not a date.

No, a date is inserted into MySQL as a string, so that's not your problem. MySQL stores dates in one format only: the ISO-recommended format YYYY-MM-DD. You need to reformat the date as 2009-08-04 (assuming you mean 4 August),

[Guest]

Hi David,

I thought as much.

I can reformat my calendar picker to become: yyyy-mm-dd, which gets accepted by MySQL.

Is there a way of keeping the calendar picker as dd-mm-yyyy, so it looks correct when people are entering the dates, then converting it, either through PHP or a MySQL statement, which enters it in the database as yyyy-mm-dd?

Thanks for your help

[jon8 · Accepted solution]

Before the date gets inserted/updated, use this function:

function mysqlDate($date) {

$parts = explode('/',$date);

return "$parts[2]-$parts[1]-$parts[0]";

}

$_POST['date'] = mysqlDate($_POST['date']);

[Guest]

Sorry for being dumb, but where in my insert query, would it go, where the two date fields are projstart and projdue?

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO tbl_projects (projtitle, projstart, projdue, FK_custid, FK_langsid, projcontact, projanalysis, projtype, projstatus, projurl) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['projtitle'], "text"),
                       GetSQLValueString($_POST['projstart'], "text"),
                       GetSQLValueString($_POST['projdue'], "text"),
                       GetSQLValueString($_POST['FK_custid'], "int"),
                       GetSQLValueString($_POST['FK_langsid'], "text"),
                       GetSQLValueString($_POST['projcontact'], "text"),
                       GetSQLValueString($_POST['projanalysis'], "text"),
                       GetSQLValueString($_POST['projtype'], "text"),
                       GetSQLValueString($_POST['projstatus'], "text"),
                       GetSQLValueString($_POST['projurl'], "text"));

  mysql_select_db($database_conndb2, $conndb2);
  $Result1 = mysql_query($insertSQL, $conndb2) or die(mysql_error());

Thanks,

Samuel

[Guest]

I added your function, but got this error when submitting the form:

Incorrect date value: '--' for column 'projstart' at row 1

[Guest]

I added your function, but got this error when submitting the form:

Incorrect date value: '--' for column 'projstart' at row 1

[Guest]

Hey man,

Just tested the function, which worked, thanks!

Only problem is, I've repeated the function for each date input on the form, but some of them are not required, and now I cannot submit the form.

How do I correct this?

[jon8]

insert function after if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

you could wrap the function in an if else statement so that it checks whether the value is null or not before running the function.

if ($_POST['date'] != "") {

function mysqlDate($date) {

$parts = explode('/',$date);

return "$parts[2]-$parts[1]-$parts[0]";

}

$_POST['date'] = mysqlDate($_POST['date']);

}