php - Get both insert_id and updated row id in insert_update query -

i have query this:

set @uids = '';  insert tbl1 (name,used,is_active) values (1,0,0),(2,0,0),(24,0,0) on duplicate key update       id = last_insert_id(id)     , used = (select @uids := concat_ws(',', last_insert_id(), @uids))     , used = used+1     , is_active = case when used > 3 1 else 0 end;  select @uids; 

see here figure out way of getting updated row id.

i updated row ids' in @uids if updates rows if row inserted, can't id of that. how both inserted row id , updated row id?

or how execute (select @uids := concat_ws(',', last_insert_id(), @uids)) in insert before on duplicate key... ?

time's short , long

you can't it, because there no way fill @uids while inserting needs select clause , not allowed use select clause within insert statement unless query can transformed insert ... select.

long answer

as long don't try insert mixed values may result in both updating , inserting (which do) there nasty safe way can go with:

set @uids := ''; insert `tbl1` (name, used, is_active)     values (1,0,0),(2,0,0),(24,0,0)     on duplicate key update         is_active = case when used > 3 1 else 0 end,         id = last_insert_id(id),         used = used + 1,         id = (select @uids := concat_ws(',', last_insert_id(), @uids)); select @uids, last_insert_id() f, max(id) l `tbl1`; 

being not tricky, have 2 values @ end:

1. last_insert_id() f first inserted row id
2. max(id) l last inserted row id

so 2 boundaries surly have inserted rows ids. saying has drawbacks , have last_insert_id() value if rows affected update statement. tagged question php there chance benefit mysqli_affected_rows while doing multi_query couldn't produce expected return values mysqli_affected_rows documented mysql:

for insert ... on duplicate key update statements, affected-rows value per row 1 if row inserted new row, 2 if existing row updated, , 0 if existing row set current values.

you can try , see if works. if expected return value can understand if query has done updates or inserts , read results based on that

as short answer, there no correct way within same query context may doing programatically neater? (though don't bet on performance)

$values = [[1, 0, 0], [2, 0, 0], [24, 0, 0]]; $insertids = []; $updateids = [];  foreach ($values $v) {     $insert = $mysqli->prepare("insert `tbl1` (name, used, is_active) values (?, ?, ?)");     $insert->bind_param('ddd', $v[0], $v[1], $v[2]);     $insert->execute();     if ($insert->affected_rows == -1) {         $update = $mysqli->prepare("update `tbl1` set id = last_insert_id(id), used = used + 1, is_active = case when used > 3 1 else 0 end name = ?"); // considering `name` unique column         $update->bind_param('d', $v[0]);         $update->execute();         if ($update->affected_rows == 1)  {             $updateids[] = $update->insert_id;         }     } else {         $insertids[] = $insert->insert_id;     } }  var_dump($updateids); var_dump($insertids); 

example output:

array(1) {   [0]=>   int(140) } array(1) {   [0]=>   int(337) } 

one workaround using mysql triggers. creating after insert trigger on table tbl1, able store ids later use:

create trigger trigger_tbl1 after insert     on `tbl1` each row begin     update `some_table` set last_insert_ids = concat_ws(',', last_insert_id(), last_insert_ids) id = 1; end;