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.)
MySQL equivalent to windows search for *
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
MySQL equivalent to windows search for *
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här
Re: MySQL equivalent to windows search for *
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.
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.
Re: MySQL equivalent to windows search for *
Here is an example of how I have done this with 3 columns, I don't want to do it with 12
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 . I hope this explains what I'm trying to acomplish.
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
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här
Re: MySQL equivalent to windows search for *
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.
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
Re: MySQL equivalent to windows search for *
Thank you!
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här