Creating a search function?

[HaLo2FrEeEk]

Hey, I'm in the process of redoing the frontpage for my website and I want to implement a search function.  It will be a simple textbox that will allow me to search through the news posted to the frontpage.  I have a basic query down that I can use:

 

SELECT * FROM `news` WHERE `news_title` LIKE '%[searchword]%' OR `news_text` LIKE '%[searchword]%'

 

The thing is, if the search word is, for example, "read", the word thread has read in it, so it returns that.  Is there a way to only return results where the word read is present, not that word inside another?  Another thing is, is this a good basic function for a search?  Could I use this?

 

Thank you.

[DarkWater]

You sure you want that? It'd be something like

$sql = "SELECT * FROM news WHERE news_title LIKE '% $search %' OR news_text LIKE '% $search %'";

 

Notice the spaces.  It SHOULD work.

[hitman6003]

What storage engine are you using?  MyISAM (and Falcon and Maria) all have builtin full text indexing, which is a VAST improvement over the 'WHERE column LIKE "%word%"' SQL queries.

 

Aside from using one of the above storage engines (Falcon and Maria are MySQL 6 only at the moment, so it's MyISAM or bust) you can use an external full text indexer...Lucene, sphinx, etc.

[DarkWater]

He doesn't need a FULLTEXT for his "basic search".  I use FULLTEXT for most of my searches, but this one is so simple that it's almost complete overkill.

[hitman6003]

He doesn't need a FULLTEXT for his "basic search".  I use FULLTEXT for most of my searches, but this one is so simple that it's almost complete overkill.

 

He never stated it was a "simple search".  If all content on the site is considered "news", then search becomes vital.  Inaccurate search is worse than no search in most cases, so fulltext indexing may be the best solution.

[HaLo2FrEeEk]

It is simple, I only want the search to return news results.  Currently there are only 86 news posts if my database reports correctly, which it does.  I add news on average once a day, but sometimes less.  The forums have their own search function built in.  I might modify this to include anything else on the frontapge that's in the database, so news, roster, affiliates, and tutorials.  However, that would be the extent of it.  I thought about the spaces trick in the first reply, didn't get to test it because I had to work.  It seems to work fine, if there is, however, anything that I could use that would better serve me, please suggest it, I would love to look into it, but for all intents and purposes, will this get the job done for me ok?