CODEWITHSUNDEEP
phpmysql
xRobot
xRobot

Reputation: 26567

Is there a way to get the date of the last inserted row?

This is my table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `mail` varchar(50) NOT NULL UNIQUE,
  `password` varchar(30) NOT NULL,
  `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

This is my query in the php code:

$query = "INSERT INTO `users` (`email`,`psw`) VALUES ('".$email."','".$psw."')";
  $_SESSION["id"] = mysqli_insert_id($db_con);
  // get the date of this row and put it in $_SESSION["regdate"]

Upvotes: 1

Views: 1797

Answers (5)

Gopinath
Gopinath

Reputation: 1

You can do like below, make sure id should be auto increment

SELECT reg_date FROM users ORDER BY id DESC LIMIT 0,1

Upvotes: 0

MrD
MrD

Reputation: 2481

Yes, there is. Change your insert statement slightly: 

$currentDate=date("Y-m-d H:i:s");//Get current date
$query = "INSERT INTO `users` (`email`,`psw`,`reg_date`) VALUES ('".$email."','".$psw."', '.$currentDate.')";//Save date, email and password to database
$_SESSION["id"] = mysqli_insert_id($db_con);//Get ID of the last row
$_SESSION["regdate"] = $currentDate;//Save it in session

IMPORTANT

Although you are searching how to insert current date in database and $_SESSION array, you have much bigger problem. Your insert statement is vulnerable to the SQL Injection attack, meaning your application has security issue. Instead of using string concatenating, use PDO classes and something what is called prepared statements.

Example: 

$currentDate=date("Y-m-d H:i:s");//Get current date
$sql = "INSERT INTO users SET email=?, psw=?, reg_date = ?";
$q = $conn->prepare($sql);
$q->execute(array($email, $psw, $currentDate));
$_SESSION["id"] = mysqli_insert_id($db_con);//Get ID of the last row
$_SESSION["regdate"] = $currentDate;//Save it in session

It will work :)

Upvotes: 1

kamal pal
kamal pal

Reputation: 4207

you may simply use mysqli_insert_id and select the row you've inserted

$query = "INSERT INTO `users` (`email`,`psw`) VALUES ('".$email."','".$psw."')";
mysqli_query($db_con, $query);
$_SESSION["id"] = mysqli_insert_id($db_con);

$query = "SELECT reg_date FROM `users` WHERE id='{$_SESSION["id"]}'";
$result = mysqli_query($db_con, $query);

$row = mysqli_fetch_array($result);
print_r($row);

or you can insert current datetime, this way you do not have to query again to get the timestamp.

$dt = date('Y-m-d H:i:s');
$query = "INSERT INTO `users` (`email`,`psw`, reg_date) VALUES ('".$email."','".$psw."', '".$dt."')";
mysqli_query($db_con, $query);
echo $dt; // last date you have inserted.

Upvotes: 1

Prashant Sharma
Prashant Sharma

Reputation: 148

Simply run a query after insertion to get the date

query = "SELECT `reg_date` FROM `users` WHERE `id` = $_SESSION["id"]"

Upvotes: 0

Rav
Rav

Reputation: 300

Get id for last inserted row

$last_id = mysqli_insert_id($db_con);

Then, execute select query

select reg_date from users where id = $last_id;

Upvotes: 0

Related Questions