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
– user3784920, Commented Jun 28, 2014 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
– Ja͢ck, Commented Jun 28, 2014 at 13:55
I would equal the chances of breaking a server in that scenario as likely as rogue unicorn attacks. — Ja͢ck
– Ja͢ck, Commented Jun 28, 2014 at 14:19

Gabriel · Accepted Answer · 2014-06-28 14:00:32Z

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.

Orel Eraki · Accepted Answer · 2014-06-28 13:54:02Z

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

You'd have to wrap it inside a transaction, though ... and a master-master setup may cause issues as well; maybe locking the table ... :)

Golu · Accepted Answer · 2014-06-28 14:02:55Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Linked

Related