How to highlight the search result from the mysql?

[yy886]

Hello, I have write a simple search page for my sit(testing!)
Now I am trying to highlight my search keyword from the search result.
Not going to use java so I am thinking to use something like preg_replace
But the problem is I dont where should I start with.
As you can see the code below(Ive took out some part of it), the output will be a table and displaying all the result which will match $term.

So now, I am trying to implement the preg_replace

$hterm = preg_replace($term, '<span class="highlight">$term<\/span>', ????);

what should I use to replace the ???? ?
Ive read the preg_replace from the PHP dot net but I still a bit confuse in my case...

Thank you for your help and sorry for my English.

<?php
      $term = $_POST['term'];

if(preg_match("/^[  a-zA-Z0-9]+/", $_POST['term'])){
    include("connect.php");
    mysql_select_db ("12345"); //the db is not real
    $sql = mysql_query("select * from 12345 where name like '%$term%' or info like '%$term%'  or id like '%$term%' ");

        if (mysql_num_rows($sql) > 0)
        {
            while ($row = mysql_fetch_array($sql))
            {
            echo 'Your search keyword(s): ' .$term;

            echo '<table border="1">';               

            echo '<tr BGCOLOR=#D8D8D8><td><b>ID:</b></td> <td>' .$row['id'].'</td></tr>';

            echo '<tr BGCOLOR=#D8D8D8><td><b>title:</b></td> <td>' .$row['title'].'<br></tr>';

            echo '<tr BGCOLOR=#D8D8D8><td><b>Category:</b></td> <td>' .$row['category'].'<br></tr>';

            echo '<tr BGCOLOR=#D8D8D8><td><b>Time:</b></td> <td>' .$row['time'].'<br></tr>';
            echo '<br><br>';
            echo '</table>';             


            }
        }else
            echo 'Your search keyword(s): ' .$term .'<p>Nothing! : </p>';
        }else 
        echo  '<p>Please enter a search query</p>'; 

?>

[veedeoo]

Hi,

Try this. First lets make a function to do the hightlight..

function highlightTerms($text_string, $terms){

    ## We can loop through the array of terms from string 
    foreach ($terms as $term)
    {
         ## use preg_quote 

            $term = preg_quote($term);

           ## Now we can  highlight the terms 

            $text_string = preg_replace("/\b($term)\b/i", '<span class="hilitestyle">\1</span>', $text_string);
    }
          ## lastly, return text string with highlighted term in it
          return  $text_string;
    }

How to use the function above? It could be something like this.. I am only be showing your while loop, and you still have to assemble everything..

    ## this is your while loop from above.
while ($row = mysql_fetch_array($sql))
{
     ## we need to pick up the search terms or terms and put them into an array

     ## in this example, I will be using the $row['title'] as the text string

 $text_string = $row['title'];

     ## the array of terms from the search keywords as $term 

 $terms = array($term, $term);

     ## return the string with hightlighted terms

 $highlite_string = highlightTerms($text_string, $terms);

     ## echo everything here.
     ## Use $highlite_string as subtitute to your actual title.


     ## end of whille loop

 }

On your css file add style to hilitestyle using the highlight css property..

[veedeoo]

here is the simple Demo of the above script . Although the string is not being pulled from the database, the term gets highlighted..

[yy886]

Thank you for the reply and sorry for the late reply as I was very busy in the past two days. Anyway, got some spear time to test this.

This is worling! So my further question is do I continue

like this:

        $text_string = $row['title'];
        $terms = array($term, $term);
        $highlite_string = highlightTerms($text_string, $terms);

        $text_string1 = $row['description'];
        $terms1 = array($term, $term);
        $highlite_string1 = highlightTerms($text_string1, $terms1);

        $text_string2 =....

I know they are and will be working but looks like it will be very long if I have 15 columns...Is there have anyway to make it shorter?

side questions, thinking to implement a user feedback on the recond...should i open a new thread or ask it here? Ive the idea and some researched but need some advice...

Thank you for your time.

*PHP is getting fun now...Just wrote a small function to generate thumbnails:P

[veedeoo]

Hi,

Yes, it will work..as long as your script is using this database query.

$sql = mysql_query("select * from 12345 where name like '%$term%' or info like '%$term%'  or id like '%$term%' ");

Your query above is just looking for the match of the term, and therefore it would only return an output with containing term. So if the term is found even in the middle of the description, the script will return the entire description, but the highlight will be apply on the term and not on the entire description.

Don't worry about the number of columns. I've used the above highlighter ( I wrote it as class and not function) in my previous project and it has more than 450000 database rows and about 35 columns not including the joins that needs to be validated.. plus the actual live spider result from across the web.

I wish I can provide the link here, but the project is not appropriate for general viewers... It is an adult search spider I wrote for someone 1 1/2 years ago.

For the user feedback implementation, you may want to start a new thread, because that is another topic...

Good Luck...

[yy886]

sorry, I am a new beginner so can you explain a bit how the class work? (Never mind if it is javascript...not going deep enough to understand complex javascript...)

As for the user feedback, I figured it out :P not that difficult as the only cheeky part is the Select * From 12345 WHERE id = $id...

anyway back to topic. is there anything that I can improve the above search function?

as for now, if i search 'test'
the reuslt will display: test 2, test 3, test 5...etc

if I search 'test 3'
the reuslt only will display: test 3

if I search 'test 3 4'
the reuslt will display nothing

I have a feeling that this search is only half way done...

thank you for your help again. :)

[h2k]

<h2>Search</h2> 
 <form name="search" method="post" action="<?=$PHP_SELF?>">
 Seach for: <input type="text" name="name" />

 <input type="hidden" name="searching" value="yes" />
 <input type="submit" name="search" value="Search" />
 </form>

<? 
 //This is only displayed if they have submitted the form 

$name = $_POST['name'];

$searching = $_POST['searching'] ;

 if ($searching =="yes") 
 { 
 echo "<h2>Results</h2><p>"; 

 //If they did not enter a search term we give them an error 
 if ($name == "") 
 { 
 echo "<p>You forgot to enter a search term"; 
 exit; 
 } 

 // Otherwise we connect to our Database 
 mysql_connect("localhost", "username", "password") or die(mysql_error()); 
 mysql_select_db("database") or die(mysql_error()); 

 // We preform a bit of filtering 
 $find = strtoupper($find); 
 $find = strip_tags($find); 
 $find = trim ($find); 

 //Now we search for our search term, in the field the user specified 
 $data = mysql_query("SELECT * FROM chardonnay WHERE MATCH (name) AGAINST('%".$name."%')

"); 
function highlightTerms($text_string, $names){
    ## We can loop through the array of terms from string 
    foreach ($names as $name)
    {
         ## use preg_quote 
            $name = preg_quote($name);
           ## Now we can  highlight the terms 
            $text_string = preg_replace("/\b($name)\b/i", '<span class="hilitestyle">\1</span>', $text_string);
    }
          ## lastly, return text string with highlighted term in it
          return  $text_string;
    }
 while($result = mysql_fetch_array( $data )) 
 { 
 //And we display the results 
$text_string = $result['name'];
$names = array($name, $name);
$highlite_string = highlightTerms($text_string, $names);

 echo "$highlite_string"; 
echo " "; 
echo " - ";
 echo $result['grape']; 
 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($data); 
 if ($anymatches == 0) 
 { 
 echo "Sorry, but we can not find an entry to match your query<br><br>"; 
 } 

 //And we remind them what they searched for 
 echo "<b>Searched For:</b> " .$name; 
 } 
 ?> 

I trying to incorporate this highlight code into my search code but it doesn't hightlight the keyword.
It only displays back the result. Can any one give me some pointer? I will fix the security on the input next.
Thank you

[h2k]

got it. I didn't define span class highlitestyle

[MikeJones_1]

You can use PHP Code Highlight Keywords function combined with css
http://www.kythuatmang.net/highlight-keywords-search-results-using-php.php