How to get multiple values from a single variable?

Question

I'm trying to build a friend system for my website. The table structure is set up something like this:

CREAT TABLE users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
) Engine=InnoDB;

CREATE TABLE friendship (
    member1 INT UNSIGNED NOT NULL, -- user's ID
    member2 INT UNSIGNED NOT NULL, -- friend's ID
    FOREIGN KEY (member1) REFERENCES users (id),
    FOREIGN KEY (member2) REFERENCES users (id),
    UNIQE (member1, member2)
) Engine=InnoDB;

Sample friendship data:

INSERT INTO friendship
  VALUES
(1, 2),
(1, 3),
(1, 5);

I am able to get all of a user's (id 1, in this example) friend's ids by using the following:

$query = mysql_query("SELECT * FROM friendship_table WHERE member1='1'");
while($row = mysql_fetch_assoc($query)) {
    $memberid = $row['member2'];
}

What I want to do now is a to get the friends' names. The memberid (member2 column in the table) variable has the values of 2,3 and 5 in the example. I want to do an if/elseif type of statement to see what the memberid is. Kind of like this:

if($memberid == "2") {
    echo "Bob is your friend";
} elseif($memberid == "3") {
    echo "Joe is your friend";
} elseif($memberid == "3" && $memberid == "2") {

//This line is the problem, the variable has multiple values in it (2,3,5) so I think it doesn't understand that and just uses one value

    echo "Bob and Joe are your friends!"; 
} else {
    //...
}

So the problem is that when I want to check if that variable has two specific values assigned to it, it doesn't check through all of its values. How can I make it so that it checks all of its values (2,3,5)?

Answer 1

Note that $memberid in the sample code doesn't hold multiple IDs. The value is overwritten with each loop, so that when the loop exits, only the last value is stored.

Get the friends name in the query by joining the friendship table to the user table on the friend's user ID:

SELECT name
  FROM friendship
    JOIN user ON friendship.friend=user.id
  WHERE friendship.user=?

Here, I've renamed the "member1" and "member2" columns as "user" and "friend", respectively, as it's more clear what the columns mean. If you want friendship to be a symmetric relationship, add a trigger to ensure this. If instead you want to keep the current column names and have it so the user and the friend can be in either column, it's messier but possible:

SELECT name
  FROM friendship
    JOIN user ON friendship.member1=user.id OR friendship.member2=user.id 
  WHERE (friendship.member1=:id OR friendship.member2=:id)
    AND NOT user.id=:id

Note that some DB extensions (e.g. PDO) don't support multiple named parameters with the same name, so in practice you'd need to use positional parameters with a repeated argument.

If there are just a few results, fetch the names as an array using (e.g.) PDOStatement::fetchAll:

try {
    $friendQuery = $db->prepare('...');
    $friendQuery->execute(array($user->id, ...));
    $friends = $friendQuery->fetchAll(PDO::FETCH_COLUMN, 0);
    switch (count($friends)) {
    case 0:
        // How sad.
        $message = "You have no friends.";
        break;
    case 1:
        $message = $friends[0] . ' is your friend.';
    default:
        $last = array_pop($friends);
        $message = implode(', ', $friends) . " and $last are your friends.";
        break;
    }
} catch (PDOException) {
    ...
}

Note that the example is only intended as an example, and not a complete solution. In production code, the part that deals with database access should be kept separate from the parts that deal with application logic and output (in the example, the switch statement).

Answer 2

This line:

$memberid == "3" && $memberid == "2"

should be:

$memberid == "3" || $memberid == "2" || $memberid == "5"

I would also suggest you to consider using switch / case instead of if / elseif /else etc like this:

switch ($memberid) {
    case "2": // do something
       break;
    case "3": // do something
       break;
    case "5": // do something
       break;
    case "1":
    case "4":
    case "7":  // do something
       break;
    default: // handle default case
       break;
}

Answer 3

Think wider use array

<?php
#you can have array of friends like this

$a1=array(2=>1,3=>1,5=>1,4=>1);

#perticular user's frieds array

$a2=array(2=>1,3=>1,5=>1);

$final_array=array_intersect($a1,$a2));
if(count($final_array) > 0)
{
  # each friend in $final_array are frieds Implode names here
}
else
{
  # Not Friend
}
?> 

Am not sure about code that is just i explaind way to do.You can do this job in better way using PHP Array functions