Inserting date value into MySQL

Question

I currently have a form which takes a date in the format m/d/y - I have then attempted to insert it into a table, but the value in the table reads 0000-00-00. I understand that the value is not being inserted due to the format of the date being inserted.

The problem is, I am unsure on how to change the format so that it is inserted in a format that MySQL will store.

Below is the function that inserts the data into the table:

public function addUser($array) {
  $array['password'] = $this->hashPassword($array['password']);
  $implodeArray = '"'.implode( '","', $array ).'"';
  $sql = ('INSERT INTO user
              (email, password, firstName, lastName, officeID, departmentID, managerID, roleID, username, contractType, startDate, endDate, totalLeaveEntitlement, remainingLeave) 
           VALUES 
              ('.$implodeArray.')');
  echo $sql;
  die();
  mysql_query($sql,$this->_db) or die(mysql_error());
  mysql_close();
}

Due to the use of implodeArray, I cannot format the value of startDate and endDate to match the MySQL DATE format.

Answer 1

Why don't you use similar method to when you hashed the password? So, you just need to add another function to convert your date input into mysql date format:

public function addUser($array) {
    $array['password'] = $this->hashPassword($array['password']);

    $array['startDate'] = $this->mysql_date_format($array['startDate']);
    $array['endDate'] = $this->mysql_date_format($array['endDate']);

    $implodeArray = '"'.implode( '","', $array ).'"';
    $sql = ('INSERT INTO user (email, password, firstName, lastName, officeID, departmentID, managerID, roleID, username, contractType, startDate, endDate, totalLeaveEntitlement, remainingLeave) VALUES ('.$implodeArray.')');
    echo $sql;
    die();
    mysql_query($sql,$this->_db) or die(mysql_error());
    mysql_close();
}

Answer 2

Hmmmmm

I know it looks like its easier to write queries like this (one function generates all your parameters etc etc) but I would STRONGLY advise that you prepare your statements - someone coming along to support your code will thank you for it.

That way you can use NOW(), DATE_DIFF and such other awesomes...

I know that doesn't answer your question but I do feel you should take the time to construct your queries properly - help prevent run time errors/ attacks etc etc.

Answer 3

Not sure on the specifics of your issue, but in general:

$mysql_formatted_date = date("Y-m-d", strtotime($mdy_formatted_date));

Answer 4

I think you'll want STR_TO_DATE()

STR_TO_DATE("%m/%d/%Y") is I think the right format

Answer 5

While both arrays and mysql columns have an implicit order, how do you know they are the same?

It would have been a lot more useful if you'd provided the output of 'echo $sql' rather than all the PHP code - although hte latter highlights a lot of messy programming not least:

• the field order problem
• quoting non-numeric values
• not escaping fields properly
• not trapping / handling errors
• no comments

form which takes a date in the format m/d/y - I have then attempted to insert it

In the case of date fields, quoting is optional depending on the format used for the literal - but it is always ordered as per ISO 8601 - i.e. big endian

Answer 6

public function addUser($array) {
  list($d,$m,$y) = explode("/",$array['startDate']);
  $array['startDate'] = "$y-$m-$d";
  list($d,$m,$y) = explode("/",$array['endDate']);
  $array['endDate'] = "$y-$m-$d";

  $array['password'] = $this->hashPassword($array['password']);

  foreach($array as $key => $value){
    $array[$key] = mysql_real_escape_string($value);
  }
  $implodeArray = implode("','", $array);
  $sql = "INSERT INTO user VALUES (NULL,'$implodeArray')";
  echo $sql;
  die();
  mysql_query($sql,$this->_db) or trigger_error(mysql_error());
}