Struggling with RevQuery

[maverickalex]

I have a number of tables in mysql database.

I am trying to do a select from query.

I have a variable tpilotid which contains a number from 1 - 1000 and corresponds with a column in a table called pilots.
what i'm trying to achieve is this.

if tpilotid = a value from the pilots column then select the value from column rankid.
I have really struggled to get this to work.

SELECT rankid from pilots WHERE rankid = ('tpilotid')

I am then trying the revquery from the tools menu and populating a field with the columns pilotid and rankid.

i was then going to do something like this.

Code: Select all

   if tpilotid = char 1 to 3 of column 1 of field "rankid" then
        put char 4 into trankid
     end if
     if trankid = "1" then
     put field "flighttime" * 100 into tpilotpay
        put tpilotpay into field "pilotpay"
     else if trankid ="2" then
        put field "flightime" * 180 into tpilotpay
        put tpilotpay into field "pilotpay"
     end if

Any pointers please?

Thank You
Alex

[Janschenkel]

Assuming that 'tpilotid' is a local variable in scope, and 'gconnectionid' is also in scope, try something like this:

Code: Select all

put "SELECT rankid from pilots WHERE rankid = :1" into tsqlquery
put revQueryDatabase(gconnectionid, tsqlquery, "tpilotid") into tcursorid
if tcursorid is not an integer then
  answer error tcursorid
else
  -- traverse the cursor to extract the data
end if

If you just want a flat tab-delimited text to work with, use something like this:

Code: Select all

put "SELECT rankid from pilots WHERE rankid = :1" into tsqlquery
put revDataFromQuery(return, tab, gconnectionid, tsqlquery, "tpilotid") into tquerydata

HTH,

Jan Schenkel.

[maverickalex]

Thanks Jan,

I finally managed to get it to work as intended using this (pretty simple)

Code: Select all

put "SELECT rank from phpvms_pilots where pilotid='"&tpilotid&"'" into tpilotrank
put revDataFromQuery(comma,return,gConnectionID,tpilotrank) into trankdata
put trankdata into field "rankdata"

I succesfully used this format for other queries.

Thanks for your assistance.

Alex