Insert date from php in mysql?

Question

please give me right solution.
I want to insert date in mysql.
mysql php form is like day month year, the three fields are stored in single dob field.
In the dob field cannot be stored mysql database.
Further, my mysql database dob field under the datatype is 'date' and mentioned not null.
But, i cannot stored dob field. when arise the problem the error message is "Could not execute mysql query!Incorrect date value: '1947-August-15' for column 'dob' at row 1"
What i do? Please give me a solution.

Answer 1

Best Answer - Chosen by Asker

The problem you have is that MySQL is not smart enough to recognize the date in the format you supply (YYYY-Month Name-DD). So we must convert the date from your format to the MySQL standard format of YYYY-MM-DD.

To accomplish this task, we build a function called convertKarthikDate. This function will take a date like 1947-August-15 and convert it to a MySQL date (1947-08-15). If the supplied date is not valid the function returns false.

You can copy and paste this function into your code and apply it to the date so your SQL code will work.


<?php
function convertKarthikDate($tmpDate){

/*
* This function takes a user supplied date in the format of
* YYYY-MonthName-DD and converts it to MySQL format (YYYY-MM-DD).
* If the supplied date is not valid the function returns false.
*
* MonthName can be abbreviated (Aug for August, Sep for
* September, etc.) or the full month name.
*/

// *** Break the date into separate parts.
$aDate_parts = preg_split("/[\s-]+/", $tmpDate);
$year = $aDate_parts[0];
$month = $aDate_parts[1];
$day = $aDate_parts[2];

// *** Rebuild date in US English date format.
$usDate = "$month $day, $year";

// *** Convert to timestamp.
$us_seconds = strtotime($usDate);

// *** Check for valid timestamp.
if (!$us_seconds) {return false;}

// *** Convert from US English date to MySQL date format.
$tmpMySqldate = date("Y-m-d", $us_seconds);

// *** Break the MySQL date into separate parts
$bDate_parts = preg_split("/[\s-]+/", $tmpMySqldate);
$year = $bDate_parts[0];
$month = $bDate_parts[1];
$day = $bDate_parts[2];

// *** Test the MySQL date to verify it is valid.
$isDate = checkdate($month, $day, $year);

if ($isDate) {

// *** Date is valid.
return $tmpMySqldate;

} else {

// *** Date is NOT valid.
return false;

}

} // *** End function.

// *** Begin main section.
if (isset ($_POST['bday'])) {

// *** Date was submitted.

// *** Assign user date to variable.
$userSuppliedDate = $_POST['bday'];

// *** Convert to MySQL format.
$mySqlDate = convertKarthikDate($userSuppliedDate);

if($mySqlDate === false) {
echo("<p>Date format error.");
} else {
echo("<p>The MySQL equivalent date of <b>$userSuppliedDate</b> is <b>$mySqlDate</b>.</p>");
}

}
?>
<p>Enter the date of your Birthday (YYYY-MonthName-DD)</p>
<form action="" method="POST">
<p><input type="text" name="bday"></p>
<p><input type="submit" name="cmdSubmit" value="Submit" /></p>
</form>

Source(s):

PHP strtotime function:
http://www.php.net/manual/en/function.st…

PHP date function:
http://www.php.net/manual/en/function.da…

PHP checkdate function:
http://us3.php.net/checkdate

Asker's Rating:

Asker's Comment:

very useful example. Thanks for you

Answer 2

Make your date in the form 1947-09-15, and it will store fine. MySQL doesn't recognise the 'August'