Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm doing a practice project setting up a basic social network. I have a table with user id, name, last name, and a friends column which contains the id numbers of all other 'users' that they are friends with. E.g. 3 "Donald" "Duck" "4, 5, 23, 42".
The following works if I know the values: 

if(mysqli_connect_errno($con))  {
    echo "failed to connect" . mysql_connect_error();
} else {
    $resulting = mysqli_query($con, "SELECT * FROM $tbl_name WHERE LastName LIKE '%$secondquery%'
        OR LastName LIKE '%$firstquery%'
        OR LastName LIKE '%$thirdquery%'
        OR LastName LIKE '%$fourthquery%'
        OR FirstName LIKE '%$secondquery%'
        OR FirstName LIKE '%$firstquery%'
        OR FirstName LIKE '%$thirdquery%'
        OR FirstName LIKE '%$fourthquery%'
    ");
    $counting = mysqli_num_rows($resulting);

    if($counting != 0){
        while($row = mysqli_fetch_array($resulting))
        {
            if($row['Profile'] != null) {
                $defaultprofilepic = $row['Profile'];
            }else {
                $defaultprofilepic = "defaultprofile.JPG";
            }
            $PublicProfile = $row['ProfilePage'];
            $Friends = array("4", "5", "23", "42") 
            if(in_array(42, $Friends)){
                echo $row['FirstName'] . " is your friend!";
            } else {
                echo "Connect with " . $row['FirstName'];
            }
        }
    }
}

However, I can't get the following working properly, regardless of how the data appears in the database (e.g. "1", "2", "42" or "1, 2, 3, 4" or 1 2 3 4 . If the 42 is the first in the list then it often does work, but that's not very helpful!

$Friends = array($row['Friends'];
if(in_array(42, $Friends)) {
    echo $row['FirstName'] . " is your friend!";
} else {
    echo "Connect with " . $row['FirstName'];
}

I'm aware there's probably a much better way of doing it, or even of storing the relevant data, so any suggestions are welcome! I also know that the following may be vulnerable to sql injection (as the "querys" are from a user search), but don't properly understand that yet so help on that would be great as well. Thanks!

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

You should create new table eg 'friends', and contain in it, with structure like this: user_id | friend_id. It should look like this:

user_id | friend_id
    1   |     1
    1   |     2
    1   |     3
    2   |     4

And 

SELECT * FROM friends WHERE user_id = $our_user_id

And use : JOIN syntax.

(Btw. better learn PDO)

share|improve this answer
1  
This is the way to do it (and as someone who is just learning SQL myself, I also find it super-frustrating that SQL doesn't support lists). And I second learning PDO as well. You can read about it here - net.tutsplus.com/tutorials/php/… –  Dan Goodspeed Oct 15 at 8:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.