Search engine - keywords?

[silvy]

I'm a newbie dabbling in the occasional case of php ( though more often I'll be nicking and modifying scripts my younger brother made ).

What I want to do is upload some information to a MySQL-database - this isn't a problem, my code may be far from perfect but it works.
What I need help with is how to collect information from the database afterwards - i.e actually putting it to good use.

In this particular case, I want 4 variables written to a FAQ database: Question, Poster, Date and Answer, where Q and A should be able to contain rather long stretches of text.
I want to create a search engine that will browse the Question and Answer fields of the database and return all posts that contain whatever keyword/s was entered into the search engine. I just don't know where to start.
If someone would give me some detailed, solid clues I'd be eternally grateful - and if someone would like to sit down and type together some actual working code for the whole thing I'd be willing to pay a few bucks for their trouble.

---

Then a second, somewhat similar question for another database - I want to add a field for multiple keywords to the posts, but how do I collect them afterwards? Not through a search engine, but as part of webpages - say I have a database post with the keywords 'daffodil' and 'sun', one with 'sun' and 'flower' and one with 'daffodil' and 'rain', and I want to display all posts containing the keyword 'sun' on a page?

Since there are multiple keywords, I can't just use an ordinary 'display all yadda yadda where variable = certain word', can I? Is there some command like 'display all where variable contains certain word'?
Geh, I'm not sure if anyone can decipher my ramblings and understand what I'm getting at - it's been a while since I last did anything php-wise.

[justsomeone]

first one is straightforward.

"SELECT * FROM yourtablename WHERE question LIKE '%butthead%' OR answer LIKE '%butthead%'"

You just need to sort out the table and field names and drop your form variable in instead of butthead.

second post same thing 🙂

instead of contains use LIKE'%whatever%' the percentages are wildcards so this means "with whatever somewhere in the field".

LIKE 'whatever%' would mean "starts with whatever"
LIKE '%whatever' would mean "ends with whatever"
LIKE '%first%second%' would mean has the word "first" somewhere in it, with the word "second" somewhere after it etc etc...

Hopefully this will save ya a few bucks 🙂

[silvy]

justsomeone wrote:

first one is straightforward.

"SELECT * FROM yourtablename WHERE question LIKE '%butthead%' OR answer LIKE '%butthead%'"

Hopefully this will save ya a few bucks 🙂

Ah, this definitely looks promising! The %s makes all the difference. Thank you! I think I get it. 🙂

[silvy]

Follow-up question: Will this pick up all words containing the right combination of letters, or just defined words?
To use the example in the first post, would a search for 'sun' include words like 'sunflower' or only the word 'sun' itself?

[justsomeone]

the above code will match "sun" with "sunflower". if you want somethng more specific, it gets a little harder. You'll need to immerse yourself in the subject of "regular expressions".

I didn't realise until just now that mysql has builtin support for regexps... here's a quick tutorial on the topic.

[silvy]

Hm, it does look a tad more complicated, but I shall just have to experiment. Once again great thanks! 🙂