searching multiple fields, with multple keywords

[whatson]

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]

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]

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]

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]

>> are you sure!

hehe, your answer is much more complete topr8

[topr8]

thanks jatar_k ... kind words!

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

[whatson]

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.