PHP MySQL search for data in array

Question

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!

Choinek · Accepted Answer · 2013-10-15 08:39:17Z

3

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)

edited Oct 15, 2013 at 8:39

answered Oct 15, 2013 at 7:56

Choinek

3132 silver badges13 bronze badges

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

1 Comment

Dan Goodspeed Over a year ago

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/…

Collectives™ on Stack Overflow

PHP MySQL search for data in array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related