php mysql search script, how to make matched results bold

Question

I have a script below which reads on displayed information from my mysql database on my webpage, how can I make matched results bold please? for example, if I searched "john" how to make the displayed results "john bloggs". thanks

here is the script so far,

<?
mysql_connect ("localhost", "user","pass")  or die (mysql_error());
mysql_select_db ("databasename");

$term = $_POST['term'];

$sql = mysql_query("select * from tablename where category like '%$term%' or title like '%$term%' or postcode like '%$term%' or info like '%$term%' ");

while ($row = mysql_fetch_array($sql)){
    echo '<br/> Category: '.$row['category'];
    echo '<br/> Title: '.$row['title'];
    echo '<br/> Address: '.$row['add1'];
    echo '<br/> Street: '.$row['street'];
    echo '<br/> City: '.$row['city'];
    echo '<br/> Postcode: '.$row['postcode'];
    echo '<br/> Phone: '.$row['phone'];
    echo '<br/> E-Mail: '.$row['email'];
    echo '<br/> Website: '.$row['website'];
    echo '<br/><br/>';
    }
?>

Kokos · Accepted Answer · 2011-09-06T10:25:52.477

7

Let's say for instance you have an array $results which contains a few results from your MySQL query.

Let's say you were searching within the field name.

You could use a very simple str_replace to achieve this:

foreach($results as $result)
    echo str_replace($search,'<b>'.$search.'</b>',$result['name']);

This replaces all instances of $search (which should be your search string) with <b>$search</b> within $result['name'].

In your case:

while ($row = mysql_fetch_array($sql)){

    echo '<br/> Category: '.str_replace($term,'<b>'.$term.'</b>',$row['category']);
    echo '<br/> Title:    '.str_replace($term,'<b>'.$term.'</b>',$row['title']);
    echo '<br/> Address:  '.$row['add1'];
    echo '<br/> Street:   '.$row['street'];
    echo '<br/> City:     '.$row['city'];
    echo '<br/> Postcode: '.str_replace($term,'<b>'.$term.'</b>',$row['postcode']);
    echo '<br/> Phone:    '.$row['phone'];
    echo '<br/> E-Mail:   '.$row['email'];
    echo '<br/> Website:  '.$row['website'];
    echo '<br/><br/>';

}

BY THE WAY (IMPORTANT)

What you are doing here:

$term = $_POST['term'];

$sql = mysql_query("select * from tablename where category like '%$term%' or title like '%$term%' or postcode like '%$term%' or info like '%$term%' ");

Is extremely dangerous. $_POST['term'] comes from the user, what if this user fills in ';DROP TABLE tablename --? Your query will suddenly change to something that will drop your entire table and delete all your information.

You should always check your user input, here is a nice tutorial explaining some methods how:

http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

edited Sep 06 '11 at 10:25

answered Sep 05 '11 at 15:23

Kokos

9,051
5
27
44

Thanks for the promt reply but I'm struggling with implementing this. I am new to php and am not sure what my search string is. I am trying to make the users input matched results return bold. thanks in advance. – John Lord Sep 05 '11 at 15:52
In your example this would be `$_POST['term']` or `$term`. – Kokos Sep 06 '11 at 05:27
thanks again, one last thing, where abouts in my code would i insert this snippet of code? thanks foreach($results as $result) echo str_replace($term,''.$term.'',$result['title']); – John Lord Sep 06 '11 at 10:02
Well this is just an example, you should be able to adjust it to your own code but I will update my answer. – Kokos Sep 06 '11 at 10:11
brilliant! It now makes the results bold if they are an exact match. is there a way that I could edit the code in order to make matches that are not complete matches bold? for example. if someone types st johns ambulance. and the search was for johns. that it would get highlighted? thanks. – John Lord Sep 06 '11 at 10:25
Well, I'm not going to write the entire thing but that's definitely possible ;). If you `explode(" ",$term)` you will have an array that contains all the separate words. Then you will have to generate a query with that will search on all these words instead of directly from `$term`. Possibly with a function like `array_map()` or through a `foreach` loop. If you can't figure out how to do it, search the site and if it's not asked before make a new question :) – Kokos Sep 06 '11 at 10:28

Richard Dalton · Answer 2 · 2011-09-06T10:39:36.900

1

function highlight($term, $result) {
    return str_replace($term, '<strong>'.$term.'</strong>', $result);
}

Then just do this for each field you want to highlight.

while ($row = mysql_fetch_array($sql)){
echo '<br/> Category: '.highlight($term, $row['category']);
echo '<br/> Title: '.highlight($term, $row['title']);
echo '<br/> Address: '.$row['add1'];
echo '<br/> Street: '.$row['street'];
echo '<br/> City: '.$row['city'];
echo '<br/> Postcode: '.highlight($term, $row['postcode']);
echo '<br/> Phone: '.$row['phone'];
echo '<br/> E-Mail: '.$row['email'];
echo '<br/> Website: '.$row['website'];
echo '<br/><br/>';
}

edited Sep 06 '11 at 10:39

answered Sep 05 '11 at 15:29

Richard Dalton

35,513
6
73
91

thanks richard, where abouts in my code would i insert your code, thanks in advance – John Lord Sep 06 '11 at 10:07
@John I've updated my answer to show you how it would work. It's basically the same as Kokos answer but calling a function instead of repeating the code (generally a good idea). You should definitely take note of his warning about SQL injection. – Richard Dalton Sep 06 '11 at 10:42

score 0 · Answer 3 · answered Sep 05 '11 at 15:39

You are also able to highlight it in ALL fields one time. ex.

while ($row = array_map(function($res) use ($search){
        str_replace($search,'<b>'.$search.'</b>',$res);
    },mysql_fetch_array($sql))){
    //your print
}

It requires PHP 5.3+ but may be rewriten to 5.2-

php mysql search script, how to make matched results bold

3 Answers3