How do i get the last ID's from the inserted data?

Question

I have an option to send multiple rows into an table, i'm using an foreach to do that:

if (is_array($add['jobname'])) {
$insert = "INSERT INTO job_offers (job_category, status) VALUES ";

foreach ($add['job_name'] as $key => $value) {

$insertedval[] = "
('" . safe($add['job_category'][$key]) . "', 
 '" . safe($add['status'][$key]) . "')";

}
}
$insert .= implode($insertedval, ",");

$last_id = db_query($insert, '+id?'); //gets the last generated ID, its a function that i created, and working great.

The problem is that i want to get the last ID, and i'm getting, but i'm inserting multiple rows into the database, and i want to get the ID's from all the inserted values, because they are being sent at the same time.

I can't put the $last_id variable inside the foreach loop, what do i do?

ps: i'm using auto increment

no, you are totally wrong, i already have the last inserted id — user3784920, Jun 28 '14 at 13:49
Unfortunately, there's nothing reliable that you can do except inserting them one by one; you could use prepared statements to optimise that a little, but that's basically it. — Ja͢ck, Jun 28 '14 at 13:55
I changed my code recently, because i was doing the full query inside the foreach loop, i changed because it seems dangerous, i was repeating INSERT INTO multiple times, am i right? — user3784920, Jun 28 '14 at 14:00
I think that it can break the server, because i dont have a limit to insert multiple values, so, if someone tries to insert 20 rows at the same time, it would be dangerous, because the query is being repeated multiple times — user3784920, Jun 28 '14 at 14:04
I would equal the chances of breaking a server in that scenario as likely as rogue unicorn attacks. — Ja͢ck, Jun 28 '14 at 14:19
You're going to want to look through [this question and answers](http://stackoverflow.com/q/5741187/812837), and some of the associated commentary. Also, no RDBMS I'm aware of outputs multiple auto-gen ids, so you'd need to do this per-line (some do have a row-change table feature, but this isn't strictly for dealing with ids...) — Clockwork-Muse, Jun 29 '14 at 10:52

score 1 · Answer 1 · answered Jun 28 '14 at 14:00

Can you try something like this?

$db = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");
$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO job_offers (job_category, status) VALUES (?, ?)');

$insertedIds = array();
foreach ($add['job_name'] as $key => $value) {
    $stmt->execute($add['job_category'][$key], $add['status'][$key]));
    $id = $db->lastInsertId();
    array_push($insertedIds, $id);
}

$db->commit();

the ids should be in the insertedIds.

score 0 · Answer 2 · answered Jun 28 '14 at 13:54

0

You can't.

Only by saving the the last index id before the inserted data, and than do it again after and select all the ids between that range.

SELECT ID FROM job_offers ID BETWEEN last-before-insert AND last-after-insert

answered Jun 28 '14 at 13:54

Orel Eraki

11,940
3
28
36

You'd have to wrap it inside a transaction, though ... and a master-master setup may cause issues as well; maybe locking the table ... :) – Ja͢ck Jun 28 '14 at 13:58

score 0 · Answer 3 · answered Jun 28 '14 at 14:02

You don't need a custom function to get the last insert id , there is already one built into both php mysqli and php PDO extensions .

As far I know , the last insert id is session aware , meaning you will get the last insert from the current insert requests only .

Get last inset id using PHP PDO

$pdo->lastInsertId();

Get last insert id using mysqli

$mysqli->insert_id;

$pdo and $mysqli are the connection variables

How do i get the last ID's from the inserted data?

3 Answers3