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

Question

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"]

Maybe you can refer on this old post [here](http://stackoverflow.com/questions/2770600/mysql-select-the-last-inserted-row-easiest-way). — Logan Wayne, Sep 04 '15 at 05:29
http://stackoverflow.com/questions/12600527/accessing-last-inserted-row-in-mysql — Surabhil Sergy, Sep 04 '15 at 05:32
Do you have that field within your table and are you saving that too.. — Narendrasingh Sisodia, Sep 04 '15 at 05:49

score 1 · Answer 1 · answered Sep 04 '15 at 05:43

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.

MrD · Accepted Answer · 2015-09-04T05:52:41.860

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 :)

Thanks, Why this is not secure? : $email= mysqli_real_escape_string($con,$_POST['email']); — xRobot, Sep 04 '15 at 06:09
No there is nothing wrong with `mysqli_real_escape_string`, BUT it is "obsoleted" approach AND what will happen if you forget to call this function (usually this happens to developers). Also by using PDO, it is automatically performed, and that saves you time, efforts, and code maintenance is much easier. — MrD, Sep 04 '15 at 06:49

score 0 · Answer 3 · edited Sep 04 '15 at 05:41

0

Simply run a query after insertion to get the date

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

edited Sep 04 '15 at 05:41

Narendrasingh Sisodia

21,247
6
47
54

answered Sep 04 '15 at 05:37

Prashant Sharma

148
11

score 0 · Answer 4 · edited Sep 04 '15 at 05:41

0

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;

edited Sep 04 '15 at 05:41

Narendrasingh Sisodia

21,247
6
47
54

answered Sep 04 '15 at 05:39

Ravneet

300
1
5

score 0 · Answer 5 · edited Sep 04 '15 at 06:13

0

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

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

edited Sep 04 '15 at 06:13

Saty

22,443
7
33
51

answered Sep 04 '15 at 05:57

Gopinath

1
2

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

5 Answers5

IMPORTANT