mysql query and while loop creating a special string

Question

I am working on a project where I need to achieve a special task like this.

I have a sql query which fetches all the ids of users who commented on a post and then I use mysql_fetch_array and while loop to create a string of comma separated ids of those users like this.

If the ids of the users on any post are 1 and 4 and 5 and 18 and 88, the string I get is like 1,4,5,18,88.

But the problem is that if any person has made more than one comment then his/her id is added again I do not want this.

Like the users are:

1,4,5,4,1,15

It gets:

1,4,5,4,1,15

But I want:

1,4,5,15

I don't want duplicates.

What I tried:

select id from table where comment !=''
mysql_fetch_array()
while (){
   // now joining the ids of commenters here but not duplicate ids
}

Try `select DISTINCT id from table where comment !='' ORDER BY id` — M Khalid Junaid, Aug 12 '14 at 05:15

score 2 · Answer 1 · answered Aug 12 '14 at 05:22

2

Try this code

SELECT DISTINCT id FROM tablename WHERE  comment !='' ORDER BY id

It will give you the distinct id from your table

answered Aug 12 '14 at 05:22

Jha

23
10

This is one of the rare occasions where `DISTINCT` is actually the correct answer. Also, if the next question is "how do I make a comma-separated list": http://stackoverflow.com/questions/7188542/db2-comma-separated-output – Turophile Aug 12 '14 at 05:30

score 1 · Accepted Answer · answered Aug 12 '14 at 05:19

1

Use group_concat and you don't even need a loop. A single query will do it.

select group_concat(distinct id) from table where comment !=''

answered Aug 12 '14 at 05:19

Lorenz Meyer

19,166
22
75
121

and how to print them – abhi Aug 12 '14 at 05:22
this may solve my problem but i dont know how to print the the values – abhi Aug 12 '14 at 05:25
it says call to undefined function :( – abhi Aug 12 '14 at 05:34
echo mysql_result($result, 0); – Lorenz Meyer Aug 12 '14 at 05:41

mysql query and while loop creating a special string

2 Answers2