Get the id of current mysql insert

Question

is there anyway to get the ID of the current record I am INSERTING into the database table using php with Mysql without having to do an extra select to get the last ID?

FOr example, if my table has these columns, id, url, name and if url consists of the domain name and current id as the query variable ex: domainname.com/page.php?id=current_id

$sql="INSERT INTO Persons (id, url, name )
VALUES
('domainname.com/page.php?id=".**whats_the_current_id**."','$_POST[age]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

What library/functions are you using to execute the insert? The answer depends on it... [and it's probably a duplicate](http://stackoverflow.com/search?q=%5Bphp%5D+%5Bmysql%5D+last+insert+id). — Mike B, Jul 25 '12 at 20:29
I'm assuming you use PDO: [LastInsertId](http://php.net/manual/en/pdo.lastinsertid.php) — Romain, Jul 25 '12 at 20:30
mysql_insert_id ? http://it2.php.net/manual/en/function.mysql-insert-id.php — Luca Filosofi, Jul 25 '12 at 20:30
I'm not sure if this works, but you could try: `INSERT INTO table SET url = CONCAT("http://beginning.of.the/url?id=", VALUES(id))` — TaZ, Jul 25 '12 at 20:41

score 4 · Answer 1 · answered Jul 25 '12 at 20:47

4

as far as I know, there is no 'clean' way to find the ID you are about to insert (from what I understand from your question, this is what you want to know).

Two options in my opinion, starting with the ugly one: select max(id) from Persons, increment it with one, and hope that no insert's will mess this up for you. Like I said, its ugly, and -not- reliable.

A better option would be to first insert the record with a dummy value for url, and then retrieving the just inserted row's ID with mysql_insert_id(). Then update that record with the correct url value.

You asked for a way to retrieve the id without a select query following the insert query, but like I said, I don't think this is possible.

answered Jul 25 '12 at 20:47

Wouter

833
6
13

3

I think it might be 'cleanly' possible with an insert trigger, not sure. Also, +1 for being the first answerer to actually understand the question. – TaZ Jul 25 '12 at 20:49
1

Nice idea. I looked into the insert triggers a bit, but unfortunately for the question asker there is still a problem, which is explained quite well in the first answer [here](http://stackoverflow.com/questions/1211792/mysql-trigger-to-update-a-field-to-the-value-of-id). Saves the question asker the trouble of diving into triggers :) – Wouter Jul 25 '12 at 20:56

score 3 · Accepted Answer · answered Jul 25 '12 at 20:36

3

i use mysql_insert_id() for that. it works fine.

// pseudo-ish code
$query = "INSERT something .... "
$updated = $db->run_query($query);
$id = mysql_insert_id();

answered Jul 25 '12 at 20:36

Kristian

21,204
19
101
176

score 1 · Answer 3 · answered Jul 25 '12 at 20:58

your table should be like this

ID AUTO_INCREMENT
person_id VARCHAR
person_url ...
person_name ...

your post form something like

<form method="post">
  <input type="hidden" name="id" value="<?php echo uniqid() ?>" />
   ...
</form>

the query should be like this:

$person_id   = intval($_POST['id']);
$person_url  = mysql_real_escape_string($_POST['url']);
$person_name = mysql_real_escape_string($_POST['name']);

mysql_query("INSERT INTO Persons (person_id, persno_url, person_name) VALUES ( {$person_id} , {$person_url}, {$person_name} )");

$ID = mysql_insert_id();

score 0 · Answer 4 · answered Jul 25 '12 at 20:41

0

The current ID is in $_GET['id']. You should sanitize it before inserting it into your query:

$id = intval($_GET['id']);

Then, use $id in your query.

answered Jul 25 '12 at 20:41

nickb

59,313
13
108
143

score -2 · Answer 5 · answered Jul 25 '12 at 20:41

If you add classes around the first insert and then the second select. The select will work then.

<?php
class insert1{
     function inserthere(){
          ***insert***
     }
}
class select1{
     function selecthere(){
          ***select***
     }
}
$a = new insert1;
$a->inserthere();
$b = new select1;
$b->selecthere();
?>

Get the id of current mysql insert

5 Answers5