How To SELECT DISTINCT From Coma Seperated mysqli Query

Question

Hello Guys I have coma separated entries in my table. I want use DISTINCT function to get unique result. I have entries like

id | name
1  | sachin tendulkar, virat kohli, ms dhoni
2  | ms dhoni, vvs laxman, ajinkya rahane
3  | rohit sharma, virat kohli, ms dhoni

I want to use mysqli to show results like

name : sachin tendulkar, virat kohli, ms dhoni, 
       vvs laxman, ajinkya rahane, rohit sharma

I don't want to repeat these names if they are same.

I tried like :

$catquery = $db->query("SELECT DISTINCT name FROM players WHERE id='$id';");
while($row2 = $catquery->fetch_object()):
echo "$row2->name";
endwhile;

But got result like :

sachin tendulkar, virat kohli, ms dhoni 
ms dhoni, vvs laxman, ajinkya rahane 
rohit sharma, virat kohli, ms dhoni

It's not finding unique names, please help me to get it solved !!!

If you are using PHP version less than 5.5, let me know to make the answer compatible with your version — Mojtaba, Jun 02 '16 at 15:01
@Mojtaba Getting error like `Fatal error: Cannot use object of type stdClass as array` when I add `$names = [];` line. — Salim, Jun 02 '16 at 15:09
Are you using $names previously? Change my $names variable to something else like $unique_names. I updated the answer — Mojtaba, Jun 02 '16 at 15:11
Related: [PHP array - explode some comma separated elements into new items](https://stackoverflow.com/q/16476140/2943403) — mickmackusa, Sep 19 '22 at 04:10

vaso123 · Answer 1 · 2016-06-02T15:09:18.290

0

You need to process it on PHP side.

$catquery = $db->query("SELECT name FROM players WHERE id='$id';");

$names = [];    //Collect name to this
while($row2 = $catquery->fetch_object()) {
    $pieces = explode(',',$row2->name); //explode the string by ',' character
    foreach ($pieces as $piece) {
        $names [] = trim($piece);
    }

}
var_dump(array_unique($names)); //here is the uniqe array

You can explode it by , (comma and space) if you are sure, there is always a space after comma.

If you just get back 1 row, because id is primary key, then no need the while.

edited Jun 02 '16 at 15:09

answered Jun 02 '16 at 14:52

vaso123

12,347
4
34
64

Getting error like `Fatal error: Cannot use object of type stdClass as array in ` on `$names = [];` line – Salim Jun 02 '16 at 15:01
Fixed this `$row2->name` – vaso123 Jun 02 '16 at 15:09
It worked but getting one error `Notice: Undefined variable: row2 xyz.php on line 74 Notice: Trying to get property of non-object in xyz.php on line 74 array(4) { [0]=> string(12) "Navjot Singh" [1]=> string(11) "Ashiwin Kumar" [2]=> string(12) "Amaal Mallik" [3]=> string(12) "Ramesh Tiwari" } ` I added `$names = $row2->name;` as you said – Salim Jun 02 '16 at 15:17

Mojtaba · Accepted Answer · 2016-06-02T15:42:11.423

0

It's better to make one array and echo it one time

$unique_names = [];
$catquery = $db->query("SELECT DISTINCT name FROM players WHERE id='$id';");
while($row2 = $catquery->fetch_object()):
  $unique_names = array_merge($unique_names, array_map('trim',explode(',',$row2->name)));
endwhile;

$unique_names = array_unique($unique_names);
echo implode(',',$unique_names);

At the end, you have all unique names in $unique_names array. For instance, I imploded it and echoed. You can use it in your own way

And this is added for your request in comment:

foreach($unique_names as $unique_name){
  echo '<a href="/player.php?player='. str_replace(' ','-',$unique_name) .'">'.$unique_name.'</a><br>';
}

edited Jun 02 '16 at 15:42

answered Jun 02 '16 at 14:53

Mojtaba

4,852
5
21
38

I am not using $names previously – Salim Jun 02 '16 at 15:20
@Salim, So, you might put both answers together. Please use my answer independently. $names = []; just initials an empty array. Should not have any problem with stdClass – Mojtaba Jun 02 '16 at 15:22
I tried your code independently, didn't got any error but got result like `Arijit Singh, Tulsi Kumar, Amaal Mallik,Ankit Tiwari, Arijit Singh ` There is two times `Arijit Singh` Not got filterd – Salim Jun 02 '16 at 15:27
@Salim, it could be because of spaces. I trimmed the arrays and uniqued them. Use the updated one – Mojtaba Jun 02 '16 at 15:33
Thank You so much it worked <3 !!! Now please tell me one last thing how to link them separately like : `Arijit Singh, Tulsi Kumar, Amaal Mallik, Ankit Tiwari` – Salim Jun 02 '16 at 15:36
@Salim, like what? – Mojtaba Jun 02 '16 at 15:37
is it really possible ?? – Salim Jun 02 '16 at 15:42
@Salim. Updated the answer – Mojtaba Jun 02 '16 at 15:42
! It's Worked Like a Champ !! Thank you so much for your help :) – Salim Jun 02 '16 at 15:46
"Thanks for the feedback! Once you earn a total of 15 reputation, your votes will change the publicly displayed post score" Sorry Buddy :/ – Salim Jun 02 '16 at 15:54
@Salim. No problem. Good luck – Mojtaba Jun 02 '16 at 15:55

How To SELECT DISTINCT From Coma Seperated mysqli Query

2 Answers2