Remove Duplicates in while loop PHP

Question

I'm trying to remove duplicate results from my SQL query using PHP.

table categories:

id | name
1 | sony
2 | nintendo

table subcategories:

id | name | category_id
1 | playstation | 1
2 | playstation2 | 1
3 | wii | 2

table video_games

id | name | subcategories
1 | grand theft auto | 1,2
2 | super mario | 3

My PHP code:

$query = $database->query('SELECT id FROM subcategories WHERE category_id = "'.$_GET['id'].'"');
while($return = $query->fetch()) {
    $subcategories = $return['id'];
    $request = '%'.$subcategories.'%';
    $game_query = $database->prepare('SELECT * FROM video_games WHERE subcategories LIKE :request');
    $game_query->bindValue('request', $request);
    $game_query->execute();
    if($game_query->rowCount() > 0) {
        while($game_return = $game_query->fetch()) {
            echo $game_return['name'];
        }
    }
}

My code works but I have duplicate video games when there have multi subcategories.

I tried using SELECT DISTINCT * FROM video_games WHERE subcategories LIKE :request but same problem.

Any idea to remove duplicate results using SQL or PHP ?

Why do you not use `prepare()` for 1 query, but do for the other? You're 1st query is very vulnerable to injection. — GrumpyCrouton, Nov 22 '17 at 16:11
I would recommend normalizing your database. For example create a jointable between subcategories and video_games. this prevents comma separated values (like `1 | grand theft auto | 1,2`) and make further code writing way easier.) — kscherrer, Nov 22 '17 at 16:25
*"WHERE category_id = "'.$_GET['id'].'"'"* -- hold right there! This is [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) by design. Use [prepared statements](https://stackoverflow.com/q/60174/4265352) instead. — axiac, Nov 23 '17 at 14:50

Bojan Radaković · Answer 1 · 2017-11-24T15:22:54.430

You can save video game names in an array, and print it afterwards. For example:

$games = array();
$query = $database->query('SELECT id FROM subcategories WHERE category_id = "'.$_GET['id'].'"');
while($return = $query->fetch()) {
    $subcategories = $return['id'];
    $request = '%'.$subcategories.'%';
    $game_query = $database->prepare('SELECT * FROM video_games WHERE subcategories LIKE :request');
    $game_query->bindValue('request', $request);
    $game_query->execute();
    if($game_query->rowCount() > 0) {
        while($game_return = $game_query->fetch()) {
            if (!in_array($game_return['name'], $games)) {
                $games[] = $game_return['name'];
            }
        }
    }
}
//now print the games
foreach ($games as $game) {
    echo $game;
}

EDIT If you want more than just 'name', you can expand $games array with $key => $value combination. For example:

    . . .
    while($game_return = $game_query->fetch()) {
        foreach ($games as $game) {
            if ($game['name'] === $game_return['name']]) {
                continue 2;
            }
        }
        $games[] = array(
            'name' => $game_return['name'],
            'price' => $game_return['price'],
        )
    }

And afterwards print it like:

foreach ($games as $game) {
    echo $game['name'];
    echo $game['price'];
}

it works but how to display `name` and an other column, for example a `price` column ? — Rubyx, Nov 23 '17 at 14:28
thx but after test same problem, some video games are duplicate — Rubyx, Nov 23 '17 at 15:08

pedram shabani · Answer 2 · 2017-11-22T20:44:44.663

and also you can use array_unique at first save video game names in an array and next remove duplicate value.

.
.
.
your code
.
.
 $game_query->execute();
 if($game_query->rowCount() > 0) {
      $fetch =array_unique($game_query->fetch());
         foreach ($fetch as $key => $value){
             echo $value;
      }
 }

test

 <pre>
<?php
$arr=array("name"=>"1","name2"=>"2","name1"=>"2","name3"=>"3","name4"=>"1","name5"=>"2","name6"=>"3","name7"=>"22");
 print_r($arr);
 $fetch =array_unique($arr);
 print_r($fetch);
 foreach ($fetch as $key => $value)
            echo $key."=>".$value."<br>";

?>
 </pre>

it doesn't work, it doesn't display $value but the full row when I tried — Rubyx, Nov 22 '17 at 16:59

score 0 · Answer 3 · answered Nov 22 '17 at 17:08

0

MySql is able to solve this problem by its own means.

Use 'DISTINCT' and 'FIND_IN_SET'.

Like this:

"SELECT DISTINCT 
  vg.id,  
  vg.name, 
  vg.subcategories 
FROM 
  video_games vg
WHERE
  FIND_IN_SET( (SELECT id FROM subcategories WHERE category_id='".$_GET['id'])."' , vg.subcategories ) > 0 "

answered Nov 22 '17 at 17:08

mscdeveloper

2,749
1
10
17

Can you develop this solution? How display results? – Rubyx Nov 24 '17 at 11:28

Remove Duplicates in while loop PHP

3 Answers3