CODEWITHSUNDEEP
phpmysql
randomphp
randomphp

Reputation: 233

MySQL - Get multiple values for the same column

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

Upvotes: 0

Views: 2100

Answers (3)

Zul
Zul

Reputation: 3608

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'];
}
?>

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65264

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

Upvotes: 0

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798506

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

Upvotes: 1

Related Questions