Passing mysql values to create 2 records at once(HELP!!!)

[Simon.Dau]

hey lads, need some serious help from you. Lets say i've got 2 databases: orders and order_samples. User is logged in. He wants to put an order. Fills up the form and press submit. My intentions are to create two records at the same time but in a bit more complex way:
when user creates new order (order table) he gets unique order ID (auto increment). But I want to create new record in order_samples table, which would have that new order ID in one of the columns. And I want to do that without any user interaction at the same time new order is being created. I hope that makes sense.
I been thinking I could create new order record, then scan database to return me the newest order ID made by particular user. But how to create second record (in order_samples table) on the same page automaticaly? any thought on that?

[Newsgroup_User]

S. Daukantas wrote:
> But how to create second record
> (in order_samples table) on the same page automaticaly? any thought on that?

You just put a second, or third <cfquery...> block on the page right
after the one the creates the first record in the orders table and the
one that scans the table for the new id.

<cfquery...>
UPDATE ORDERS TABLE
</cfquery>
...
<cfquery...>
SCAN TABLE
</cfquery>
...
<cfquery...>
UPDATE ORDER SAMPLES TABLE
</cfquery>

If you are on CF8 you may want to look at some of the new database
functionality that may return that ID for you without the second query
to scan the database.

You will probably also want to investigate the <cftransaction...> tag
that could give you rollback capability. Thus if something happens to
prevent the second update from happening, the first is rolled back and
undone so that you do not get data out of sync. If this is relevant to
your application.

[Dan_Bracuk]

There is another approach you might consider. Use UUIDs for your primary keys instead of numbers. Then you can do this:

<cfset thiskey = createuuid()>
<cfquery name = "q1">
insert into order
(orderid, etc)
values
(<cfqueryparam value = "#ThisKey#">, etc

<cfquery name = "q1">
insert into order_samples
(orderid, etc)
values
(<cfqueryparam value = "#ThisKey#">, etc