Matching a word in a field

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
Wardy7
Forum Commoner
Posts: 38
Joined: Wed Aug 24, 2005 4:45 am
Location: UK

Matching a word in a field

Post by Wardy7 »

I have a site I write news articles for that I want to be able to list certain stories that contain a certain word to be displayed on a page but I am unsure on how to do the search for this as currently I can only display things when I match a complete record which is too restrictive.

This is how I currently how I feth the news to one of the pages but it's not how I wnat to do it to other pages.
$query = "SELECT title,news,publisher,url,date,newsid FROM news WHERE newsid='$newsid' ORDER BY date";

An example of how I would like it would be so it displayed all news articles that matched 1 word in the "title" field for example if the title field contained the word "car".

I'm guessing this is pretty simple but having had a look on php.net I can't figure it :oops:

Cheers
Wardy
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

you need to use LIKE in your SQL statement:

Code: Select all

SELECT * FROM NEWS WHERE TITLE LIKE '%$searchterm%'
The percentage signs are the SQL equivalent of the wildcard * and means "match any characters".
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post by printf »

If you want to match exact whole words then use a regex....

examples (sail, motor)

using LIKE...

Would match things like (sail, sailing, sailboat, motorboat, motors)

Using a regex

Would match only whole words (sail, motor)

Code: Select all

SELECT title FROM my_table WHERE title REGEXP '[[:<:]]sail|motor[[:>:]]';

pif
Post Reply