Page 1 of 1

one, two or more search

Posted: Thu Jan 25, 2007 3:37 am
by gurjit
Hi,

In MYSQL how can I do a search on one or more words searching multiple fields?

For example, if I searched "ST JOHN" and we had a field in the database named "mytxt", which was datatype "TEXT" with the following text

1. "Today it was reported st john road has been closed"
2. "Joe walked across the road"

I want to search the left of the word "ST" and "JOHN". Only record 1. should be found. Record 2 containing "JOE" matching "JOHN" should not appear


How can I do this?

Code: Select all

select * from table1 where mytxt like '%ST JOHN%'
the above would give me both records. How can I just get record 1 because it matched "ST" and "JOHN".

I should be able to search for "JOHN" and get record 1 only or search for "JOE" and get record 2 only.

Posted: Thu Jan 25, 2007 8:32 am
by Grim...
This should be in the Database forum.

Code: Select all

SELECT * FROM table1 WHERE mytext LIKE '%ST%' AND mytext LIKE '%JOHN%'

Re: one, two or more search

Posted: Thu Jan 25, 2007 8:33 am
by Grim...
Also:
gurjit wrote:the above would give me both records.
Would it? Are you sure?