Search + Highlight

[buffy]

Hi all,

Well I thought what I was after was somewhat simple.

I have a search that returns the results no worries... but the non complex examples I look at for hightlighting words never include the search query so am not sure if I'm on the right track.

My end result is to have people type something in in the feild, then for the results to display as:

<a href"...?productid=$ID..>Field1 Field2</a>
Field3 (description)

Repeat as necessary...

I have the following search code which is working fine, displaying a simple result at the moment. I just need to change the query so it searchs all the fields rather then just description.

        $searchsmall=mysql_real_escape_string($_POST['searchsmall']);
        //If they did not enter a search term we give them an error
        if ($searchsmall == "")
        {
            echo "<p>You forgot to enter a search term.";
            exit;
        }
        
        // We preform a bit of filtering
        $searchsmall = strtoupper($searchsmall);
        $searchsmall = strip_tags($searchsmall);
        $searchsmall = trim ($searchsmall);
        
        
        $sql = ("SELECT ID, Part, Name, Name2 FROM product WHERE Description LIKE '%".$searchsmall."%'");
        $query = mysql_query($sql);
            
        //And we display the results
        while($result = mysql_fetch_array($query))
        {
        echo $result['Part'];
        echo " ";
        echo $result['Name'];
        echo "<br>";
        echo $result['Name2'];
        echo "<br>";
        echo "<br>";
        }
        
        
        //This counts the number or results - and if there wasn't any it gives them a little message explaining that
        $anymatches=mysql_num_rows($query);
        if ($anymatches == 0)
        {
        echo "Sorry, but we can not find an entry to match your query<br><br>";
        } 

Here is the example I found to highlight the searched word/s from the results.... how do i fit it in?

/**
 * @highlight words
 * @param string $text
 * @param array $words
 * @param array $colors
 * @return string
 */ 
 
function highlightWords($text, $words, $colors=null)
{
        if(is_null($colors) || !is_array($colors))
        {
                $colors = array('yellow', 'pink', 'green');
        }

        $i = 0;
        /*** the maximum key number ***/
        $num_colors = max(array_keys($colors));

        /*** loop of the array of words ***/
        foreach ($words as $word)
        {
                /*** quote the text for regex ***/
                $word = preg_quote($word);
                /*** highlight the words ***/
                $text = preg_replace("/\b($word)\b/i", '<span class="highlight_'.$colors[$i].'">\1</span>', $text);
                if($i==$num_colors){ $i = 0; } else { $i++; }
        }
        /*** return the text ***/
        return $text;
}

/*** example usage ***/
$string = 'This text will highlight PHP and SQL and sql but not PHPRO or MySQL or sqlite';
$words = array('php', 'sql', 'phpro', 'sqlite');
$string =  highlightWords($string, $words); 

[kows]

first, I noticed that you used strtoupper() on your search term; this is unneeded if you're doing it for easier finding, since MySQL does a case insensitive search (I believe -- I haven't used LIKE in a long time!).

I'm not sure if that function you have would do exactly what you wanted, as it is looking for a bunch of words (array) rather than one string of words (what you have). you could still use it, but instead you could make your own:

<?php
  function highlight_search($needle, $haystack){
    $needle = preg_quote($needle);
    return preg_replace("/\b($needle)\b/i", '<span style="background:#ff8000; color: #000;">\1</span>', $haystack);
  }
?>

which should basically do the same thing. you could then use it like (copying from your code):

        //And we display the results
        while($result = mysql_fetch_array($query))
        {


        /***/
        //loop through every $result and hightlight
        foreach($result as $k => $v){
          //we use untouched $_POST['searchsmall'] instead of $searchsmall
          //--> because you used mysql_real_escape_string() and other functions

          $result[$k] = highlight_search($_POST['searchsmall'], $v);
        }
        /***/


        echo $result['Part'];
        echo " ";
        echo $result['Name'];
        echo "<br>";
        echo $result['Name2'];
        echo "<br>";
        echo "<br>";
        } 

I put comments around the code I added in. and I stole the preg_replace from the function you posted, but it should work fine.

edit: added preg_quote() call to function, forgot about it.

super late edit: forgot you were using strip_tags() on $searchsmall too, so you will probably want to make a second variable that only does a trim and strip_tags on $_POST['searchsmall'], just don't escape any of it. otherwise, you may have results but wouldn't have anything highlighted!

[BrianS]

It would be case insensitive unless you setup the DB otherwise.

first, I noticed that you used strtoupper() on your search term; this is unneeded if you're doing it for easier finding, since MySQL does a case insensitive search (I believe -- I haven't used LIKE in a long time!).

[buffy]

Thanks that worked a treat!

The only thing is if I search for two words, from different feilds, it finds no results

        //query to get results        
        $sql = ("SELECT ID, Part, Name, Name2, Description 
        FROM product 
        WHERE (Part LIKE '%".$searchsmall."%')
        OR (Name LIKE '%".$searchsmall."%')
        OR (Name2 LIKE '%".$searchsmall."%')
        OR (Description LIKE '%".$searchsmall."%')");
        $query = mysql_query($sql); 

[kows]

you need to echo out the query and see what MySQL is actually being sent by PHP. sometimes, you'll have an extra slash or some other character that would be screwing it up. if you can't see any problems, post the printed out query.

[buffy]

The syntax is okay, but if I type in say a part number and a description it searchs the part, name, name2, and description feild for a part and description, which don't exist in the one field.

I have used the following example code which works:

    $searchsmall=mysql_real_escape_string($_POST['searchsmall']);

//We preform a bit of filtering
//$searchsmall = strtoupper($searchsmall); - not required
$searchsmall = strip_tags($searchsmall);
$searchsmall = trim ($searchsmall);
        
//search function
function highlight_search($needle, $haystack){
        $needle = preg_quote($needle);
        return preg_replace("/\b($needle)\b/i", '<span style="background:#000000; color: #cccccc;">\1</span>', $haystack);
      }

// As more than one word could be submitted
// turn the variable into an array
$word = explode(" ",$searchsmall);

//search more then one field
$sql = "SELECT ID, Part, Name, Name2, Description  FROM product WHERE ";
     while ( list ($k, $v) = each ($word)){
     if(!$j){
     $sql .= "Part LIKE '%".$v."%' OR Name LIKE '%".$v."%' OR Name2 LIKE '%".$v."%' OR Description LIKE '%".$v."%'";
     $j = 1;
     }else
     $sql .= "OR Part LIKE '%".$v."%' OR Name LIKE '%".$v."%' OR Name2 LIKE '%".$v."%' OR Description LIKE '%".$v."%'";    
     }
     $sql .= "ORDER BY Name";
     //Not sure how to order by relevance? 


//while loop
//show results and highlight 

But its not highlighting anymore... do I need to change your code (e.g. maybe since I am using $k again in the while loop?)

And a bit off topic... I escape_string a field but it still makes my php go weird if someone enters a + in a field, should escape_string be getting rid of it?

[kows]

mysql_real_escape_string() will not get rid of any plus signs, but it might escape them if it needs to. this won't change anything, though; just make sure you're keeping the variable you used mysql_real_escape_string() on separate from the variables you are using to check for highlights. example:

  $searchsmall = '"quotes" are escaped by MySQL!';; //this is our variable, no escaping
  echo $searchsmall;
  //will return: "quotes" are escaped by MySQL!

  echo mysql_real_escape_string($searchsmall);
  //will return: \"quotes\" are escaped by MySQL!

  //so, we can't use the second variable for highlight_search(), we can do this instead:
  $safe_searchsmall = mysql_real_escape_string($searchsmall);
  //and only use this variable for mysql queries 

and instead of your while loop, this might be a bit more efficient/easier to read:

$sql = "SELECT ID, Part, Name, Name2, Description  FROM product WHERE ";
for($i = 0; $i < count($words); $i++){
  if($i > 0) $sql .= "OR ";
  $sql .= "Part LIKE '%{$words[$i]}%' OR Name LIKE '%{$words[$i]}%' ...";
}
$sql .= " ORDER BY Name"; 

as far as sorting by relevance, I'm really not sure what you mean. you can do multiple sorts within the same query, though.

SELECT * FROM table WHERE something='somethinglese' ORDER BY importantfield1 ASC, importantfield2 DESC, irrelevantfield ASC

now, as far as not highlighting goes, since you're now splitting up your search terms, you will need to highlight every word found. you can do this by doing the exact same for() loop I used above, but calling highlight_search() for part, name, name2, etc on $word[$i] every time. this will highlight each single word in every category.

andd finally, if you type in a part number and description in the search and want to be able to distinguish between the two (eg. which fields to look in), then you'll need to make some sort of way for a user to be specific with one field (like if they want to search for a part, they could input "part:3091-2391" and you could look for part: and take the number and search in the part field), but that may be a bit tricky for average users. so, you could use multiple fields in your search. one field for "search part," one field for description, etc. then, you could also have a field for "search all," if you wanted.

hope that makes sense!

edit: quick revision to code.

[buffy]

Hi kows

Thanks for the more efficient for code. Have put that in.

Tried to add the highlight search to the second for loop (which I placed in the while loop again and removed the old one)... however I didn't quite grasp how to edit it... I think I need to remove the $sql as well?

for($i = 0; $i < count($words); $i++){
  if($i > 0) $sql .= "OR ";
  $sql .= "Part LIKE '%{highlight_search($words[$i])}%' OR Name LIKE '%{highlight_search($words[$i])}%' ...";
} 

And I get what you mean about not using the escaped variable for the highlight function.

I need to add somelike similar to force no + symbols or anything (will prob use javascript) for my quantity fields as people can enter + into quantity and get a price of $0, oops!

As for the relevance, I meant more like if a search has 2 words - one part number, one description word... the search might have 3 results:
1) a result where there was a part number
2) a result where there was a description
3) a result where there was a part number and the description (from the two seperate db fields in the record)

So if record 3 has the most matches then display that first. Its more of a nice to have rather then a need to have.

And in reference to your last note about 'distinguish between the two' this is a good idea and I think I could use it for the advanced search page.

[kows]

oh, I see what you meant by relevance. as far as I know, there is no way to do this in SQL. however, it would be possible with PHP. you would do your regular query, but instead of looping through it and displaying the results, you would need to calculate how many recurrences of the terms there were and sort them according to that. you could do this using preg_match_all() with a simple regular expression of just your search terms. then, you create an array with the numbers of terms matched as the key, and you can use ksort() to sort by the keys. this is a very very rough outline of what you might do, and I didn't really include any tough work (looping through $words array and making a regular expression including all the words, or simpling calling the preg_match_all() function for every word -- either or) because I'm tired:

<?php
  $sql = mysql_query("select * from table where something='%somethingelse%' LIMIT 20");
  $my_results = array();
  while($results = mysql_fetch_assoc($sql)){
    $c = 0; //count of matched terms
    //match things
    $c += preg_match_all('/somethingelse/', $results['part'], $junk_array);
    $c += preg_match_all('/somethingelse/', $results['name'], $junk_array);
    $c += preg_match_all('/somethingelse/', $results['name2'], $junk_array);
    //store ALL information
    $my_results[$c][] = $results;
  }
  ksort($my_results); //sort by key
  print_r($my_results);
?>

you can try your hand at sorting that out, though! so, if you can set up some test information (like replacing field names and the "somethingelse" with an actual value that shows up in your database a lot in multiple rows) and run that script, you'll see a nice array printed out. you can then run it all through a foreach() loop and dump all the information out in a nice manner that will appeal to your users!

second, you took what I said about the highlight_search() in the FOR loop way too literally. I meant to do it like this, while you were displaying the actual information:

while($results = mysql_fetch_assoc($query)){
  $display = array();
  $display['part'] = $results['part'];
  $display['name'] = $results['name'];
  $display['name2'] = $results['name2'];
  //etc!
  //or use a foreach() to define all these, depends on if you will use all the keys returned
  //or just a few

  //loop it and highlight it all
  for($i = 0; $i < count($words); $i++){
    foreach($display as $key => $value){
      $display[$key] = highlight_search($words[$i], $value);
    }
  }
  print_r($display);
} 

and if you want something that will get rid of everything except numbers, use a regular expression and preg_replace():

$quantity = 'adasjlj124321q123-0samdlasmd2+`!)@!)!#_~!@+#231-101=-23';
$quantity = preg_replace("/[^0-9]/i", '', $quantity); 

echoing $quantity should print out something like "1243211230223110123". using javascript, and especially relying on it for form validation, is BAD.