Member Avatar for OmniX

Im trying to figure out a way to access a row I 'just created' via the primary key.

As it being a primary key it is auto incremented.

I am using php/mysql to create the row then I require to access that same row (via the primary key) to be able to do the required adjustments.

So any ideas?

Thanks, Regards X

  • 3 Contributors
  • 2 Replies
  • 308 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by nomadhacker

Dani AI

Generated

Short answer: use the connection-specific "last inserted id" for the same DB connection, and prefer the modern APIs (mysqli or PDO). 's pointer to the MySQL docs and 's suggestion are on the right track, but note a common mistake: do not pass the query result resource into the "last insert id" call — it expects the DB link/connection (or, with PDO, nothing).

A few compact examples (do not copy the exact code already in the thread):

/* ext/mysql (old) */
$link = mysql_connect('host','user','pass');
mysql_select_db('dbname', $link);
mysql_query("INSERT INTO mytable (col) VALUES ('x')", $link);
$id = mysql_insert_id($link);
/* mysqli (OO) */
$mysqli = new mysqli(...);
$mysqli->query("INSERT INTO mytable (col) VALUES ('x')");
$id = $mysqli->insert_id;
/* PDO */
$stmt = $pdo->prepare("INSERT INTO mytable (col) VALUES (:v)");
$stmt->execute([':v' => $val]);
$id = $pdo->lastInsertId();

Practical notes and pitfalls:

  • last-insert-id is connection-specific, so it is safe under concurrency as long as you use the same connection for the INSERT and the retrieval.
  • A multi-row INSERT returns the first generated auto-increment value; subsequent rows get consecutive IDs (compute offsets if needed).
  • Use prepared statements to avoid SQL injection, and wrap related operations in a transaction when you need atomicity.
  • The old ext/mysql API is deprecated/removed in modern PHP — migrate to mysqli or PDO for new projects.
  • If your DB supports it, an INSERT ... RETURNING (or equivalent) can return columns in one round trip; otherwise INSERT then SELECT by the returned id is the standard pattern.

This covers the usual ways to create a row and immediately access it safely.

Recommended Answers

All 2 Replies

Member Avatar for MitkOK

Member Avatar for nomadhacker

The unique id will work, or you could use the mysql_insert_id function to get the primary key of your last insert.

say you have a query you wrote:

$sql = "INSERT INTO ..."
$result = mysql_query($sql);

$keyid = mysql_insert_id($result);

Then keyid will have the primary key value, and you can use that to perform additional operations using the same primary key. Hope that helps.

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.