CountryGirl Posted January 27, 2010 Share Posted January 27, 2010 With the search I'm working on, I need people to be able to search by several things - Account, Name, etc. Right now this is what I have for that: WHERE asmnt_parcel.Account = '{$search}' OR asmnt_parcel.OwnersName = '{$search}' OR asmnt_parcel.ParcelID = '{$search}' OR asmnt_legal.Legal = '{$search}' OR appr_miscimpr.Account = '{$search}' I have to tie in several more tables, because not all of the account numbers are in asmnt_parcel.Account. Some are in other tables. So, if someone tries to search by an account number that's not in asmnt_parcel or appr_miscimpr, they won't pull anything up. So, my question is, what is the most efficient way to let someone search by certain things? Should I just list all the table names & rows that will need to be able to be searched, like I've done with several above? There seems like there should be a simpler, more refined way to do this. Thanks for putting up with all my questions! Qadoshyah Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/ Share on other sites More sharing options...
fenway Posted January 28, 2010 Share Posted January 28, 2010 Well, you can build the query knowing what the user is searching for -- but otherwise you need fulltext searching of some sort. Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/#findComment-1002799 Share on other sites More sharing options...
CountryGirl Posted January 28, 2010 Author Share Posted January 28, 2010 but otherwise you need fulltext searching of some sort. fulltext searching?? Is that similar to what I have up already or is that different? Qadoshyah Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/#findComment-1003190 Share on other sites More sharing options...
CountryGirl Posted January 28, 2010 Author Share Posted January 28, 2010 Okay, I'm looking up fulltext searching and I have some questions. I'm look at a tutorial online. This is one of the code examples given: "SELECT *, MATCH(title, body) AGAINST('$keyword') AS score FROM articles WHERE MATCH(title, body) AGAINST('$keyword') " I have multiple tables that I've joined into my search. Do I need to put the MATCH or WHERE MATCH statements at the end of every table I have joined in? Is there a code/clause I can put in that is like a wildcard or something where whatever the person searches will return? Is that kind of what $keyword is? And the (title, body) are the tables it is selecting from? Excuse any dumb questions, this is all new to me and I'm trying to understand this right! Qadoshyah Now Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/#findComment-1003199 Share on other sites More sharing options...
CountryGirl Posted January 28, 2010 Author Share Posted January 28, 2010 Also, another question: Do I need to index every field in the tables that I want to be fulltext searched? Qadoshyah Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/#findComment-1003211 Share on other sites More sharing options...
fenway Posted February 1, 2010 Share Posted February 1, 2010 You need to make a single fulltext index with all the columns you want to incloude. Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/#findComment-1004813 Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 You need to make a single fulltext index with all the columns you want to incloude. I really don't think that can be done with what I have to work with. To put every field that I need from all 9 tables into one table??? Is that what you're saying I could do? Qadoshyah Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/#findComment-1004848 Share on other sites More sharing options...
fenway Posted February 1, 2010 Share Posted February 1, 2010 See my response to your other post -- and lets' try and keep this related topic all is a single thread, ok? Link to comment https://forums.phpfreaks.com/topic/190018-most-efficent-way-to-search-using-a-certain-word/#findComment-1004985 Share on other sites More sharing options...
Recommended Posts