CODEWITHSUNDEEP
mysqlcoldfusionlucee
HPWD
HPWD

Reputation: 2240

(MySQL - Coldfusion/Lucee) - One MySQL Statement, Multiple Inserts - How to get EACH subsubCategoryID?

I'm passing a list of IDs to my insert function. I'm inserting the parentID and the subcategoryID into my database via one sql statement with multiple inserts. This works just fine. However now the rquirements have been altered and now I need to get back some information from each insert.

Using the code below, I added SELECT last_insert_id() as subsubCategoryID and the query returned only the first inserted record (I actually expected to only get the very last one inserted not the first one so that was a surprise). Is it possible to get back EACH of the subsubcategoryIDs for each insert or would that require looping over the insert statement and breaking the multiple inserts into distinct insert statements?

for (item in listToArray(local.formData, ",")) { 
    local.values &= "(44, " & item & "," & local.formData.userID &")," ;
}

local.values = left(local.values,len(local.values)-1) ;

local.sql = "
    INSERT INTO table (parentID, subcategoryID, userID) 
    VALUES " & local.values & "
    ; SELECT last_insert_id() as subsubCategoryID;
"
writeDump(local.sql);

Upvotes: 2

Views: 313

Answers (2)

Phillip Senn
Phillip Senn

Reputation: 47595

You should check out stored procedures. I envision a block of text with delimiters like the pipe symbol, which are broken apart and inserted in a begin transaction/commit.

Upvotes: 1

HPWD
HPWD

Reputation: 2240

I've decided to leave the original query as-is (without the SELECT last_insert_id() as subsubCategoryID) and then at the end perform a lookup (select statement) that will return the subsubcategoryIDs. This will only be two database connection opposed to the multiple database connections that would be required if I broke up the insert statements just so I could append SELECT last_insert_id() as subsubCategoryID after each insert statement.

** UPDATE ** Based on a comment made below by Ageax, I'm adding this update for clarity in case someone else runs across this SO Question... When I say "at the end perform a look up" I mean the end of the process and not the end of the query **

Upvotes: 1

Related Questions