After some MySQL help & advice

[Jeff FV]

Please be gentle - I'm trying to teach myself the basics of MySQL and I'm after a little help.:|

Is it possible to search for a keyword within a field and then return that field if the keyword is in the field, but not at the start of the field?

For example, say I'm interested in looking for the word "frogs". I know how to grab the data from a field if it appears at the start of the field using:

"SELECT * FROM SomeTable WHERE description LIKE 'Frogs' "

So if SomeTable was:

ID Description
1 Frogs live in ponds
2 You might find frogs in ponds
3 Tadpoles grow in ponds

The code above would return:
1 Frogs live in ponds

but not:
2 You might find frogs in ponds or
3 Tadpoles grow in ponds

What I'm asking is, is there a way to interrogate the table to return:

1 Frogs live in ponds
2 You might find frogs in ponds

because they both contain the word "frogs", albeit in different places?

Sorry if this is a really basic question, but I'd love to know the answer!

Many thanks in advance,

Jeff

 

[adam391]

Jeff.

Try "SELECT * FROM SomeTable WHERE description LIKE '%Frogs%' "

this should return both

1 Frogs live in ponds
2 You might find frogs in ponds

 

[Jeff FV]

Brilliant! Many thanks Adam, that works and was just what I was looking for!

Thanks again,

Jeff

 

[Kev Jaques]

That's not the most efficient way, there are plenty ways to skin a cat

If you have Full Text indexing on the column in question you can use a match against in the where clause.
"SELECT * FROM SomeTable WHERE MATCH(description) AGAINST ('Frogs')"

Also be sure to sanitize any user input
Sometimes it's often worth doing either a whitelist or retrieve specific words from the database and preg match the user input, then process if it's in the list.

 

[stugster]

http://www.sqlzoo.net/