I have a database with 9 fields...
I want the user to enter something to search for like a search engine. This way only works with one word.
Example: "m42" returns 1 result as I intend
Example: "m 42" does not return any results
Also, if the user enters more than one criteria then I would like it to evaluate each word separately.
Example: "Rocket U.S.S.R" returns 0 results but "Rocket" returns 13 results as expected
I have attached a copy of the database.
The steps that I have taken to research this problem is exploring using a Full Text Search (FTS) and creating a virtual table to use the MATCH function of SQL. I just don't know if B4A supports any of the FTS extensions.
I have also researched the REGEX function to separate the individual words. Then use a do while loop to search and return the results to the list view.
I just don't know what the most efficient approach will be. I also want to choose the best route before educating myself toward the best answer.
My background is using Visual Basic for Applications (Excel) and I am new to the SQL environment. I have read the Beginners manual and taken the SQL tutorials suggested.
Any Help is appreciated.
I want the user to enter something to search for like a search engine. This way only works with one word.
Example: "m42" returns 1 result as I intend
Example: "m 42" does not return any results
Also, if the user enters more than one criteria then I would like it to evaluate each word separately.
Example: "Rocket U.S.S.R" returns 0 results but "Rocket" returns 13 results as expected
B4X:
Sub DisplayResults
lv_SearchResults.clear
Dim Search As String
Search = edt_SearchOrdnance.Text
Dim Cursor1 As Cursor
Cursor1 = SqlOrdnance.ExecQuery("SELECT * FROM " & DBTablename & _
" Where "& ColumnName(1) &" LIKE '%" & Search & "%' OR " _
& ColumnName(2) &" LIKE '%" & Search & "%' OR " _
& ColumnName(3) &" LIKE '%" & Search & "%' OR " _
& ColumnName(4) &" LIKE '%" & Search & "%' OR " _
& ColumnName(5) &" LIKE '%" & Search & "%' OR " _
& ColumnName(6) &" LIKE '%" & Search & "%' OR " _
& ColumnName(7) &" LIKE '%" & Search & "%' " & _
"Order by " & ColumnName(5) & " ASC")
lbl_numberofresults.Text = ("Displaying " & Cursor1.RowCount & " results.")
Dim bitmap1 As Bitmap
For i = 0 To Cursor1.RowCount - 1
Cursor1.Position = i
bitmap1.Initialize(File.DirAssets, (Cursor1.GetString(ColumnName(7))) )
lv_SearchResults.AddTwoLinesAndBitmap((Cursor1.GetString(ColumnName(3))), (Cursor1.GetString(ColumnName(2))) & " Diameter: " & (Cursor1.GetString(ColumnName(5))),bitmap1)
Next
Cursor1.Close
Dim p As Phone
p.HideKeyboard (Activity)
End Sub
I have attached a copy of the database.
The steps that I have taken to research this problem is exploring using a Full Text Search (FTS) and creating a virtual table to use the MATCH function of SQL. I just don't know if B4A supports any of the FTS extensions.
I have also researched the REGEX function to separate the individual words. Then use a do while loop to search and return the results to the list view.
I just don't know what the most efficient approach will be. I also want to choose the best route before educating myself toward the best answer.
My background is using Visual Basic for Applications (Excel) and I am new to the SQL environment. I have read the Beginners manual and taken the SQL tutorials suggested.
Any Help is appreciated.