MySQL equivalent to windows search for *

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

MySQL equivalent to windows search for *

Post by DavJans » Thu May 29, 2014 6:05 pm

In windows you can do a search for *, is there such a thing in MySQL?

example of what I'm looking for would be

SELECT * FROM Table where column= *

Why?

I have a table with 12 columns, I want the user to be able to search my filling in what they are looking for in any column or combination of columns. I can write a switch statement that will do this but with 12 columns that is A LOT of lines (2048 diferent switch statements.)
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

robertem
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 24
Joined: Tue Mar 19, 2013 2:58 pm

Re: MySQL equivalent to windows search for *

Post by robertem » Thu May 29, 2014 7:21 pm

If I read what you are trying to do correctly, I believe you will have to build your query to reference the columns.

Couldn't you just check each of the 12 fields to see if the user entered data into them and build the query based on that information?

Maybe I'm missing something but, that way, it wouldn't be anywhere near 2048 lines, more like 20ish.

DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Re: MySQL equivalent to windows search for *

Post by DavJans » Thu May 29, 2014 7:51 pm

Here is an example of how I have done this with 3 columns, I don't want to do it with 12

Code: Select all

if the hilite of btn "Check" then
       put "1" into sfp
    else
       put "0" into sfp
    end if
    if fld "sJob" <> "" then
       put 1 into tcJob
    else
       put 0 into tcJob
    end if
       if fld "stester" <> "" then
      put "1" into tcTester
   else
      put "0" into tcTester
   end if
    put tcJob & tcTester & sfp into tParam
    put tParam into fld test
    
    if theConnectionID is an integer then
       ## Query the database for data
       switch
          case tParam = "101"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt WHERE job='" & tJob & "'") into theCursor
             break
          case tParam = "000"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt WHERE done IS NULL") into theCursor
             break
          case tParam = "001"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt") into theCursor
             break
          case tParam = "100"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt WHERE job='" & tJob & "' and done IS NULL") into theCursor
             break
          case tParam = "111"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt WHERE job='" & tJob & "' and tester='" & tTester & "'") into theCursor
             break
          case tParam = "010"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt WHERE done IS NULL and tester='" & tTester & "'") into theCursor
             break
          case tParam = "011"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt WHERE tester='" & tTester & "'") into theCursor
             break
          case tParam = "110"
             put revQueryDatabase( theConnectionID, "SELECT * FROM utmt WHERE job='" & tJob & "' and done IS NULL and tester='" & tTester & "'") into theCursor
             break
       end switch
If I do it the exact same way again with 12 search fields that's 12^n -1 = 4095 different switch statements I was off by a few thousand earlier :P . I hope this explains what I'm trying to acomplish.
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

robertem
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 24
Joined: Tue Mar 19, 2013 2:58 pm

Re: MySQL equivalent to windows search for *

Post by robertem » Thu May 29, 2014 8:24 pm

Hi DavJans,

I was thinking about a loop through the fields but maybe I'm not really understang what you are trying to accomplish with your query.

Quick, untested example below (assuming the fields are in a group called "columns" and the field names match the column names.

Code: Select all

   local tQuery, tClauseCount, tColumn
   
   set the text of fld "Query" to empty
   put 0 into tclauseCount
   put "SELECT FROM TABLE WHERE " into tQuery
   
   repeat with x=1 to the number of fields in grp "columns"
      if the text of fld x <> empty then
         put the short name of fld x into tColumn
         put quote & the text of fld x & quote into tClause
         
         if tClauseCount > 0 then
            put " AND " after tQuery
         end if
         
         add 1 to tClauseCount
         put tColumn && "=" && tClause after tQuery
         
   end if 
end repeat
answer tQuery

DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Re: MySQL equivalent to windows search for *

Post by DavJans » Thu May 29, 2014 9:53 pm

Thank you!
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”