Whats the best way to:

insert a record into table1 (orders)
get an (automatically created) order_number field back from it
insert the order_number into table2 (order items)

?

this is a pain in the bum depending on what database you are using.

Some databases have things like "LAST_INSERT_ID" that you can select and work on a "per connection" basis.

When this is not available, I do the following...

datestamp = now()

conn.execute "INSERT INTO Table1 values('" & datestamp & "', " & ClientID & ", blah blah)"
rs.open "SELECT ID from Table1 where datestamp = '" & ddatestamp & "' and clientid = " & ClientID

In other words, you work out what is explicit to that record (or you create something) to then retrieve the id again..

but most dbs have an connection based function you can use

I was hoping to avoid the scenario:

insert into orders (userID)
values (myvar_userID)

select order_num from orders where userID = myvar_userID
myvar_order_num = order_num

insert into order_items (order_num)
values (myvar_order_num)

Seems like a lot of wasted database hits. I'll check if there's a special function to do what you said. I was hoping there'd be something in SQL I could use, like an INSERT SELECT

Cheers,
-DV

I suppose I can see where a RAM based shopping cart has its advantages now.

Ok, I've got it down to:

insert into orders (userID)
values (myvar_userID)

INSERT INTO order_items (order_number)
SELECT DISTINCT order_number FROM orders
WHERE userID LIKE '%#myvar_userID#%'

What DB are you talking to?

Easy for most of my work: $id = mysql_insert_id($db_connection);

Can you not just do a Select as MAXID? i.e. once the record has been added, the SQL then selects the max ID, which would be the last record entered.

Sure I saw this in use somewhere recently. :confused:

DW

Andrewza - ColdFusion

Dreamweaver - basically the code is for 'add_item' process of a shopping cart, eg.

ORDERS table
one-to-many linked by order_number to
ORDERED ITEMS table

I've got it working but I like to optimise as much as possible and reduce the database workload - as it stands, I'm doing an INSERT INTO, then a SELECT, then an INSERT INTO again. I want to reduce this as much as I can (unless this normal?)

Question - in coldfusion is there anyway to retrieve the contents of the order_number field when doing this (its autonumbered)

<cfquery name="insert" datasource="db">
INSERT INTO orders(userID)
VALUES ('#myvar_userID#')
</cfquery>

or do I have to add this:

<cfquery name="get_order" datasource="db">
SELECT DISTINCT order_number FROM orders
WHERE userID LIKE '%#myvar_userID#%'
</cfquery>

<cfoutput query="get_order">
<cfset myvar_order_number=#order_number#>
</cfoutput>

-DV

PS Why the change of username ;)

Hmm, got me there, don't do coldfusion, done this in ASP and PHP.

Its ok, I'll keep using the method I've got running at the moment.

Might do a RAM based cart over the weekend and see what I think of that. Really the idea of making someone's shopping cart persist for a couple of days doesn't really appeal, so a RAM based solution maybe fine.

Daz,

Only two options are, use a function that generates a unique primary key and populate the field yourself, or use a database that allows you to return the key as part of the proc (SQL Server, Oracle, Interbase):

INSERT INTO Orders .....

SELECT @NewPKey = @@IDENTITY

Doing a SELECT MAX is *not* reliable especially in a multi-user environment, if another record gets added between your INSERT and SELECT you are going to return the wrong row.

Cheers

Gary

Very true Gary :) Didnt think of that :rolleyes:

DW

DW,

hey like the site!

Gary

I use PHP and MySql, and I would do it like this...

$result1 = mysql_query ("INSERT INTO orders (userID) VALUES ('myvar_userID'));
$order_number = mysql_insert_id ($link);
$result2 = mysql_query ("INSERT INTO orderitems (ordernumber) VALUES ('$order_number')");

hope this helps getting you to where you want to go..?!

apjames, yeah, putting the info into a variable like that would be great but I don't think cold fusion offers that option.

I've got it all running nicely, but it would be nice not to hit the database as much as I am.

I'll keep looking into it and see if there's any solution in CF - this is my first CF project, so it as learning process at the same time.

-DV