Firstly before I start I’d just like to say I think Trading Eye is a great piece of software and I think the guys at Dpivision have done a great job with it. Still there are some shortcomings and one of my major issues is with the search which I believe would be fixed with version 6 but from first look hasn’t.

Firstly if you search with an apostrophe there’s a good chance of breaking the search e.g. searching for (dave’s book). Secondly if you want a red book and searched with the phrase “red book” you wouldn’t get a match unless the exact phrase “red book” in that order was somewhere in the product title or description, even if the phrase “book red” was there that wouldn’t show up either.

So here’s my fix  which currently works for version 5 and in the near future after I’ve had  a poke around version 6 I hope to give you some adjustments for that.

Before we start, what you need :

• Access to your database, or if you’re not confident or allowed to mess with it a responsible adult/developer who can.
• The search.php script located in /modules/ecom/classes/main

Also before we start backup both your database and your search.php file, the last thing I want is people telling me this broke something and they can’t get it back!

Step 1:  Prepare your database

Have a look at your list of tables, if you’re using the default table names you should have

te5_tbShop_Products
and
te5_tbShop_content

If you aren’t using default table names you should hopefully at least have something similar.

You then need to open an SQL window and stick in the following two queries,

[sourcecode language=”SQL”]
ALTER TABLE `te5_tbShop_Products` ADD FULLTEXT (vTitle, tShortDescription, tContent)
ALTER TABLE `te5_tbShop_content` ADD FULLTEXT (vTitle, tShortDescription, tContent)
[/sourcecode]

(Obviously substituting the table names if yours are different)

If your queries execute ok then you’re ready for step 2.

Step 2 – Fixing the apostrophe problem

From what I can tell this essential stems from different php options with magic quotes.  So around line 24 should be a few lines of code like this
[sourcecode language=”PHP”]
function m_searchResults()
{
if(empty($this->request[‘mode’]))
{
$this->request[‘mode’]=””;
}
[/sourcecode]

After these add in the following few lines

[sourcecode language=”PHP”]
if(!get_magic_quotes_gpc()) $search_term=addslashes($this->request[‘mode’]);
/*from now on all references to $search_term have replaced $this->request[‘mode’]
Just if you ever wanted to put it back*/
[/sourcecode]

Once that’s changed quickly open up your site and do a quick search, if all is ok then carry on down the document replacing $this->request[‘mode’] with $search_term. DO NOT replace any of them above the new line of code!

Step 3 – Fixing the search problem

Next you need to go to around line 54 and look for the following code

[sourcecode language=”PHP”]

#TO QUERY DEPARTMENT TABLE

$this->obDb->query = “SELECT iDeptid_PK,vTitle,vSeoTitle,tShortDescription FROM “.DEPARTMENTS.” WHERE “;
$this->obDb->query .=”(vTitle LIKE ‘%”.$search_term.”%’ OR “;
$this->obDb->query .=”tShortDescription  LIKE ‘%”.$search_term.”%’ OR “;
$this->obDb->query .=”tContent LIKE ‘%”.$search_term.”%’)”;
$rowDept = $this->obDb->fetchQuery();

[/sourcecode]

We need to change that for this

[sourcecode language=”PHP”]

$this->obDb->query = “SELECT iDeptid_PK,vTitle,vSeoTitle,tShortDescription, “;
$this->obDb->query .=”MATCH (vTitle, tShortDescription, tContent) AGAINST (‘”.$search_term.”‘) as Relevance “;
$this->obDb->query .=”FROM “.DEPARTMENTS.” WHERE “;
$search_words=explode(” “,$search_term);
$count_words=count($search_words);
$loop_count=1;
foreach($search_words as $word)
{
$this->obDb->query .=”(“;
$this->obDb->query .=”vTitle LIKE ‘%”.$word.”%’ OR “;
$this->obDb->query .=”tShortDescription  LIKE ‘%”.$word.”%’ OR “;
$this->obDb->query .=”tContent LIKE ‘%”.$word.”%’ “;
if($loop_count < $count_words) $this->obDb->query .=”) AND “;
else $this->obDb->query .=”) OR  “;
$loop_count++;
}
$this->obDb->query .=” MATCH (vTitle, tShortDescription, tContent) AGAINST (‘”.preg_replace(” “, ” +”, $search_term).”‘ IN BOOLEAN MODE)”;
$this->obDb->query .=”ORDER BY Relevance DESC”;

[/sourcecode]

Next we’ll find the product query which should be around line 109 by now and looks like this

[sourcecode language=”PHP”]

#TO QUERY PRODUCT TABLE

$this->obDb->query = “SELECT iProdid_PK,vTitle,vSeoTitle,vImage1,tShortDescription FROM “.PRODUCTS.” WHERE “;
$this->obDb->query .=”vTitle LIKE ‘%”.$this->request[‘mode’].”%’ OR “;
$this->obDb->query .=”tShortDescription  LIKE ‘%”.$this->request[‘mode’].”%’ OR “;
$this->obDb->query .=”tContent LIKE ‘%”.$this->request[‘mode’].”%'”;
$rowProduct = $this->obDb->fetchQuery();

[/sourcecode]

(as a quick note your query may not have vImage1 in the fields this was added after using the adding product images to search results page modification)

and replace it with this

[sourcecode language=”PHP”]

$this->obDb->query = “SELECT iProdid_PK,vTitle,vSeoTitle,vImage1,tShortDescription, “;
$this->obDb->query .=”MATCH (vTitle, tShortDescription, tContent) AGAINST (‘”.$search_term.”‘) as Relevance “;
$this->obDb->query .=”FROM “.PRODUCTS.” WHERE “;
$search_words=explode(” “,$search_term);
$count_words=count($search_words);
$loop_count=1;
foreach($search_words as $word)
{
$this->obDb->query .=”(“;
$this->obDb->query .=”vTitle LIKE ‘%”.$word.”%’ OR “;
$this->obDb->query .=”tShortDescription  LIKE ‘%”.$word.”%’ OR “;
$this->obDb->query .=”tContent LIKE ‘%”.$word.”%’ “;
if($loop_count < $count_words) $this->obDb->query .=”) AND “;
else $this->obDb->query .=”) OR  “;
$loop_count++;
}
$this->obDb->query .=” MATCH (vTitle, tShortDescription, tContent) AGAINST (‘”.preg_replace(” “, ” +”, $search_term).”‘ IN BOOLEAN MODE) “;
$this->obDb->query .=”ORDER BY Relevance DESC”;
$rowProduct = $this->obDb->fetchQuery();

[/sourcecode]

That should fix it.

Before you close the file near the top after the opening comments drop this in

[sourcecode language=”PHP”]

/***************

file modified for search fixes

http://www.kamikazemusic.com

***************/

[/sourcecode]

That’ll just make sure you have a note that the file has been modified it you ever upgrade it and will also tell you where to come if you forget what you’re doing.

Step 4 – Test it

Upload the file (keep the backup handy) and give the search a test, try different order of words etc. The results should be sorted automatically by relevance to the search phrase.

I’m hoping to have a poke around version 6 soon and bring you any changes that need to be made to the code to integrate the search in filter that is in version 6. I’ll also look at ordering by price, a-z by title and paging – any preference as to what you’d like first let me know!

Hope this helps.

Problems?

Any problems you can send me a message or leave a comment below. Please try to include details of any error messages and copies of the code changes you’ve made (it helps if you attach the search.php file to an email!)