Topic: Search all fields?

[aveach]

I\'m new and i\'m trying to write a query that searches all columns  for a certain variable without writing out the name of every column in the table. Is there a command like
SELECT * FROM mytable WHERE * LIKE\'%...%\'   ?  That would search the entire table for a value?

   I dont want to write SELECT * from mytable WHERE field1 like \'%somevar%\' OR field2  like \'%somevar%\' OR ......

Or even a way of indexing the whole database in one column then i could search that column in my query?
How do i go about this. Thanks[/i][/u]                    

[biopv]

The answer is NO. There is no smart way to search all coloumns for a given string. But why would you do that?

(If you really want to, then do as you suggest in your second line.)

You COULD combine all coloumns into one large string and put that into a new coloumn - but that is not nice at all.

The idea behind having a database disappears if you start collapsing information into one huge pile and then searches it like that.

I\'m curious - what kind of data would you like to search in such a manner ?

P., denmark

I\'m new and i\'m trying to write a query that searches all columns  for a certain variable without writing out the name of every column in the table. Is there a command like
SELECT * FROM mytable WHERE * LIKE\'%...%\'   ?  That would search the entire table for a value?

   I dont want to write SELECT * from mytable WHERE field1 like \'%somevar%\' OR field2  like \'%somevar%\' OR ......

Or even a way of indexing the whole database in one column then i could search that column in my query?
How do i go about this. Thanks[/i][/u]

                   

[biopv]

Also sent by mail to original questioner...

Ok, just came up with an idea...

You MAY be able to combine all fields in the search using

Code: [Select]




select * from table where

concat(field1, field2, field3,....field20) like %$searchterm%;



(I\'m not sure if I remeber correctly with concat but anyway there is a mysql order for combining fields. I\'m not sure about speed though - it might be quite slow...

CORRECTION: I just checked my own code... It works. (This is for a adress database and dive logging - danish field names, sorry about the confusion).  

Code: [Select]


$sql->Query("SELECT

p.Prs_PersonID,

CONCAT(p.Prs_Fornavn,\' \',p.Prs_Efternavn),

p.Prs_Status,

p.Prs_Email,

p.Prs_WWW,

COUNT(tbl_Noter.Note_NoteID),

tbl_Dykkererfaring.Dykef_LoggedeDyk



FROM

(tbl_Person AS p LEFT JOIN tbl_Noter ON p.Prs_PersonID = tbl_Noter.Note_PersonID)

LEFT JOIN tbl_Dykkererfaring ON p.Prs_PersonID = tbl_Dykkererfaring.Dykef_PersonID



WHERE

(// HERE IT COMES!!!

((concat(p.Prs_Fornavn,\' \',p.Prs_Efternavn)) like \'%$navn%\')

AND

(p.Prs_Status like \'$status\')

)

GROUP BY p.Prs_PersonID

ORDER BY $sortby

");





P.