Home Tutorials Php Date PHP date in mysql

PHP date in mysql

Posted in : PHP Posted on : January 28, 2011 at 6:39 PM Comments : [ 0 ]

In this tutorial you will learn how to PHP date in mysql in a PHP web application .

PHP date in mysql

In this tutorial you will learn how to use PHP date in mysql in a PHP web application . In the mysql date or date/time value store in database following data types :

DATE-format :: "YYYY-MM-DD"
DATETIME-format :: "YYYY-MM-DD HH:MM:SS"
TIMESTAMP-format :: "YYYY-MM-DD HH:MM:SS"
YEAR-format :: "YY or YYYY"

So PHP date change similar mysql date format

<?php
$date1=date('Y-m-d');
$date2=date('Y-m-d H:i:s');
$date3=date('Y-m-d H:i:s');
$date4=date('Y');
$date5=date('y');
?>

In this tutorial you will learn how different date format insert in database .First create 'user' table in 'database' :

CREATE TABLE 'user' (
'user_id' int(11) NOT NULL AUTO_INCREMENT,
'user_name' varchar(200) DEFAULT NULL,
'date' date NOT NULL,
'datetime' datetime NOT NULL,
'timestamp' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
'year' year(4) NOT NULL,
PRIMARY KEY ('user_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Again use two php files "config.php" and "php-date-in-mysql.php" . The "config.php" use for connection in database and "php-date-in-mysql.php" use for insert data in 'user' table . The code of "config.php" given below :

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'database';

$con = mysql_connect($dbhost,$dbuser,$dbpass)or die("Unable to connect to MySQL");
mysql_select_db($dbname, $con)or die("Could not select database ");
?>

And "php-date-in-mysql.php" code given below :

<?php
include('config.php');

$date1=date('Y-m-d');
$date2=date('Y-m-d H:i:s');
$date3=date('Y-m-d H:i:s');
$date4=date('Y');
$date5=date('y');
$sql="INSERT INTO user (user_name, date, datetime,timestamp,year)
VALUES
('User Name','$date1','$date2','$date3','$date4')";
if (!mysql_query($sql,$con))
{
die('Error in insert data : ' . mysql_error());
}
echo "one row insert in user table.";
mysql_close($con);
?>

Similar you can perform another mysql operation (select, delete and update).

Output :

When run "php-date-in-mysql.php" display output as :