Development MySQL multiple LIKE search not returning expected

running a LIKE query has been easy enough.
now i need to expand it to allow for more then one search term

I have the following

PHP:

SELECT *
FROM `table`
WHERE `field` LIKE '%TERM1%'
OR `field` LIKE '%TERM2%'


I would have expected it to return anything it found to contain EITHER of those search terms.
however it only seems to be returning results for TERM2.

I have data in the fields that match both TERM1 and TERM2

I have obviously over looked something, googling has had me chasing my tail and the documentation is making my eyes bleed.

and im running MySQL Server version: 4.1.22-standard so i cant use the newer better features of 5

any ideas?

 

according to http://www.gieson.com/school/mysql/index.html#rlike

SELECT * FROM table_name WHERE col_A_name RLIKE ('search_termA') AND col_B_name RLIKE ('search_termB') ;

 

Brilliant thank you

 

Your assumption is correct, it should (and does) return for either or bot) term1 or term2.
RLIKE is a regex version of LIKE and performs no differently to LIKE at all.

There's absolutely no reason why your original query would not work (there must be some problem with the data in your table)

 

im starting to think there is something fundamentaly wrong with our MySQL server. because the first query returns a completly different set of results then using the RLIKE query suggested.

both with the same 2 search terms