sql Select statement where column = variable (as opposed to a specific value)

Getting Started Newbie Questions
#1

–The following code works fine:

for row in db:nrows(“SELECT * FROM highScore WHERE level = 4 ORDER BY score DESC”) do

    

–BUT I need to use replace the number 4 above with a variable “index” as outlined below:

local function retrieveData(index)

        print(“Index :”…index)    

        for row in db:nrows(“SELECT * FROM highScore WHERE level = index ORDER BY score DESC”) do

    

–The print statement works fine i.e. it prints out whatever number was sent to function but index is not accepted within the Select Statement directly after.  So if someone knows the syntax of referencing a variable in sql please let me know !

#2

This should work: 

for row in db:nrows("SELECT \* FROM highScore WHERE level = " .. tostring(index) .. "ORDER BY score DESC") do

You need to treat the part before index and after index as two separate strings and then put the index in the middle with string concat operators. My suggestion would be to do it like this : 

local mySQLStatement = "SELECT \* FROM highScore WHERE level = " .. tostring(index) .. "ORDER BY score DESC" print (mySQLStatement) for row in db:nrows(mySQLStatement) do

This way you will see exactly what your SQL statement looks like during code execution and sort things out if needed. Good luck.

#3

Thank you so much.  Works great now ! 

#4

This should work: 

for row in db:nrows("SELECT \* FROM highScore WHERE level = " .. tostring(index) .. "ORDER BY score DESC") do

You need to treat the part before index and after index as two separate strings and then put the index in the middle with string concat operators. My suggestion would be to do it like this : 

local mySQLStatement = "SELECT \* FROM highScore WHERE level = " .. tostring(index) .. "ORDER BY score DESC" print (mySQLStatement) for row in db:nrows(mySQLStatement) do

This way you will see exactly what your SQL statement looks like during code execution and sort things out if needed. Good luck.

#5

Thank you so much.  Works great now !