using last_insert_id() from mySQL in Rev

[maverickalex]

i have had a lot of success now completing my app in rev using mysql databases.

My final stumbling block prior to finishing is using the LAST_INSERT_ID() function in conjunction with rev.

In short my table pireps has incrementing rows for each insert.

my insert looks like so:(shortened)

put "INSERT INTO phpvms_pireps (pilotid,code,aircraft) VALUES ('"&tpilotid&"','"&tcode&"','"&tAircraft&"')" into tSQL

that gives me

0001 010 AVA B747
0002 010 AVA B747
0003 010 AVA B747 etc

What i want to do is use the LAST_INSERT_ID(); function in mysql to grab the last id number in this example 0003, then insert it with some other variables into another table named pirepcomments.

i have tried this code.

Code: Select all

--construct the SQL
put "INSERT INTO phpvms_pireps (pilotid,code,aircraft,flightnum,depicao,arricao,flighttime,flighttime_stamp,distance,submitdate,`load`,fuelused,fuelunitcost,fuelprice,price,flighttype,gross,pilotpay,expenses,revenue,source) VALUES ('"&tpilotid&"','"&tcode&"','"&tAircraft&"','" &tFlightnum&"','" &tDepicao&"','"& tArricao&"','"&tflighttime&"','"&tflighttime&"','"&tdistancedata&"','"&tsubmitdate&"','"&tload&"','"&tfuelused&"','"&tfuelunitcost&"','"&tfuelprice&"','"&tpricedata&"','"&tflighttype&"','"&tgross&"','"&tpilotpay&"','"&texpenses&"','"&trevenue&"','"&tsource&"')" into tSQL

--send the comments
put "INSERT INTO phpvms_pirepcomments SELECT LAST_INSERT_ID ( ),pirepid,pilotid,comments,submitdate from phpvms_pireps WHERE pirepid,pilotid,comments,submitdate = ('"&tpilotid&"','"&tcomments&"','"&tsubmitdate&"')" into tpirepcomments

-- send the SQL to the database, 
revExecuteSQL gConnectionID,tSQL,tpirepcomments
put tSQL,tpirepcomments

My insert look like this (edited to remove the first insert)

Code: Select all

INSERT INTO phpvms_pirepcomments SELECT LAST_INSERT_ID ( ),pirepid,pilotid,comments,submitdate from phpvms_pireps WHERE pirepid,pilotid,comments,submitdate = ('001','first automated pirep subission','2011,3,29,14,41,0,3')

As you can see that way does not get the last insert id.

Any prod in the right direction would be a blessing.

Thanks
Alex

[maverickalex]

Actually, by trial and error i managed to do this although i think its not the correct way.

i first executed the first insert which sent the auto incremented row to the table phpvms_pireps.

Then a second execute like so

Code: Select all

put "select last_insert_id ( ) from phpvms_pireps" into tpirepid
put revDataFromQuery(comma,return,gConnectionID,tpirepid) into tpirepdata
put "INSERT INTO phpvms_pirepcomments (pirepid,pilotid,comment,postdate) VALUES ('"&tpirepdata&"','"&tpilotid&"','"&tcomment&"','"&tsubmitdate&"')" into tpirepcomments
put tSQL,tpirepcomments

revExecuteSQL gConnectionID,tpirepcomments

It succesfully populates my table phpvms_pirepcomments with the proper data.
The one issue when i looked in my message box is that the "tpirepdata" is scrolled about the same number of times as the auto incremented number.

In this case this is pirep id 1685

my message looks like this

Code: Select all

INSERT INTO phpvms_pirepcomments (pirepid,pilotid,comment,postdate) VALUES ('1685
1685
1685
1685
1685
1685
1685
1685
1685
repeated many times
1685','001','First fully automated pirep submission using the Newly Developed App. This calculates your fuel usage,time online,earnings. So no need to write anything down. All you need to know is your aircraft and flightnumber. available for testing soon.!!!','2011,3,29,15,23,0,3')

This will obviously slow down the process.
Any pointers as a better way to do this?

[asawyer13]

Generally in MySQL you just do a normal insert, then issue the select last_insert_id ( ) command and you get it

basically like this

Code: Select all

INSERT INTO test3(VALUE) VALUES ('ding')

SELECT LAST_INSERT_ID()

You don't have to pick a table, it just gives you the last value.

Alan