Php insert date into mysql

$date=$year."-".$month."-".$day; 
$new_date=date('Y-m-d', strtotime($dob)); 
$status=0;  
$insert_date = date("Y-m-d H:i:s");  
$latest_insert_id=0; 

$insertSql="insert into participationDetail (formId,name,city,emailId,dob,mobile,status,social_media1,social_media2,visa_status,tnc_status,data,gender,insertDate)values('".$formid."','".$name."','".$city."','".$email."','".$new_date."','".$mobile."','".$status."','".$link1."','".$link2."','".$visa_check."','".$tnc_check."','".json_encode($detail_arr,JSON_HEX_APOS)."','".$gender."','".$insert_date."')";

This problem describes the date format for inserting the date into MySQL database. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The date can be stored in this format only. However, it can be used with any time format functions to change it and display it.

When writing a query in MySQL using PHP it’s applicability will be checked on the basis of MySQL itself. So use default date and time format as provided by MySQL i.e. ‘YYYY-MM-DD’

Examples:

DATE: YYYY-MM-DD
Example: 2005-12-26

DATETIME: YYYY-MM-DD HH:MI:SS
Example: 2005-12-26 23:50:30

TIMESTAMP: YYYY-MM-DD HH:MI:SS
Example: 2005-12-26 23:50:30

YEAR: YYYY or YY

MySQL query to create DataBase:

CREATE DATABASE Date_time_example;

Example 1: PHP program to Create database and table

php

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "geeks";

$conn = mysqli_connect( $servername, $username, $password, $dbname );

if ( !$conn ) {

    die("Connection failed: " . mysqli_connect_error());

}

$sql = "CREATE TABLE date_test (

    id INT AUTO_INCREMENT PRIMARY KEY,

    created_at DATETIME

)";

if (mysqli_query($conn, $sql)) {

    echo "Table date_test created successfully";

} else {

    echo "Error creating table: " . mysqli_error($conn);

}

mysqli_close($conn);

?>

Output:

Table date_test created successfully

Example 2: PHP program to insert date into the table.


php

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "geeks";

$conn = mysqli_connect( $servername, $username, $password, $dbname );

if ( !$conn ) {

    die("Connection failed: " . mysqli_connect_error());

}

$sql = "INSERT INTO date_test( created_at ) 

        VALUES( '2018-12-05 12:39:16' );";

if (mysqli_query($conn, $sql)) {

    echo "New record created successfully";

} else {

    echo "Error: " . $sql . "
" . mysqli_error($conn);

}

mysqli_close($conn);

?>

Output:

New record created successfully

Example 3: This example is used to display which row created on 2018-12-05. Use the following query to display result.
The created_at column contains not only date but also time. So it will display error message.

SELECT * FROM date_test WHERE created_at = '2018-12-05';

Output:

(!Important) Wrong Query It returns no rows

Correct Query: To correct it, use the DATE function as follows:

SELECT * FROM date_test WHERE DATE( created_at ) = '2018-12-05';

php

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "geeks";

$conn = mysqli_connect( $servername, $username, $password, $dbname );

if ( !$conn ) {

    die("Connection failed: " . mysqli_connect_error());

}

$sql = "SELECT * FROM date_test

WHERE DATE(created_at) = '2018-12-05'";

$result = mysqli_query( $conn, $sql ); 

if ($result) {

    echo $result;

} 

else {

    echo "Error: " . $sql . "
" . mysqli_error($conn);

}

mysqli_close($conn);

?>

Output:


To get the year, quarter, month, week, day, hour, minute, and second from a DATETIME value, use the functions as shown in the following statement:
HOUR(@dt), MINUTE(@dt), SECOND(@dt), DAY(@dt), WEEK(@dt), MONTH(@dt), QUARTER(@dt), YEAR(@dt);

PHP is a server-side scripting language designed specifically for web development. You can learn PHP from the ground up by following this PHP Tutorial and PHP Examples.