Welcome to WebmasterWorld Guest from 23.20.230.24

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

searching multiple fields, with multple keywords

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

WebmasterWorld Senior Member 10+ Year Member



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.
3:14 am on Feb 12, 2013 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
11:58 am on Feb 13, 2013 (gmt 0)

5+ Year Member Top Contributors Of The Month



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.
12:44 pm on Feb 13, 2013 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
4:01 pm on Feb 13, 2013 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



>> are you sure!

hehe, your answer is much more complete topr8
11:58 pm on Feb 13, 2013 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



thanks jatar_k ... kind words!

... and that's why you're a good mod
7:42 pm on Feb 14, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month