unable to highlight descriptions php mysql

Question

I'm working on mini project where the words from the description are highlighted on the webpage. The keywords are inputted by the user dynamically in to the database.

While displaying data into webpage the description and keywords are selected from data base and the words in description which match the keywords are highlighted.

Database image:

Table name information

table name keywords

Here is the code

    <?php
    $link = new mysqli('localhost','root','','example');
    if($link->connect_error){
        die("Connection Failed".$link->connect_error);
    }

?>

<!DOCTYPE html>
<html>
<head>
<style>
.highlight{
    color:green;
    background:yellow;
}
</style>
</head>
<body>
<?php
        //select keywords
        $sql = "SELECT keywords FROM keywords";
        if($res = $link->query($sql)){
            while($row = $res->fetch_assoc()){
                $keyword[] =array(
                    'name'=>$row['keywords'],
                ); 
            }
        }
        else
        {
            echo "Error".$link->error;
        }

        //get the description
        $Query = "SELECT * FROM information";
        if($result = $link->query($Query)){
            while($rows = $result->fetch_assoc()){
                echo $rows['description']."<br><br><br>";
            }
        }
        else
        {
            echo "error".$link->error;
        }

?>

</body>
</html>

here is the my output look like

Now here is how it show be looking

Note i know my code is vulnerable to sql injection but this is experimental not for college or online

Thanks is advance

Try and stay focused on the problem here. That's a ton of screenshots that are confusing the situation. — tadman, Jul 10 '17 at 17:26
I don't see anywhere in your code where you're actually trying to do any kind of formatting. Here's what I see: You get the keywords from the database, but then you don't use them anywhere else. Then you get the description from the database, and simply dump it on the page. You even defined a CSS class for the highlight style, but you didn't use that either. Maybe you should try using something like `preg_replace()` on the description to wrap all the keywords in a ``. Is that what your real question is? How to do the actual formatting? — Ian Drake, Jul 10 '17 at 17:39

Ben · Answer 1 · 2017-07-10T20:57:09.457

0

To get an array of the keywords, you will need to create an empty array, and then pushing the array to input the values, instead of creating an empty array every time you loop, which will overwrite the previous array.

 //select keywords
    $keywords=[];

    $sql = "SELECT keywords FROM keywords";
    if($res = $link->query($sql)){
        while($row = $res->fetch_assoc()){
            keywords[]=$row['keywords'];
        }
    }
    else
    {
        echo "Error".$link->error;
    }

To format it, now that you have an array of the keywords, is rather difficult, as str_replace is not friendly with arrays, but I think this should work.

 //get the description
    $Query = "SELECT * FROM information";
    if($result = $link->query($Query)){
        while($rows = $result->fetch_assoc()){
            foreach($keywords as $i){
                str_ireplace($i, "<span class='highlight'>$i</span>",  $rows['description']);
            }
        }
    }
    else
    {
        echo "error".$link->error;
    }

Now, I didn't test this code, so don't kill me if it doesn't work as intended :P

EDITED: Keyword searching is now case insensitive, and I made it so that it would search the text for the keyword and not an array of the entire row.

EDITED 2: Please note that with this code, the case of the highlighted words will always be lowercase, no matter what. You may change your highlighting style to fix this if you wish. i.e.

.highlight{
    background-color: yellow;
    text-transform: uppercase;
}

EDITED 3: Performance enhancement, by replacing array_push(); with an assignment.

edited Jul 10 '17 at 20:57

answered Jul 10 '17 at 18:14

Ben

2,200
20
30

1

`array_push($keywords, $res);` should be `$keywords[] = $row['keywords'];` because you are working with `$row`, not whole result. Also note to use `array[] =` instead of `array_push`, since it is faster. Then, you can use `str_replace($keywords, "$i", $rows['description']);` - you can put whole array instead of using `foreach` which can be a pain for your code if you have many keywords. – Patrik Krehák Jul 10 '17 at 18:23
In addition to what @debute said, there's a downside to `str_replace()` because it's inherently case sensitive. Something like `preg_replace()` would let you match even if a keyword appeared at the beginning of a sentence and had its first letter uppercased, for example. – Ian Drake Jul 10 '17 at 18:27
@IanDrake I edited it to make it case insensitive with str_ireplace – Ben Jul 10 '17 at 18:32
@debute I have to use `array_push();` because `aray[]=` would have overwritten the array everytime it looped. Also, I cannot use `str_replace($keywords, "$i", $rows['description']);` because that would mean that all found keywords would be replaced with the last keyword (highlighted) unless I removed the foreach loop, in which case I would have no way of specifying which keyword to replace it with. And thank you for pointing out that I needed to use `$keywords[] = $row['keywords'];` – Ben Jul 10 '17 at 18:36
@Ben `str_ireplace()` works fine, but it it won't maintain the original case. It will always lowercase it, which won't look right at the beginning of the sentence. Just something to be aware of. – Ian Drake Jul 10 '17 at 18:40
@IanDrake Thank you, that's a great point, luckily, all of the keywords that he showed in his images are all lowercase, and I'd be surprised if someone starts a sentence with the word 'liver' – Ben Jul 10 '17 at 18:43
@Ben this `array =` will overwrite, but this `array[] =` will append. And yes, you are right about `str_replace`, forgot about that :) – Patrik Krehák Jul 10 '17 at 18:44
@debute Yep, you're right, I forgot that adding the [] makes it assign to a new index, not to the array. I have a bad habit of always using array_push(); – Ben Jul 10 '17 at 18:54
I think @debute has a good point about performance, but I think there's an error in the comment which might be causing some confusion (because `$i` is still in the replacement value). If you have a `$keywords` array, you can build a replacement array ahead of time like `$replacements = []; foreach ($keywords as $keyword) { $replacements[] = '' . $keyword . ''; }`. Then it just becomes: `echo str_ireplace($keywords, $replacements, $rows['description']);` Then you avoid the foreach loop inside each description. I think that's what @debute was trying to say. – Ian Drake Jul 10 '17 at 18:56
@IanDrake Doesn't that have the same performance impact, as you are still looping through the array and then using `str_ireplace()`, the only difference being that you are doing those two things separately instead of at the same time? – Ben Jul 10 '17 at 19:02
@Ben Honestly, we're probably talking about micro-optimizations at this point -- I just wanted to fix the error I saw in the comment. In theory, my way is faster because it involves one loop ahead of time instead of a loop happening over and over again inside another loop. In fact, you don't even need a separate loop to build the `$replacements` array. There's already a loop after the first query to build the `$keywords` array, so you can build it at the same time. After `$keywords[]=$row['keywords'];` just do `$replacements[] = '' . $row['keywords'] . '';` – Ian Drake Jul 10 '17 at 19:11
Then, inside the loop for each description, just do `echo str_ireplace($keywords, $replacements, $rows['description']);`. Will a make a big difference? Probably not. Just seems like an easy way to take advantage of the fact that `str_ireplace()` accepts an array for the first 2 arguments. – Ian Drake Jul 10 '17 at 19:13

score 0 · Answer 2 · answered Jul 10 '17 at 19:19

0

I was googling and found a question similar to yours - highlight multiple keywords in search. I edited accepted answer to fit your code.

function highlight($text, $keywords) {
    $re = '~\\b(' . implode('|', $keywords) . ')\\b~';
    return preg_replace($re, "<span class='highlight'>$0</span>", $text);
}

$Query = "SELECT * FROM information";
if($result = $link->query($Query)){
    while($rows = $result->fetch_assoc()){
        $highlighted = highlight($rows['description'], $keywords);
        echo $highlighted . "<br><br><br>";
    }
}

answered Jul 10 '17 at 19:19

Patrik Krehák

2,595
8
32
62

what is meant by `$0` – Jul 11 '17 at 02:44
`$0` represents the entire part of the string that matches the pattern – Patrik Krehák Jul 12 '17 at 22:05

unable to highlight descriptions php mysql

Note i know my code is vulnerable to sql injection but this is experimental not for college or online

Thanks is advance

2 Answers2