Inserting date value into MySQL

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.

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();
}

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.

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

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

I think you'll want STR_TO_DATE()

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

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

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());
}