mysqli last insert id

Question

I would like to associate the image with firstname, lastname...how can I retrieve the last rowand use it to insert to the other table? I tried $image = $mysqli->insert_id; then binding but it doesn't work. Can someone help me out?

 $image = $mysqli->insert_id;//this should come from table2
 $stmt = $mysqli->prepare("
  insert into table1 (username, firstname, lastname, image) 
  select ?,?,?,image from table2 t2 where username = ? and  t2.id = ? 
   ");
 $stmt->bind_param('sssss', $username, $fname, $lname, $username, $image);
 $stmt->execute();

you need to use `insert_id;` after you executed query with insert, like: `if ($stmt->execute()) $image = $stmt->insert_id;` — Iłya Bursov, Nov 02 '13 at 02:21
You have to get rid of this monster query and explain with plain language what you want to insert, why and where — Your Common Sense, Nov 02 '13 at 02:28
@YourCommonSense after I get the last row from table2 I would like to insert it to table1. That is all I need — user2926655, Nov 02 '13 at 02:32
ok, so you need to change your query to `insert into table1 (username, firstname, lastname, image) select ?,?,?,image from table2 t2 where username = ? order by t2.id desc limit1` — Iłya Bursov, Nov 02 '13 at 02:34
@IlyaBursov thanks it works. bzw can I be assured that I always get the last row? — user2926655, Nov 02 '13 at 02:41
Just read my answer. this query of yours doesn't guarantee that. — Your Common Sense, Nov 02 '13 at 08:47
@user2926655 `order by t2.id desc limit 1` sorts in desc order, which means that only last id in table will be selected — Iłya Bursov, Nov 02 '13 at 16:05
What **exactly** do you mean by "doesn't work"? What **exactly** is not working in the given snippet? — Nico Haase, Nov 04 '20 at 10:10
@NicoHaase you would scarcely get an answer as the OP left this site 7 years ago — Your Common Sense, Nov 04 '20 at 10:11

score 29 · Answer 1 · edited Sep 27 '15 at 15:05

mysqli::$insert_id -- mysqli_insert_id — Returns the auto generated id used in the last query, Example:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE myCity LIKE City");

$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

/* drop table */
$mysqli->query("DROP TABLE myCity");

/* close connection */
$mysqli->close();

output

New Record has id 1.

Reference

PHP mysqli_insert_id: http://www.php.net/manual/en/mysqli.insert-id.php

copied from PHP.Net... can you please explain the need of creating a new table? — Veer Shrivastav, May 11 '15 at 13:28

score 15 · Answer 2 · answered Feb 01 '17 at 09:59

15

First of All you need to create auto_increment field in you ID

Then You can used $last_id = mysqli_insert_id($conn);

answered Feb 01 '17 at 09:59

Rathod Paresh

171
1
4

score 1 · Answer 3 · answered Nov 02 '13 at 03:05

1

after I get the last row from table2 I would like to insert it to table1. That is all I need

Go on:

insert into table 1 with simple regular insert query
get last insert id
insert into table 2 with simple regular insert query

As simple as that

answered Nov 02 '13 at 03:05

Your Common Sense

152,517
33
193
313

Your answer would be conceptually correct if you had not reversed your table names. – MLK.DEV Jun 05 '14 at 08:49

mysqli last insert id

3 Answers3

Linked

Related