Mysql 'Like' statement returning the same rows twice

Question

I am attempting to display a distinct set of rows in PHP based on the results of a MySQL query, but I am seeing duplicate records output to the HTML.

My PHP and MySQL code looks like this:

<h2>Related products</h2>
<?php
    $title=str_replace(' ',',',$_GET['title']);
    $words=explode(',',$title);
    foreach ($words as $word){
    if (strlen($word) > 5){ 
    $res=mysqli_query($link, "SELECT title,id FROM posts WHERE title LIKE '% $word %' or title LIKE '%$word' or title LIKE '$word%' ORDER BY id DESC");
    while ($row=mysqli_fetch_array($res)){
        echo "<a href='/product/".$row['id']."/'><h3>".$row['title']."</h3></a>";
    }}}
?>

I searched for similar questions already asked, and based on their guidance I tried applying:

Select DISTINCT..

As well as:

while ($row=array_unique(mysqli_fetch_array($res)))

It is still showing duplicate results, however. (The same product shows up 3-5 times in the related products section.)

(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Aug 08 '21 at 18:59
[Edit] the question and provide a [example], i.e. the `CREATE` statements of the tables or other objects involved (paste the **text**, don't use images, don't link to external sites), `INSERT` statements for sample data (dito) and the desired result with that sample data in tabular text format. — sticky bit, Aug 08 '21 at 19:01
I will add a mysqli_real_eascape_string eventually to the $_GET['title'] to prevent sql injections. but i'm not sure what is it i need to clarify more in this post — malacai79, Aug 08 '21 at 19:09
No, forget `...real_eascape...` and use parameterized queries! — sticky bit, Aug 08 '21 at 19:11
Clarify as I told you. What don't you understand in particular about it? — sticky bit, Aug 08 '21 at 19:12
You’re running the query *n* times, once for each word. So if a title matches on more than one word, it will match on each iteration. Instead of iterating the query, you need to iterate to create a single where clause — Tim Morton, Aug 08 '21 at 19:16
…and the time to start using parameterized query is now, because you need to change the query anyway. Might as well do it right. — Tim Morton, Aug 08 '21 at 19:18

Tim Morton · Accepted Answer · 2021-08-10T15:09:58.600

The initial problem is that you are repeating the query, instead of building a single query.

This code illustrates building the query, while also using a parameterized query.

Please note I am unable to test this; some adjustments may be necessary.

<?php
// initialize $title to prevent uninitialized variable warning in while loop
$title = '';

if(isset($_GET['title']) {

    // make pdo connection here; assign to $pdo
    // left to reader to implement 

    $title=str_replace(' ',',',$_GET['title']);
    $words=explode(',',$title);

    $where = [];
    $values = [];
    foreach ($words as $word) {
        if (strlen($word) < 5) { 
            continue;
        }
        $where[] = 'TITLE LIKE ?';
        $values[] = '%' . $word . '%';
    }

    $where = join(' OR ', $where);

    $stmt = $pdo->prepare("SELECT TITLE, ID FROM POSTS WHERE $where");
    $stmt->execute($values);
}
?>

<h2>Related products</h2>
<?php while($title && ($row = $stmt->fetch(PDO::FETCH_ASSOC);)): ?>
    <a href="product<?= $row['id'] ?>">
        <h3><?= $row['title'] ?></h3>
    </a>
<?php endwhile; ?>

Mysql 'Like' statement returning the same rows twice

1 Answers1