Can I Return the Autoincrement id from an Insert?

Databases
1

When I insert a new entry into a MySql table which has an ‘id’ column that is auto-incremented, can I return the new id value?
What I have is something like this:

$sql = $db->prepare("INSERT INTO Table (ThisData, ThatData) VALUES (?, ?)") ;
$insdata = array($this, $that) ;
$sql->execute($insdata) ;

As it is I’m having to query the id with Select:

"SELECT id FROM Table WHERE ThisData = '$this' AND ThatData = '$that' LIMIT 1"

I’m in the process of migrating from mysqli to pdo, and thought there must be a smarter way to do this.

2

for PDO, lastInsertId() should get you the correct value.

2 Likes
3

Thanks. I’ll try that.

5

It worked! :astonished:
I’m only surprised because I wasn’t sure of the correct syntax, and my first “guess” at it worked.

$id = $db->lastInsertId('id');
7

If you want to keep the current ID, you will need to store it in a session cookie because using lastInsertId() won’t last for long.

Let’s say you created someone and incremented the number to 1. If you’re still on the page and someone happens to come by and increment a new number to let’s say 2. The number you will be using is 2, not 1.

8

[off topic] I am using a tablet at the moment and cannot test if lastinsertid will produce the same as count()

9

I would think they are not the same. Count will say how many entries there are, last id will give the last entry, which will not necessarily be the same, as some entries may have been deleted.

1 Like
10

Maybe use a subsequent MAX{'id') query?

11

The lastInsertId (from post #2) already does it without a subsequent query…this was solved - I’m not quite sure why it’s continuing on…

2 Likes
12

[quote=“DaveMaxwell, post:11, topic:211409, full:true”]this was solved - I’m not quite sure why it’s continuing on…
[/quote]
or why it wasn’t in the php forum in the first place :wink:

1 Like
13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.