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();

Womanizer

you need to use insert_id; after you executed query with insert, like: if ($stmt->execute()) $image = $stmt->insert_id; – Resurrect

@IlyaBursov so then how do bind t2.id = ? – Womanizer

You need to express your wishes more clear and distinct way – Venatic

@Womanizer do you want to insert into table1 or table2? – Resurrect

You have to get rid of this monster query and explain with plain language what you want to insert, why and where – Venatic

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

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 – Resurrect

@IlyaBursov thanks it works. bzw can I be assured that I always get the last row? – Womanizer

Just read my answer. this query of yours doesn't guarantee that. – Venatic

@Womanizer order by t2.id desc limit 1 sorts in desc order, which means that only last id in table will be selected – Resurrect

What exactly do you mean by "doesn't work"? What exactly is not working in the given snippet? – Octagonal

@NicoHaase you would scarcely get an answer as the OP left this site 7 years ago – Venatic

Answer 1

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

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

Mulligatawny

Answer 2

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

Go on:

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

As simple as that

Venatic

Your answer would be conceptually correct if you had not reversed your table names. – Ulaulah