I am focusing on creating a "friend" system for my website. I've got a Customers table during my database and 2 fields for the reason that table known as "friendrequestssent" and "friendrequestsreceived".

Whenever a user transmits a buddy request to a different user, it stores his USERID in to the other person's "friendrequestsreceived" area and also the other person's USERID into his "friendrequestssent" area, both having a trailing comma (ie: 12345,).

I have to determine if your request already is available to be able to prevent duplicate demands, and to date I have attempted this:

$sql = "SELECT * FROM users WHERE userid = $userid AND $profileid IN (friendrequestssent);";
    $sent = mysql_num_rows(mysql_query($sql));
    $sql = "SELECT * FROM users WHERE userid = $profileid AND $userid IN (friendrequestsreceived);";
    $received = mysql_num_rows(mysql_query($sql));
    if(($sent > 0) && ($received > 0)) {
//do stuff here
}

This labored great in the beginning, however when multiple comma-separated values appear in the area (ie: 12345,12346,) , the IN statement no more finds the worthiness and the amount of rows remains zero.

So far as I will tell, I am unable to understand why the IN statement within the MySQL query will not begin to see the value.

However, I am sure there's a much better method of doing this anyway. I am simply not sure how yet. Please advise.

The IN clause examines individual people of the comma-separated list, however when you store that comma-separated list in one area, MySQL goodies it as being just one-string value, so you are doing

... WHERE x IN ('1,2,3')

which means

... WHERE x = '1,2,3'

To pressure MySQL to deal with that CSV list like a CSV and never a monolithic string, you will need the [cde] function:

FIND_IN_SET()

Don't treat just one area like it's multiple rows. Make use of a table with this. Something similar to:

... WHERE FIND_IN_SET($profileid, friendrequestssent);

friend_requests =============== friend_id requested_friend_id and friend_id will comprise an amalgamated key.

Your design is alright, but it will likely be better should you allow me to redesign this for you personally:

First leave your table customers because it is, then perform a new table with 3 fields:

  • An autoincrement area as the index (if you would like it, or simply skip this column).
  • A area in which you store a buddy that's asking for a friendship and allows call: friend_request.
  • A area in which you keep friend that's finding the friendship request, and allows call ir: friend_receive_request.

You need to add the propper foreign key indexes and remember that you might have duplicate demands by storing exactly the same friendship in various posts. I possibly could obvious this more for those who have questions.

one factor you should do is fetch the whole entry that contains all of the id's as an assortment or string and living room properly perform a string search or perhaps an array search to check on for duplicacy.