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)?

Obviously you don't want to hardcode the user names in the PHP code. It's an issue that's best solved with a nicer SQL query. So show that part too. — mario
– mario, Commented Dec 31, 2011 at 7:21
@mario My query is: "SELECT * FROM friendship_table WHERE member1='1'" — randomphp
– randomphp, Commented Dec 31, 2011 at 7:26
Please update the question to add additional information. In general, respond to requests for clarifications by updating your post, rather than replying with a comment. For one thing, a question should be understandable without reading comments. For another, SO is a QA & site, not a forum, and comments aren't intended (nor are they well suited) for discussions. — outis
– outis, Commented Dec 31, 2011 at 7:30
Consider picking a meaningful username. One advantage to this is others can use at-replies and you'll get a notification that someone has addressed you in a comment. — outis
– outis, Commented Dec 31, 2011 at 7:31
@outis Great, thanks for the advice. I updated the question and added a username to my profile. — randomphp
– randomphp, Commented Dec 31, 2011 at 7:35

outis · Accepted Answer · 2012-03-26 01:26:36Z

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).

anubhava · Accepted Answer · 2011-12-31 07:21:31Z

0

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;
}

answered Dec 31, 2011 at 7:21

anubhava

790k67 gold badges603 silver badges671 bronze badges

2 Comments

randomphp Over a year ago

Thanks for the reply but I tried that and that didn't work either. And how would I use the switch and have the case for 2 numbers? Like: case: "2" || "3", <- Is that possible?

anubhava Over a year ago

That is indeed possible, check my code for case "1": case "4": case "7": which essentially means "1" || "4" || "7"

user319198 · Accepted Answer · 2011-12-31 07:32:12Z

0

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

answered Dec 31, 2011 at 7:32

user319198

Collectives™ on Stack Overflow

How to get multiple values from a single variable?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related