Search/ Matching problem

[asif49]

This code seems to work on a simpler level but not on the one I'm trying to use. It's for a website's search engine.

First the result is gotten from the database like so:

$query = "select * from $tbl_name where name like \"%$var%\" OR text like \"%$var%\""; 

 $numresults=mysql_query($query);
 $num=mysql_num_rows($numresults);

  if ($num == 0) //if no results found
  {
  "No results found";
  }
 
 else {

Then the result is gotten from the database and matched, it's intended to match all the loosely, everything in the array $skip can be skipped over while matching and then the matched terms are highlighted.

$skip = array(" ","-","_","'"); //ignore
  while ($row = mysql_fetch_array($result)) { 
  $name = $row['name'];
  $text = nl2br($row['text']);

  /*stop input's ($var) casing from affecting the output's casing, skip over chars specified in $skip*/
  $name = preg_replace('/([?]*' .  str_replace('@@@', "['\s_-]*",str_replace($skip,'@@@',$var)) . '[?]*)/i', '<b>${1}</b>', $name); 
  $text = preg_replace('/([?]*' .  str_replace('@@@', "['\s_-]*",str_replace($skip,'@@@',$var)) . '[?]*)/i', '<b>${1}</b>', $text);
echo $name . $text;

The above matches substrings (case insensitive) but doesn't skip the terms listed i.e. for $text to be "hello world", "hello" or "world" would be recognised but not "hello-world" which should.

If the same matching is done on a simpler level then it works. By simpler level I mean another smaller script which runs on the same principle for instance the following, which works, which is pretty much the same thing I'm doing (unless I'm missing something)

<?php
$words = "hEllO wOrLD"; 
$skip = array(" ","-","_","'");
$phrase = "I made a program helloworld which prints Hello World hello";
echo preg_replace('/([?]*' .  str_replace('@@@', "['\s_-]*",str_replace($skipchars,'@@@',$words)) . '[?]*)/i', '<b>${1}</b>', $phrase);
?>

[the_traveller]

Hi
i think ur solution is in using Full-Text Search Functions

[asif49]

That could seriously not be more cryptic, do you or someone else who understands that^^ want to explain it to me in a bit more depth ?

[madCoder]

@asif49 means that MySQL's full-text index feature is designed to accomodate natural language searches that are much more powerful than a simple LIKE statement with wildcards. Check out the MySQL manual for details on how to use them.

Here's the manual entry for MySQL 5.0: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

[asif49]

It appears that the full-text search method is REALLY slow, as far as everyone is saying. Also that site doesn't give any examples as to how to put it into the code.

I'm hoping there's a way to correct the error I'm having rather than do that full-text-search stuff.

Hopefully someone sees what's wrong with the code and gets back to me about it...

[the_traveller]

if you consider to change ur mind about using full text search
after adding new full text key with the fields name , text
ur statment should be like this

SELECT $tbl_name ,MATCH (`name`,`text`)AGAINST ('%$var%') AS score
FROM $tbl_name AS $tbl_name
WHERE MATCH (`name`,`text`)AGAINST ('%$var%') 
ORDER BY score DESC

you should try this in phpmyadmin and check out the results