remove duplicate SQL results inside foreach while loop

Question

I have a foreach while loop to search every sent chat message, and it will display if it gets a match.

My issue is that it displays multiple of the same result.

Result:

<?php
    $bad_word = array("badword","bad_word","bad","frick","heck");
    foreach($bad_word as $word){
        $get_flags = $con->query("SELECT DISTINCT username,msg,date_log,time_log FROM chat_logs WHERE msg LIKE '%$word%'");
        while($bad_msg = mysqli_fetch_array($get_flags)){
            echo '
            <div class="msg">
            <div class="msg-top">
            <p><b>'.$bad_msg['username'].'</b> '.$bad_msg['date_log'].' - '.$bad_msg['time_log'].'</p></div>';
            if(!empty($bad_msg['msg'])){
                echo '<span>'.$bad_msg['msg'].'</span>';
            }
            echo '</div>';
        }
    }
?>

I am expecting unique results from my query, but im still getting duplicate results.

There's probably differences in whitespace. Try using `TRIM(msg) AS msg` in the SELECT list. — Barmar, Mar 31 '23 at 19:59
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Mar 31 '23 at 22:09

score -2 · Answer 1 · answered Mar 31 '23 at 20:03

Try this:

<?php
    $bad_word = array("badword","bad_word","bad","frick","heck");
    $bad_word = array_map(fn($word) => "msg LIKE '%{$word}%'", $bad_word);
    $bad_words_where_clausule = implode(' OR ', $bad_word);
    $get_flags = $con->query("SELECT DISTINCT username,msg,date_log,time_log FROM chat_logs WHERE {$bad_words_where_clausule}");
    while($bad_msg = mysqli_fetch_array($get_flags)){
        echo '
        <div class="msg">
        <div class="msg-top">
        <p><b>'.$bad_msg['username'].'</b> '.$bad_msg['date_log'].' - '.$bad_msg['time_log'].'</p></div>';
        if(!empty($bad_msg['msg'])){
            echo '<span>'.$bad_msg['msg'].'</span>';
        }
        echo '</div>';
    }
?>

remove duplicate SQL results inside foreach while loop

1 Answers1