one, two or more search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

one, two or more search

Post 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.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

This should be in the Database forum.

Code: Select all

SELECT * FROM table1 WHERE mytext LIKE '%ST%' AND mytext LIKE '%JOHN%'
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Re: one, two or more search

Post by Grim... »

Also:
gurjit wrote:the above would give me both records.
Would it? Are you sure?
Post Reply