Mysql Fulltext Search In Php Options

[inaki]

Movable Type's internal search engine is not as good as many of us would want it to be, and it brings a heavy load to our servers. I have been looking for a replacement search engine for our blogs (we even considered using Google Search as some do), and in the end I decided to try to create a simple search using PHP and MySQL's fulltext search.

We have been using it for a few days and it seems to work well and much faster than MT's internal search engine. If you want to see it in action try this search for Ronaldinho, or the iPod nano (sorry, the content is in Spanish)

So, I am sharing my work with you all, someone might find it useful and I'm sure some of you will find some bugs. Here go the instructions:

1- Enable dynamic publishing.

Even if you don't use dynamic publishing for any of your templates, you need to enable it for this search to work. For instructions, check Enabling Dynamic Publishing on MT's manual

2- Create the fulltext index.

We need to create a full-text index for the mt_entry table. For this, you need access to your database, using PHPmyAdmin or any other MySQL client. The sentence to create the index is:

CODE

ALTER TABLE `mt_entry` ADD FULLTEXT `fulltext` (
`entry_title` ,
`entry_text` ,
`entry_text_more`
)

3- Create the file block.MTSearchEntries.php

Create the file MT/php/plugins/block.MTSearchEntries.php with this code:

CODE

<?php

/*

# PHP MTSearchEntries
# -------------------
# Created by Iñaki (inaki@weblogssl.com), December 2005

Use fulltext MySQL search to search entries.

Example use:

<MTSearchEntries query="iPod" lastn="25">
<a href="<$MTEntryPermalink$>"><$MTEntryTitle$></a><br/>
<p><$MTEntryExcerpt$></p>
<br />
</MTSearchEntries>

You can use any Entry tags inside the MTSearchEntries block.

*/


function smarty_block_MTSearchEntries($args, $content, &$ctx, &$repeat)
{
   $localvars = array( 'entry', '_entries_counter', '_entries_lastn', 'entries' );

   if (!isset($content)) {
       // initialization and setup...
       $ctx->localize($localvars);
       $counter = 0;
       $lastn = $args['lastn'];
       $query = $args['query'];
       $order = $args['order'];
       $ctx->stash('_entries_lastn', $lastn);
   } else {
       // code that executes iteratively while $repeat == true
       // optionally set $repeat to true to loop  
       // manipulate the data in $content if so desired
       $lastn = $ctx->stash('_entries_lastn');
       $counter = $ctx->stash('_entries_counter');
   }

   $entries = $ctx->stash('entries');
   if (!isset($entries)) {
       $args['blog_id'] = $ctx->stash("blog_id");
       $query = str_replace( "'", "\\'", $query );
       $sql = "SELECT * FROM mt_entry, mt_author WHERE entry_blog_id = '".$args["blog_id"]."' AND entry_status = 2 AND MATCH (entry_title, entry_text, entry_text_more ) AGAINST ('".$query."')  AND entry_author_id = author_id ";
       if( $order == "date" ) {
           $sql .= " ORDER BY entry_created_on DESC ";
       }
       if( $lastn != "" ) {
           $sql .= " LIMIT $lastn ";
       }
       $args['lastn'] = $lastn;
       $entries = $ctx->mt->db->get_results( $sql, ARRAY_A );  
       $ctx->stash('entries', $entries);
       $ctx->stash('_entries_lastn', $lastn);
   }
   else
   {
       // code that executes iteratively while $repeat == true
       // optionally set $repeat to true to loop  
       // manipulate the data in $content if so desired
       $lastn = $ctx->stash('_entries_lastn');
       $counter = $ctx->stash('_entries_counter');
   }
   if (($lastn > count($entries)) || ($lastn == -1)) {
       $lastn = count($entries);
       $ctx->stash('_entries_lastn', $lastn);
   }
   if ($lastn ? ($counter < $lastn) : ($counter < count($entries))) {
       $entry = $entries[$counter];
       if ($counter > 0) {
           $last_entry_created_on = $entries[$counter-1]['entry_created_on'];
       } else {
           $last_entry_created_on = '';
       }
       if ($counter < count($entries)-1) {
           $next_entry_created_on = $entries[$counter+1]['entry_created_on'];
       } else {
           $next_entry_created_on = '';
       }
       $ctx->stash('entry', $entry);
       $ctx->stash('_entries_counter', $counter + 1);
       $repeat = true;
   } else {
       $ctx->restore($localvars);
       $repeat = false;
   }
   return $content;
}

?>

4- Create the file function.MTSearchEntriesCount

Create the file MT/php/plugins/function.MTSearchEntriesCount.php with this code:

CODE

<?php

/*

# PHP MTSearchEntriesCount
# ------------------------
# Created by Iñaki (inaki@weblogssl.com), December 2005

Use fulltext MySQL search to search entries. Counts the number of entries that match the criteria.

Example use:

<MTSearchEntriesCount query="iPod">

*/

function smarty_function_MTSearchEntriesCount( $args, &$ctx ) {
   $blog = $ctx->stash('blog');
   $entrycount = 0;
   $blog_id = $blog['blog_id'];
   $query = $args['query'];  
   $query = str_replace( "'", "\\'", $query );
   $sql = "SELECT count(*) as CNT FROM mt_entry WHERE entry_blog_id = '".$blog_id."' AND MATCH (entry_title, entry_text, entry_text_more ) AGAINST ('".$query."') ";
   // print $sql;
   $result = $ctx->mt->db->get_var( $sql );
   return $result;
}
?>

5- Create the search template

Create a "Index Template" in MT, with:

- Template name: Search
- Output file: search.php
- Build options: Enable dynamic building for this template
- Template body:

CODE

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<$MTPublishCharset$>" />
<meta name="generator" content="http://www.movabletype.org/" />

{{assign var="query" value=$smarty.request.query|replace:'\"':'"'|replace:"\'":"'"}}
{{assign var="order" value=$smarty.request.order}}

<title>{{$query}} - <$MTBlogName$></title>
<link rel="stylesheet" href="<$MTBlogURL$>styles.css" type="text/css" />
</head>

<body>

<div id="container">

<div class="content">
<h1>PHP Search Test Page</h1>
<br / >
<form method="get" action="<$MTBlogURL$>search_test.php">
<p><input type="text" size="40" name="query" value='{{$query|escape:html}}' />
<input type="submit" value="Search" /></p>
</form>

<br />

<?php
global $query;
$query = str_replace( "\'", "'", $query );
$n = $this->tag('MTSearchEntriesCount', array( "query" => $query ) );
if( $n == 0 ) {
?>
<h1>No results found</h1>
<?php
} else {
?>
<h1>Search results</h1>
<p>1-<?php echo min($n, 25); ?> of <?php echo $n; ?> results</p>
</p>
<br/>

<MTSearchEntries query="`$query`" lastn="25" >

<h1><a href="<$MTEntryPermalink$>"><$MTEntryTitle$></a></h1>
<p><$MTEntryExcerpt google_highlight="`$query`"$></p>
<br />

</MTSearchEntries>
<?php
}
?>

</div>

</div>

</body>
</html>

You can use any MT tags in this template, this is just a very simple example. You can include any other MT templates (ie, banners), and use any MT Tags (for example, Entry tags inside the MTSearchEntries block)

Save this template.

You're all set, now you just have to go to yourdomain.com/search.php and see it working.

Hope this is helpful for some of you, and if you see any bugs please tell me!

Thanks
Inaki

[arvind]

That's nice, you should consider packaging it up into a plugin. Albeit it there is one already out there in alpha/beta and its called Seeker You could talk to Seeker's author and collaborate

[Kevin Aylward]

Has anyone else implemented this code? It sure looks nice, but I'd like to see a few more sites that are using it.

What about multi-blog setups? Are searches accross all blogs?

[elapin]

I can't use dyanmic build in Yahoo hosting. Is there any way to set static template instead of dynamic for this php searching function.?
THank you

[ITX]

Hi I use this way to search my site but when i search for example N70 Permalink dont work, in html tags show,

CODE

<h1><a href=""> N70</a></h1>

and link to the entry dosen't work, what is problem? what i must do to work fine?

[ITX]

nobody know?? is this support forum???

[inaki]

Hi I use this way to search my site but when i search for example N70 Permalink dont work, in html tags show,

CODE

<h1><a href=""> N70</a></h1>

and link to the entry dosen't work, what is problem? what i must do to work fine?

Hi, ITX, try searching for a 4-characters word and see if everything works ok. If it does, then the search for N70 doesn't work because your MySQL server is set to search for words of 4 or more characters, which is the default configuration.

If you have access to the MySQL server configuration, you can change the minimum word length as explained here

[inaki]

Hi all,

Mark Carey from mt-hacks has done a great job packaging this fulltext search as a full-fledged MT plugin, you can find it here:

http://mt-hacks.com/fastsearch.html

Check it out, you'll see it is really easy to install this plugin.

Iñaki