How do I check to see if value exists within comma-separated field?

I'm working on designing a "friend" system for my site. I have a USERS table in my database and two fields in that table called "friendrequestssent" and "friendrequestsreceived".

When a user sends a friend request to another user, it stores his USERID into the other person's "friendrequ开发者_Go百科estsreceived" field and the other person's USERID into his "friendrequestssent" field, both with a trailing comma (ie: 12345,).

I need to check to see if a request already exists in order to prevent duplicate requests, and so far I've tried 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 worked great at first, but when multiple comma-separated values exist in the field (ie: 12345,12346,) , the IN statement no longer finds the value and the number of rows remains zero.

As far as I can tell, I cannot see why the IN statement in the MySQL query won't see the value.

On the other hand, I'm sure there is a far better way to do this anyway. I'm just not sure how yet. Please advise.

---

The IN clause looks at individual members of a comma-separated list, but when you store that comma-separated list in a single field, MySQL treats it as a single-string value, so you're doing

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

which translates into

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

To force MySQL to treat that CSV list as a CSV and not a monolithic string, you'll need the FIND_IN_SET() function:

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

---

Don't treat a single field like it's multiple rows. Use a table for this. Something like:

friend_requests
===============
friend_id
requested_friend_id

friend_id and requested_friend_id will comprise a composite key.

---

Your design is OK, but it will be better if you let me redesign this for you:

First leave your table users as it is, then do a new table with 3 fields:

• An autoincrement field as your index (if you want it, or just skip this column).
• A field where you store a friend that is requesting a friendship and lets call: friend_request.
• A field where you store the friend that is receiving the friendship request, and lets call ir: friend_receive_request.

You should add the propper foreign key indexes and be aware that you could have duplicate requests by storing the same friendship in different columns. I could clear this more if you have questions.

---

one thing you can do is fetch the entire entry containing all the id's in the form of an array or string and den appropriately do a string search or an array search to check for duplicacy.