Jump to content


Photo

Search all fields?


  • Please log in to reply
2 replies to this topic

#1 aveach

aveach
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 04 January 2003 - 05:35 AM

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]

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 06 January 2003 - 03:35 PM

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]


Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 08 January 2003 - 10:13 AM

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




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). :D


$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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users