homepage Welcome to WebmasterWorld Guest from 204.236.254.124
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
searching multiple fields, with multple keywords
whatson




msg:4538736
 7:39 pm on Jan 23, 2013 (gmt 0)

I want to search a few columns in mysql for a search term. e.g. London Hotels, I would want to search each column (name, title, content) for 'London' and 'Hotels'. How is this done?

I am very new to search, so if I am missing something major then please let me know.

 

jatar_k




msg:4544792
 3:14 am on Feb 12, 2013 (gmt 0)

at the simplest I guess something along the lines of

select * from tablename where name='London Hotels' or title='London Hotels' or content='London Hotels'

but that would only match exact strings, there are other ways to match variable characters such as using % but these searches get pretty resource intensive. Text searching definitely requires a fair bit of reading.

adder




msg:4545278
 11:58 am on Feb 13, 2013 (gmt 0)

How big is the database and how many columns do you have? If it's huge, you might want to create a system that indexes your main database and saves the searchable data in another database.

Then your search script runs on the index database and simply references the entries from the main database. In other words, your index database would only have id, name, title, content columns (and the item's id is identical to the id on the main database).

Otherwise, if you do "select * from tablename" on a huge database, as jatar_k said, it will be hellishly resource-intensive.

topr8




msg:4545298
 12:44 pm on Feb 13, 2013 (gmt 0)

i never thought i'd say to jakar_k are you sure!

but i'd have thought it would be more like:
select * from tablename where name LIKE '%London Hotels%' or title LIKE '%London Hotels%' or content LIKE '%London Hotels%'

that is assuming that the entire field is not exactly equal to the search string

it would be even more complex if you were searching for the words seperately rather than as a phrase.

i'd suggest you create a FULL TEXT INDEX on the columns you want to search

and then you would query it something like:
select * from tablename where MATCh(name,title,content) against('London','Hotels')

this way you'd be searching for the two words to appear but not neccesairily as a phrase or in the same columns, i'm a bit rusty with text search so my syntax my be slightly out.

or search for the phrase thus:
select * from tablename where MATCh(name,title,content) against('London Hotels')

by default the full text index only indexes words of 4 letters and above i think, if i use it i tend to reset it to words of 3 letters and above.

jatar_k




msg:4545340
 4:01 pm on Feb 13, 2013 (gmt 0)

>> are you sure!

hehe, your answer is much more complete topr8

topr8




msg:4545496
 11:58 pm on Feb 13, 2013 (gmt 0)

thanks jatar_k ... kind words!

... and that's why you're a good mod

whatson




msg:4545712
 7:42 pm on Feb 14, 2013 (gmt 0)

It wouldnt be like that, it would be like

select * from tablename where name LIKE '%London% || %Hotels%' or title LIKE '%London% || %Hotels%' or content LIKE '%London% || %Hotels%'

But need some way to count that at least london and hotel both occurred in one area at least once.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved