Jump to content

Search all fields?


aveach

Recommended Posts

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]

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.