Using Fulltext searches
You've got a website. It is full of content. This content is stored in a MySQL database (If it isn't you will want to migrate everything over; I don't know of any methods (other than Google) to search content in flat html files). How do you search this content? Keep reading. (BTW this tutorial assumes you know how to use forms and filter data).
CREATE TABLE `bible_asv` ( `id` int(11) NOT NULL auto_increment, `book` int(11) NOT NULL default '0', `chapter` int(11) NOT NULL default '0', `verse` int(11) NOT NULL default '0', `text` text collate latin1_general_ci NOT NULL, PRIMARY KEY (`id`), KEY `book` (`book`), KEY `chapter` (`chapter`), KEY `verse` (`verse`), FULLTEXT KEY `text` (`text`) ) ENGINE=MyISAM AUTO_INCREMENT=31103 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=31103 ;
As you can tell, for this example I am using the asv version of the Holy Bible. I already went through the work of reformatting the version I was able to download. After setting the 'columns' of the database table, there are a bunch of keys. Using a lot of keys will make inserting records into the table slower and searches faster. Since I will not be adding any more entries, this is not a problem. Setting the text column to fulltext is the magic that makes it searchable.
Before adding the keys and the fulltext attributes, the table was ~8MB. It is now ~14MB. All the added information (bloat) is put off to the side and is used for indexing/searching purposes.
SELECT A. * , B.book AS book FROM bible_asv AS A, books AS B WHERE A.book = B.id AND MATCH ( A.text ) AGAINST ( '$query' ) ORDER BY A.book, A.chapter, A.verse
This is the query that I run to search for a particular Bible verse. This is not a broad tutorial on mysql queries and is assuming that you know what all this means. The $query variable is what the user is searching for (which hopefully you will be escaping). The results of this query is all the attributes of the bible_asv, and the name of the chapter (which I have in a second table called books. books has two columns, id and book).
The Bible search program is just a small project that I'm working on. Changing the 'columns' of the table to something like title, content, author, and date could make the table a skeleton for a custom (searchable) CMS.| Tutorial Links | Our Tutorials | Discussion (2) |