PERL/DBI: display autonumber after insert

[Rovent]

Hullo all!

I'm not sure if this is possible or not, but i'm not really too up on my SQL. . .

I have a page where customers can insert comment cards. After the comment card is inserted, i would like to return what number the autoincrement assigned to the new row.

The pseudocode would kinda flow like this

---

$strSQLQuery = "insert into tblfoo(one, two, three) values (1, 2, 3)";

prepare($strSQLQuery);
execute($strSQLQuery);

## MAGIC HAPPENS WHERE I GET THE ROW NUMBER ($intIndex) HERE. . .##

print "Your comment card number is $intIndex. Please remember this for your records";

---

Much thanks to everyone in advance!

- alex

 

[clueo8]

# Finds the latest row in the sample table with the column being index 
$sth = $dbh->prepare("SELECT * FROM sample s WHERE s.index = (SELECT MAX(s2.index) FROM sample s2)");
$sth->execute() or die $sth->errstr;
my @row = $sth->fetchrow_array;

the @row array will contain the entire row with $row[0] being the first column $row[1] being the 2nd column. So if your auto-increment is the first column, then $row[0] will hold the value in it.

You can also maybe try:

# Finds the latest column in the sample table with the column being index 
$sth = $dbh->prepare("SELECT index FROM sample s WHERE s.index = (SELECT MAX(s2.index) FROM sample s2)");
$sth->execute() or die $sth->errstr;
my $column = $sth->fetchrow_array;

Where $column will hold the value your looking for.

Tim

 

[Rieekan]

Actually, the select statements listed above are a bit overkill for what they're intentions are. Sub-selects have extra overhead in the database that isn't necessary.

# Finds the latest column in the sample table with the column being index
$sth = $dbh->prepare("SELECT MAX(s.index) FROM sample s");
$sth->execute() or die $sth->errstr;
my $column = $sth->fetchrow_array;

- Rieekan

 

[Rovent]

Ah, thank you both! That was it exactly!!

- alex

 

[icrf]

What about last_insert_id, a DBI method designed for this exact function? It requires that the underlying database driver implement the method, as not all databases have such a feature. What driver/database are you using?

________________________________________
Andrew

 

[goBoating]

Or, if last_insert_id is not available, write a stored procedure in the DB that explicitly returns the row id of the insert.

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.