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.