MySQL - Get multiple values for the same column

Question

I'm trying to build a friend system for my website and it is set up something like this:

User's Table:
id
name

Friendship Table:
member1
member2

So what I want is that when I have values like this (member1 is the user's id and member2 is the friend's id:

member1 - member2
1           2
1           3
1           5

I want to be able to get all of the user's (id of 1 in example) friend's ids. But when I use mysql_fetch_assoc in PHP I get only one of the member2 ids. So even though the member1 has the ids 2,3 and 5 in his friend list, I can only get one of his friend's ids. Is there any way to fix this so that I can get all of his friend's ids? Thanks

so, what output do you get? I'm not sure what you mean by 'But when I use mysql_fetch_assoc in PHP I get only one of the member2 ids'. Do you only get one at a time, or one in total? — Frank
– Frank, Commented Dec 18, 2011 at 20:42

Ignacio Vazquez-Abrams · Accepted Answer · 2011-12-18 19:53:04Z

1

As any decent PHP database tutorial would show, call mysql_fetch_assoc() in a loop until it returns false.

answered Dec 18, 2011 at 19:53

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

randomphp Over a year ago

I am using while($row = mysql_fetch_assoc($query)){...} to do it but I still only get one friend id. Any ideas?

Ignacio Vazquez-Abrams Over a year ago

You will only get one at a time. If you need to process more than one at a time then you will need to store them and then process after.

randomphp Over a year ago

I'm not sure I understand, I'm kind of a beginner. Can you please show me what you mean? Thanks

Zul · Accepted Answer · 2011-12-18 20:29:19Z

0

Let say you want get friendship table. The table has column: member1 and member2. To get member2 value based on member1,

<?php
$q1 = mysql_query("SELECT * FROM user_table");
while($r1 = mysql_fetch_assoc($q1))
{
    $q2 = mysql_query("SELECT * FROM friendship_table WHERE member1='".$r1['id']."'");
    while($r2 = mysql_fetch_assoc($q2))
    {
        echo $r2['member2'];
    }
}
?>

Or use sql join statement.

<?php
$q = mysql_query("SELECT  * FROM user_table JOIN friendship_table WHERE user_table.id=friendship_table.member1");
while($r = mysql_fetch_assoc($q))
{
    echo $r['member2'];
}
?>

edited Dec 18, 2011 at 20:29

answered Dec 18, 2011 at 20:00

Zul

3,6063 gold badges23 silver badges36 bronze badges

3 Comments

randomphp Over a year ago

I am using that but I still get only one friend id rather than all the friend ids.

Zul Over a year ago

what is your sql query. For that while loop, I will use: "SELECT member1,member2 FROM friendship_table WHERE member1=1", then use while loop for fetch value.

Zul Over a year ago

I update my answer, First looping to get member1 ID and second loop, to get member2. Other way, you can use SQL JOIN statement.

Eugen Rieck · Accepted Answer · 2011-12-18 19:58:07Z

0

If processing on the webserver is less of a bottleneck than bandwidth to the MySQL server, use

SELECT GROUP_CONCAT(member2) FROM Friendship where member1=...

and then

$ids=explode(',',$resultfield)

in PHP

answered Dec 18, 2011 at 19:58

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Collectives™ on Stack Overflow

MySQL - Get multiple values for the same column

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related