Highlight search term in mysql php search

Question

Could someone please help me to highlight the searchterm in my php search code? Below is the code that I am currently using, and it works fine. Would just like to add a highlight function but have no idea how to implement that on this code without redoing the whole thing.

I came across Highlight search text in mysql php search thispost which looks very nice. But I'm lost trying to implement this. Some time ago I had a <span> effect, but couldn't get it into the <table> to highlight only the searchterm and still loop through the table.

include("config/config.php");
$con = mysql_connect($host, $db_user, $db_pass);
if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }

mysql_select_db($db, $con);

$result = mysql_query("SELECT * FROM data WHERE `data_id` LIKE '%$_POST[searchterm]%'
OR `who` LIKE '%$_POST[searchterm]%'
OR `ref` LIKE '%$_POST[searchterm]%'
OR `asset` LIKE '%$_POST[searchterm]%'
OR `make_model` LIKE '%$_POST[searchterm]%'
OR `serial` LIKE '%$_POST[searchterm]%'
OR `os` LIKE '%$_POST[searchterm]%'
OR `swp` LIKE '%$_POST[searchterm]%'
OR `ea` LIKE '%$_POST[searchterm]%'
OR `dt_in` LIKE '%$_POST[searchterm]%'
OR `status` LIKE '%$_POST[searchterm]%'
OR `dt_out` LIKE '%$_POST[searchterm]%'
");
$num_rows = mysql_num_rows($result);

echo "<center>";
echo "<BR><BR>";
echo "<a href='index.php'><button id='sblogloginbtn' name='login' type='submit'><b>BACK</b></button></a>";
echo "<BR><BR>";
echo "<h1>Your search has found&nbsp;";
echo "<b><font size='15' color='blue'>$num_rows</font></b>";
echo "&nbsp;records.</font></h1>";
echo "<BR><BR>";

echo "<table border='frame'>
<tr style='color:#FF00FF'>
<th>Signed in By</th>
<th>Reference Number</th>
<th>Asset Number</th>
<th>Make Model</th>
<th>Serial Number</th>
<th>Operating System</th>
<th>Office</th>
<th>Profile</th>
<th>Extra Apps</th>
<th>Time IN</th>
<th>Status</th>
<th>Time OUT</th>
</tr>";

while($row = mysql_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>" . $row['who'] . "</td>";
    echo "<td>" . $row['ref'] . "</td>";
    echo "<td>" . $row['asset'] . "</td>";
    echo "<td>" . $row['make_model'] . "</td>";
    echo "<td>" . $row['serial'] . "</td>";
    echo "<td>" . $row['os'] . "</td>";
    echo "<td>" . $row['office'] . "</td>";
    echo "<td>" . $row['swp'] . "</td>";
    echo "<td>" . $row['ea'] . "</td>";
    echo "<td>" . $row['dt_in'] . "</td>";
    echo "<td>" . $row['status'] . "</td>";
    echo "<td>" . $row['dt_out'] . "</td>";
    }
echo "</table>";
echo "<br /><br />";
echo "</center>";

mysql_close($con);

Your code is vulnerable to SQL injections. You should read on [how to avoid them](http://stackoverflow.com/q/60174/53114). — Gumbo, Jan 22 '14 at 19:33
@Gumbo how can this code be vulnerable to sql injections if its only doing a query to select, no insert, no update nothing. no change on database side. I am asking because i have been trying to find the problem, and i only see the issue being when there is INSERT or UPDATE to database involved. — Fishy, Jan 23 '14 at 06:19
[SQL injections are not restricted to just INSERT and UPDATE statements but can happen in any statement.](http://stackoverflow.com/a/15732682/53114) In this case it’s `$_POST['searchterm']` that used in MySQL string literals without proper escaping. — Gumbo, Jan 23 '14 at 06:42
@Gumbo Would this then work? `$term = mysql_real_escape_string($_POST[searchterm]);` — Fishy, Jan 23 '14 at 07:03

score 7 · Accepted Answer · edited May 23 '17 at 11:44

The simplest solution is to use str_replace() to replace the search term with <span> tags wrapped around them, styled.

Warning: The way you have your script set up, you're vulnerable to injection attacks. This is just an example to show you how to pass in variables.

See: How can I prevent SQL injection in PHP?

<?php

include("config/config.php");
$con = mysql_connect($host, $db_user, $db_pass);
if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }

mysql_select_db($db, $con);

$term = $_POST[searchterm];

$result = mysql_query("SELECT * FROM data WHERE `data_id` LIKE '%$_POST[searchterm]%'
OR `who` LIKE '%$_POST[searchterm]%'
OR `ref` LIKE '%$_POST[searchterm]%'
OR `asset` LIKE '%$_POST[searchterm]%'
OR `make_model` LIKE '%$_POST[searchterm]%'
OR `serial` LIKE '%$_POST[searchterm]%'
OR `os` LIKE '%$_POST[searchterm]%'
OR `swp` LIKE '%$_POST[searchterm]%'
OR `ea` LIKE '%$_POST[searchterm]%'
OR `dt_in` LIKE '%$_POST[searchterm]%'
OR `status` LIKE '%$_POST[searchterm]%'
OR `dt_out` LIKE '%$_POST[searchterm]%'
");
$num_rows = mysql_num_rows($result);

echo "<center>";
echo "<BR><BR>";
echo "<a href='index.php'><button id='sblogloginbtn' name='login' type='submit'><b>BACK</b></button></a>";
echo "<BR><BR>";
echo "<h1>Your search has found&nbsp;";
echo "<b><font size='15' color='blue'>$num_rows</font></b>";
echo "&nbsp;records.</font></h1>";
echo "<BR><BR>";

echo "<table border='frame'>
<tr style='color:#FF00FF'>
<th>Signed in By</th>
<th>Reference Number</th>
<th>Asset Number</th>
<th>Make Model</th>
<th>Serial Number</th>
<th>Operating System</th>
<th>Office</th>
<th>Profile</th>
<th>Extra Apps</th>
<th>Time IN</th>
<th>Status</th>
<th>Time OUT</th>
</tr>";

while($row = mysql_fetch_array($result))
        {
    echo "<tr>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['who']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['ref']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['asset']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['make_model']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['serial']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['os']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['office']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['swp']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['ea']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['dt_in']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['status']) . "</td>";
    echo "<td>" . str_replace($term, "<span class=\"highlight\">$term</span>", $row['dt_out']) . "</td>";
    }
echo "</table>";
echo "<br /><br />";
echo "</center>";

mysql_close($con);

?>

And some sample styling:

<style type="text/css">
.highlight { background-color: yellow; }
</style>

You could make this simpler / more readable by storing everything that you echo in the while loop into a variable, then execute the replace on that variable, then echo the variable. — Fabien Warniez, Jan 22 '14 at 19:42
I edited your answer to fix a typo and to add a link to "How to prevent SQL injection", but you did another edit at the same time. I'm not going to re-edit. — Funk Forty Niner, Jan 22 '14 at 19:43
You're welcome, however the link is not showing up now, because your edit overwrote mine. If you want to add it again, see the edit history; it's in there. — Funk Forty Niner, Jan 22 '14 at 19:46
with this i get a parse syntax error (syntax error, unexpected ';' in /.../.../.../.../search.php on line 64) the while($row is line 61 — Fishy, Jan 22 '14 at 20:00

score 0 · Answer 2 · answered Jan 22 '14 at 19:37

0

i dont see where you are printing the searchterm on your page. also, I would use css style sheets, avoid font tags, for example

 <style>

 .searchTerm{
    background-color:red;
    } 
 </style>


 <table>
   <tr><th>You searched for<div class='searchTerm'><?php echo $_POST[searchterm];?></div></th></tr>
  //rest of page

answered Jan 22 '14 at 19:37

bart2puck

2,432
3
27
53

the printing of the search term happens in the table in that code. did not actually highlight it as i did not know now to. %$_POST[searchterm]%. my styling is allready done, in a css file, but did not know how to implement it. The font tag is only to change the color and size of the amount of records the search term found. – Fishy Jan 23 '14 at 06:26

score 0 · Answer 3 · answered Jan 22 '14 at 19:40

0

You will want to use a regex (preg_replace) to search for your term and replace it with said term surrounded with <span> </span>.

Look at the documentation of preg_replace for how to use it: http://us3.php.net/preg_replace

answered Jan 22 '14 at 19:40

Fabien Warniez

2,731
1
21
30

I don't think there's any need for `preg_replace()` since OP will always know what the search term is. `str_replace()` seems perfectly OK. – Jan 22 '14 at 19:42

Highlight search term in mysql php search

3 Answers3

Linked