column data search

[deepthi]

Hi All,

I ve a column name friends ids.in tat column datas are inserted one by one like 23,24,25..etc for particular row.i need to serach where the input data is in this range.ex if i try to insert 25 again to that row it should not allow duplicate values.hw can i achieve this

userid friends_ids
1 23,24,25

[tangor]

Can you provide a sample of your query code?

[deepthi]

Hi


########################Add Friends ids into database##########################

case 'FriendsList':

$sql2="SELECT * FROM manage_users WHERE user_id = ".$fr_id;//search whether the requeste fr_id is present in the database as a user.

$res2 = mysql_query($sql2);
if(mysql_num_rows($res2) != 0)//if requested friend is present in the DB
{
$sql="select friends_id from manage_users where user_id=".$userid;//select the friends_ids for userid.ex 2.
$res=mysql_query($sql);
while($row=mysql_fetch_array($res))
{
$ids[]=$row['friends_id'];
}
//$value = array($ids);
echo $ids[0];exit;//this will display 23,25,36.since these are the friends_ids for userid 2;
if (in_array("83", $value)) { //This is my code,where if i again attempt to add 23 as a friend_id for that userid.it should search in an array where 23 is present r not.since it is in the friends_id list it should display duplicate value and come out of the case.

echo "Duplicate value";exit;
}

//else,it should add to the list
if($ids!="")
{
$sql="update manage_users Set friends_id='".$ids.",".$fr_id."' where user_id=".$userid;//insert some more block users for that userid
$sqlRes= mysql_query($sql,$conn);
}
elseif($ids=="")
{
$sql1="UPDATE manage_users SET friends_id='".$fr_id."' WHERE user_id=".$userid;
$sqlRes1=mysql_query($sql1,$conn);
}



Can u tell me where i went wrong.may be in array near while looop?

Thanks

[rocknbil]

Well, as you can see, this is a very error prone way to go about it. It also makes for a great deal more programming than necessary, and slower searches on textual values, as opposed to numeric. But the answer is here:

//$value = array($ids);
$value = explode(',',$ids); // NOW it's an array
if (in_array("23", $value)) { // Sure you meant "23" here
echo "Duplicate value";exit;
}

What would be better: store your "friend ID's" in an integer field in a separate table, joined on that user's id

table friends
id|user_id|friends_id
1|123|23
2|123|25
3|123|36

then

$sql="select friends.friends_id from manage_users,friends where manage_users.user_id=friends.user_id and manage_users.user_id=$userid and friends.friends_id=$my_cleansed_friend_id";
$res=mysql_query($sql) or die("could not check for user already friended");
if ($row=mysql_fetch_array($res)) {
// No need to compare values - if the record exists, you're done.
echo "You already have that person as a friend.";
exit;
}

Less programming + integer value lookups = always faster. Note the bolded, this is presuming you've cleansed user input and stored it in $my_cleansed_friend_id. You'll notice I don't use quotes for numeric values - if you store a value in a numeric field and quote it, it may very well be empty

$sql="insert into friends (user_id,friends_id) values ('$userid','')";

and it will still run, it just won't do anything. If unquoted, it would error, which is what you want, to help you find the problem.

This makes your update easier too. Instead of re-compiling that comma separated string, you'd just add this:

$sql="select friends.friends_id from manage_users,friends where manage_users.user_id=friends.user_id and manage_users.user_id=$userid and friends.friends_id=$my_cleansed_friend_id";
$res=mysql_query($sql) or die("could not check for user already friended");
if ($row=mysql_fetch_array($res)) {
// No need to compare values - if the record exists, you're done.
echo "You already have that person as a friend.";
exit;
}
// No need for else if it doesn't exit
$sql="insert into friends (user_id,friends_id) values ($userid,$my_cleansed_friend_id)";
$sqlRes= mysql_query($sql,$conn) or die("could not insert record");

As for your first query,

//search whether the requeste fr_id is present in the database as a user.

You wouldn't even need to do this if you present only users that exist to this user. That is, I imagine there's some front end process before this where the user selects someone to be a "friend." Since that list should come from the database, the value already exists, no reason to check for it again. Right?