INSERT order and getting row ID back

[Dax_Trajero]

I've been over to the MySQL site just to confirm this, but any voice of experience would be nice here...

So I'm reaching the point that when an order is placed I do two things:

1. Insert a row into table 'orders'

2. Insert shopping cart rows for that order, into the 'ordered_items'  table

The problem I have is, table 'orders' uses an auto increment value for each row's key field, so I need some way to insert the row and retrieve that newly generated key field value.

MySQL provides this:

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');         # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

Does this seem OK ?

In theory I don't *need* to use an auto increment field. For example I could use the user's unique_session_ref and concatenate a datetime onto it ? I wasn't sure if such a VARCHAR type field for the key would impact performance.

Let me know what you think ?

[Guest]

I think this seems to be ok, but additionaly you have to implement the LAST_INSERT_ID() function. With MSSQL this is much easier. There you can either use the result attribute from cfquery tag (and get the IDENTITYCOL value) or directly insert a "select @@identity" clause after the insert statement, but in the same cfquery tag (maybe the "select @@identity" statement could also be used in MySQL).

And yes, if you use a varchar as primary key this can cause an impact of performance. Auto-increment values are normally integers, this data type uses less memory then varchar and the comparison methods for integers and varchars (to match foreign keys) differ significant.

[Dax_Trajero]

So are you saying I can't use that LAST_INSERT_ID() in the cfquery ?

[Guest]

Dax,

you can use the LAST_INSERT_ID() function, but I think you have to additionally implement it.

I don't know much about MySQL. Is LAST_INSERT_ID() an internal MySQL function?

[Dax_Trajero]

No, I think it's a built in function of MySQL v5.1.2 onwards

Adam Cameron mentioned to me the other day I can use these type of functions

[Guest]

Ok, I got it...

Then everything seems to be ok. Use the query from your first post as it is.

To decide if a primary key should be an int or a varchar depends on what you plan to do with it. For simple primary key - foreign key matching you can use an auto-incement integer. But in other situations there can be a reason to use varchar. E.g. I work on a project where we handle orders. The primary key for the orders table is a 12 digit integer, where the first part is the year and the rest is an auto-increment value. So it is very easy to identify orders from last year, this year,...

[Dax_Trajero]

So here's the finished code, using an auto increment field as the key field in orders

<cftransaction>

    <cfquery name="insertOrder" datasource=#APPLICATION.datasource#>

        Insert Into 00_orders (text)

        Values ('Fooi')

    </cfquery>

    <cfquery name="getID" datasource=#APPLICATION.datasource#>

        Select LAST_INSERT_ID() as new_ID

    </cfquery>

    <cfset newID = getID.new_ID>

</cftransaction>

One drawback I suppose is portability, when moving from one type of database to another eg, MySQL to MsSQL

[Dan_Bracuk]

Anytime you move a db from one software to another, you can expect to rewrite many queries.  The S in SQL stands for Structured, not Standard.

[Dan_Bracuk]

You could use a uuid instead of an autoincrement.

[Dax_Trajero]

Is UUID unique enough for the orders table key field ?

Isn't it a 35 character string, and therefore an impact on performance if made a key field ?

[Dan_Bracuk]

If you are concerned with uniqueness, you could always run a select query first.  Opinions vary as to whether this is necessary.

For performance, it shouldn't make any appreciable difference.

[Dax_Trajero]

Thanks Dan

I already use a UUID for my session.unique_session_ref

So I may as well just use that as the key for the orders table ?

[Adam Cameron.]

Is UUID unique enough for the orders table key field ?

Yes.  overwhelmingly so.

Isn't it a 35 character string, and therefore an impact on performance if made a key field ?

A CF UUID is a 35-char string.  A UUID (or GUID) when stored in a DB is a 128-bit number.

--

Adam