Searching MySQlite with user input

Solar2D General Questions/Discussion
#1

Whats a good method for to use in the MySqlite query for user input? I know in in MYSQL it would be,

SELECT * from table WHERE column1 LIKE %$input%

But am not sure what it would be for SQLite. Thoughts?

#2

This  should loop through just fine.

            for row in oDatabase:nrows(‘SELECT * from table WHERE column1 LIKE %somevalue%’) do
                iRowCount = iRowCount +1
                tblData[iRowCount] = {}
                tblData[iRowCount].ItemId         = row.ItemId
                tblData[iRowCount].Active         = row.Active
                tblData[iRowCount].Description    = row.Description
                tblData[iRowCount].PurchaseDate   = row.PurchaseDate
            end

Larry

#3

Hm I tired that it didnt seem to work. The user variable would just be its name, with % on either side?

#4

Change the for loop to append the variable inside the query.

for row in oDatabase:nrows(‘SELECT * from table WHERE column1 LIKE %’ … somevalue … ‘%’) do

Using the … Is like using the plus sing in JavaScript or c#

#5

That worked well. One question for you, for this part of the looping table,

iRowCount = iRowCount +1
                tblData[iRowCount] = {}
                tblData[iRowCount].ItemId         = row.ItemId

I would only update the tblData to the actual column name and ItemId? For instance, if I had a column labelled column one, I would do column1[iRowCount].column1 = row.colum1 ?

#6

I also see in the sample code,

local t = display.newText( row.content, 20, 30*row.id, nil, 16 )

but the 30*row.id results in the text displaying not in single rows, but all over the screen with large gaps in between them, as results can be returned from row 1, row 7, row 89 for example.

#7

This  should loop through just fine.

            for row in oDatabase:nrows(‘SELECT * from table WHERE column1 LIKE %somevalue%’) do
                iRowCount = iRowCount +1
                tblData[iRowCount] = {}
                tblData[iRowCount].ItemId         = row.ItemId
                tblData[iRowCount].Active         = row.Active
                tblData[iRowCount].Description    = row.Description
                tblData[iRowCount].PurchaseDate   = row.PurchaseDate
            end

Larry

#8

Hm I tired that it didnt seem to work. The user variable would just be its name, with % on either side?

#9

Change the for loop to append the variable inside the query.

for row in oDatabase:nrows(‘SELECT * from table WHERE column1 LIKE %’ … somevalue … ‘%’) do

Using the … Is like using the plus sing in JavaScript or c#

#10

That worked well. One question for you, for this part of the looping table,

iRowCount = iRowCount +1
                tblData[iRowCount] = {}
                tblData[iRowCount].ItemId         = row.ItemId

I would only update the tblData to the actual column name and ItemId? For instance, if I had a column labelled column one, I would do column1[iRowCount].column1 = row.colum1 ?

#11

I also see in the sample code,

local t = display.newText( row.content, 20, 30*row.id, nil, 16 )

but the 30*row.id results in the text displaying not in single rows, but all over the screen with large gaps in between them, as results can be returned from row 1, row 7, row 89 for example.