Is it possible to retrieve id (ua_id is in my case) for each INSERT statement in following SQL request
INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,1); INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,2); INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,3); using mysqli::multi_query() to execute it wheres ua_id is AUTO_INCREMENT primary key?
In MySQL you can use LAST_INSERT_ID() function. You need to use this function each time - after INSERT statement. It gets ID for the last inserted record.
MySQL documentation - LAST_INSERT_ID()
SELECT LAST_INSERT_ID() AS last_insert_id[1,2,..,n] after each INSERT doesn't work too.
INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,1); SELECT LAST_INSERT_ID(); INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,2); SELECT LAST_INSERT_ID(); it doesn't seems to work as expected.Well. you do a multiquery, let's use it:
<?php $db->multi_query(' INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,1); SELECT LAST_INSERT_ID(); INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,2); SELECT LAST_INSERT_ID(); INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,3); SELECT LAST_INSERT_ID()') or trigger_error($db->error); do { if ($result = $db->store_result()) { while ($row = $result->fetch_row()) { var_dump($row); } } } while ($db->next_result()); mysqli->insert_id will return the ID of the first row inserted if you insert in the same table you then know that the next row inserted is the first row insert_id + 1...
If you work with many tables at once you then need to scroll back your results then you need to create another query using :
SELECT MAX(id) FROM table-a WHERE 1 ... SELECT MAX(id) FROM table-n WHERE 1 Now I don't know what is better (single insert query or multi query)...
Yes. Use $mysqli->insert_id - see working example below.
$mysqli = new mysqli(host, user, pass, db); $query = "INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,1); "; $query .= "INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,2); "; $query .= "INSERT INTO users_addresses (ua_user_id, ua_address_id) VALUES (1,3); "; if ($mysqli->multi_query($query)) { do { /* Returns the auto generated id used in the last query */ printf("%d\n", $mysqli->insert_id); /* Check if loop should continue */ if ($mysqli->more_results() === FALSE) break; } while ($mysqli->next_result()); } Use built in mysqli::$insert_id - documentation: http://php.net/manual/en/mysqli.insert-id.php
