[ciapug] mysqli prepared statements

[Carl Olsen]

In SQL Server there is a function I can use to return the id number of the
record using a stored procedure, but I haven' figured out how to do it with
MySQLi prepared statements (using MySQL 4.1).  I haven't tried using MySQL
stored procedures yet (MySQL 5), so maybe there's a function for it.  The
stupid book I'm reading suggested the MAX(id) method, feeding in the
parameters that were just used to create the insert statement to make it
hard to return the wrong id (using the same parameters in the WHERE clause).
I can see why the book thinks this is a better method than simply requesting
the MAX(id), but I like the way SQL Server does it much better.  Since
MySQLi has a function for insert_id, it seems to suggest there would be
something similar for a MySQLi prepared statement.

-----Original Message-----
From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On Behalf
Of Scott Phillips
Sent: Monday, December 12, 2005 4:12 PM
To: ciapug at cialug.org
Subject: Re: [ciapug] mysqli prepared statements

I think the resounding silence following your question can be translated 
into, "No. We don't know."

Regarding your current approach... Do you lock the table before the insert 
and unlock it after you select the max id? Otherwise, another record could 
be inserted between the two and you'll end up with the wrong id. That's how 
I do it, anyway. Is there a better way?



At 10:26 AM 12/10/2005 -0600, you wrote:
>Does anyone know if you can use the mysqli->insert_id inside a prepared
>statement to get the id of the record that has just been inserted?
>
>I'm doing it with a select statement which uses the same parameters as the
>insert statement and selects "MAX(id) AS id" as the output parameter.  It
>works, but it's easy to make an error when typing it in.
>
>Carl
>http://www.carl-olsen.com/
>
>_______________________________________________
>ciapug mailing list
>ciapug at cialug.org
>http://cialug.org/mailman/listinfo/ciapug

_______________________________________________
ciapug mailing list
ciapug at cialug.org
http://cialug.org/mailman/listinfo/ciapug