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 19 · Answer 1 · answered Feb 01 '17 at 09:59

19

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

221
2
4

score 1 · Answer 2 · 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

156,878
40
214
345

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

2 Answers2

Linked

Related