Insert data into table and immediately select ID number of the row

Question

I have a MYSQL table called 'Buyers'. I am taking form data and inserting it into the table on a page called insert.php.

Now, along with the column names for the form fields, I have an Auto-increment ID column in the table. What I want is; once I send the form data to the table, I want to then execute some SQL to get the ID number for the row into which I just inserted the data into.

E.g, I have the SQL:

INSERT INTO Buyers (name, email, job) VALUES ('$_POST[name]', '$_POST[email]', '$_POST[job]');

This will create a row in the 'Buyers' table with the data from form fields 'name', 'email' and 'job'.

This row will have an ID number generated by Auto Increment.

How, then, can I then select that exact ID number? Is there some way to select the most recent row in the table, since that is the row which contains the ID number I want?

what you are using `mysqli_*` or `PDO`? because both have inbuilt function like `last_inserted_id` to get the latest id just after insertion. Other wise `SELECT id from buyers ORDER BY id DESC LIMIT 1` — Alive to die - Anant, Jul 30 '16 at 09:06
@Anant:I think your solution will be worked for him, am observing you from few days. your answers are very good in manner.thank you champ. — Vamsi Abbineni, Jul 30 '16 at 09:57
@VamsiAbbineni thanks. please maintain this curtsy when you are posting answers and comments. I hope you got me what i want to say. thanks — Alive to die - Anant, Jul 30 '16 at 09:59

score 3 · Answer 1 · edited May 23 '17 at 12:32

Mysqli : mysqli_insert_id()

<?php
mysqli_query($con,"INSERT INTO Buyers (name, email, job) VALUES ('$_POST[name]', '$_POST[email]', '$_POST[job]')");
$last_id = mysqli_insert_id($con); 
?>

PDO : PDO::lastInsertId()

<?php
$stmt = $con->prepare("INSERT INTO Buyers (name, email, job) VALUES (?,?,?)");
$stmt->bind_param("sss", $_POST['name'], $_POST['email'], $_POST['job']);
$stmt->execute();
$last_id = $con->lastInsertId();
?>

Assuming $con as connection.

Mysql : mysql_insert_id()

<?php
mysql_query("INSERT INTO Buyers (name, email, job) VALUES ('$_POST[name]', '$_POST[email]', '$_POST[job]')");
$last_id = mysql_insert_id(); 
?>

NOTE: [The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead ]

Thanks @Anant `:)` And Thank You For This Too "*@VamsiAbbineni thanks. please maintain this curtsy when you are posting answers and comments. I hope you got me what i want to say. thanks – Anant*" — Nana Partykar, Jul 30 '16 at 10:37

score 0 · Answer 2 · answered Jul 30 '16 at 09:20

0

SELECT x.*
  FROM messages x
  JOIN 
      ( SELECT from_id, MAX(time_sent) time_sent GROUP BY from_id ) y
    ON y.from_id = x.from_id 
   AND y.time_sent = x.time_sent;

The last part of this puzzle is left as an exercise for the reader.

answered Jul 30 '16 at 09:20

Strawberry

33,750
13
40
57

This will not work if multiple records are being inserted at the same time from different threads. – Erik Jul 30 '16 at 09:23

score 0 · Answer 3 · answered Jul 30 '16 at 09:22

0

Use mysql_insert_id() to retrive ID. More details - http://php.net/manual/en/function.mysql-insert-id.php

answered Jul 30 '16 at 09:22

SKM

71
5

3

Note that this function is deprecated. The correct solution would be mysqli_insert_id() or PDO::lastInsertId(). – Erik Jul 30 '16 at 09:24

Insert data into table and immediately select ID number of the row

3 Answers3