Member Avatar for anthonyjpv

Hi!

I need to know if theres a function that is simmilar to mysql_insert_id function

where it gets the last inserted id

but the thing is my id is not auto increment

i tried doing the select statement but not working

my scenario is:

i am updating a record from one table after inserting a new record in another table

so insert then update in one script.

thanks in advance!

  • 4 Contributors
  • 7 Replies
  • 100 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Short summary and practical fixes for the situation described by : because the id column is not AUTO_INCREMENT, the usual mysql_insert_id() approach won’t give you a usable value. The reliable options are to (A) produce the id in your application and reuse it for both INSERT and UPDATE, (B) tell MySQL to record a specific value as the connection’s “last insert id”, or (C) maintain a safe sequence counter on the server. Below are compact patterns and cautions.

Generate the id in PHP and reuse it (safe, simple):

$newId = /* generate a unique id or UUID */;
$stmt = $pdo->prepare('INSERT INTO table_a (id, col) VALUES (?, ?)');
$stmt->execute([$newId, $value]);

$stmt = $pdo->prepare('UPDATE table_b SET ref_id = ? WHERE ...');
$stmt->execute([$newId]);

Use MySQL’s LAST_INSERT_ID(expr) to set a per-connection last-id that PHP can read back:

INSERT INTO table_a (id, col) VALUES (LAST_INSERT_ID(12345), 'x');
$last = mysql_insert_id();  // will return 12345 for this connection

This is handy when you must supply a numeric id server-side but still want mysql_insert_id() semantics.

Use a sequence table when you need numeric, incrementing ids and must avoid races:

START TRANSACTION;
SELECT next_id FROM seq FOR UPDATE;
UPDATE seq SET next_id = next_id + 1;
COMMIT;

Use that value for the INSERT and the subsequent UPDATE.

Notes and cautions: do updates/inserts on the same DB connection; SELECT MAX(id) is race-prone under concurrency; LAST_INSERT_ID(expr) works per connection and with numeric values; generating the id in the app or switching to AUTO_INCREMENT (if feasible) is usually the simplest and safest long-term fix. In this thread and pointed out the AUTO_INCREMENT limitation; the approaches above extend those observations into concrete, safe alternatives.

Recommended Answers

All 7 Replies

Member Avatar for baig772

i guess that your scenario is that you are updating 2nd table after inserting in 1st table???
try

mysql_info()
Member Avatar for vaultdweller123

it doesn't matter if your incrementing your id or not, mysql_insert_id() always return the last inserted id

Member Avatar for baig772

it doesn't matter if your incrementing your id or not, mysql_insert_id() always return the last inserted id

no it returns 0 if column is not auto incremented

Member Avatar for vaultdweller123

no it returns 0 if column is not auto incremented

it's because it is not incremented! did you get my point? because he did not increment his primary id, all his id including his last id is zero

Member Avatar for pritaeas

From the manual:
"Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT)."

I've highlighted the important parts:
1. If he is not using an auto increment ID column, then this function returns zero.
2. You have to call this function directly after the INSERT query, otherwise it returns zero.

If the OP is not using an auto incremented ID, he has to query the table with a SELECT to get the highest (most likely last) ID, or any other logic that matches his inserting logic.

Edited by pritaeas because: n/a
Member Avatar for anthonyjpv

Thats the reason why I indicated that my id is not auto increment

cause I know it will return 0 or false

Ive also tried doing select statement after insert then update but not getting any luck

Member Avatar for pritaeas

Show what you are doing then, and your table structures.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.