Searching a mysql database for one result, help

[Coly010]

Ok, so I have a table in my database called friends. It has three columns. f_id, u1_id, u2_id.

f_id is simply the primary key, etc.

u1_id is the userid of one of the users

u2_id is the userid of the other user.

This entry links these two users together as friends. simple as.

now say someone has fifty friends. that means there will be 50 entries, with his/her userid as the u1_id.

Now my problem.

I have a php file with a GET function that gets the id of the friend that the user has clicked on.

essentially the user will click on the link that appears from this:

$q = mysqli_query($c, "SELECT u2_id FROM friends WHERE u1_id = '$userid'");
$q2 = mysqli_num_rows($q);
while($q3 = mysqli_fetch_array($q))
{
$f_id = $q3['u2_id'];
$fs = mysqli_query($c, "SELECT * FROM users WHERE userid = $f_id");
$fr = mysqli_fetch_array($fs);
if( $fr['online'] == 1 )
{
	echo "<img src='img/online.png' width='16' height='16' /> <a href=\"chat.php?fid={$fr['userid']}\">" . $fr['username'] . "</a><br />";
}
}

 

now we all know that the GET can be faked, anyone can enter any value they like into the url and have it processed.

So what i want to be able to do in the chat.php file is have a way to check that the two people really are friends.

How would i go about doing it.

For example i cannot have

$f_id = abs((int)$_GET[fid']);
$q = mysqli_query($c, "SELECT * FROM friends WHERE u1_id = '$userid'");
$q2 = mysqli_num_rows($q);
while($q3 = mysqli_fetch_array($q))
{
  if( $fid != $q3['u2_id'])
  {
     echo "you cannot talk to someone who does not know you";
  }
}

 

in the hope that it finds one person out of fifty, because it could end up echoing 49 "you cannot talk to someone who does not know you" before finding that one person.

Any help would be extremely appreciated.

[Guest]

$query = mysqli_query("SELECT * FROM friends WHERE u1_id = '$userid' AND u2_id = '$f_id' LIMIT 1");

 

Then check if num_rows is greater than 0.

Something along those lines. :)

[sniko]

$query = mysqli_query("SELECT * FROM friends WHERE u1_id = '$userid' AND u2_id = '$f_id' LIMIT 1");

 

Then check if num_rows is greater than 0.

Something along those lines. :)

....You'd only have a % succession rate, as you're not checking it the other way around

$u1 = 1;
$u2 = 2;
$sql = "SELECT * FROM `friends`
         WHERE (`u1_id` = ". $u1 ." AND `u2_id` = ". $u2 ." ) OR (`u1_id` = ". $u2 ." AND `u2_id` = ". $u1 ." ) 
         LIMIT 1";
$query = mysqli_query($sql);

(Change the values of the variables.)

[Coly010]

Sniko, I thought i might have had that problem, so I set it up so that for everytime someone adds a friend, it puts two entries into the table. I know i do not have to do this, but at the time when i created it i did not know how to check both ways, now however i might go back and change it all. Thanks to both of you for the answers :) it helps a lot

[HauntedDawg]

SELECT COUNT(`f_id`) FROM `friends` WHERE IF(`u2_id` = '.$userid.', `u1_id`, `u2_id`) = '.$f_id.' LIMIT 1

[Coly010]

Thanks :), got it sorted now.

But now I have another problem, unrelated to the table in the original post

I want to pull the bottom most rows from the mysql database with a limit on it. now I know that I do this with ORDER BY DESC LIMIT 20

but after that if i was to echo them to the screen it would be in descending order from the database. How would i go about echoing them in reverse order from how they were taking from the database?

[HauntedDawg]

Thanks :), got it sorted now.

But now I have another problem, unrelated to the table in the original post

I want to pull the bottom most rows from the mysql database with a limit on it. now I know that I do this with ORDER BY DESC LIMIT 20

but after that if i was to echo them to the screen it would be in descending order from the database. How would i go about echoing them in reverse order from how they were taking from the database?

One way would be to do something like this:

 

$sql = mysqli_query('SELECT COUNT(`f_id`) FROM `friends` WHERE IF(`u2_id` = '.$userid.', `u1_id`, `u2_id`) = '.$f_id.' ORDER BY `f_id` DESC LIMIT 1');
$friends_arr = array();
while($friends = mysql_fetch_assoc($sql))	{

$friends_arr[] = $friends;

}
$friends = array_reverse($friends_arr);

foreach($friends as $row)	{

echo $row['friend_name'];

}

 

Alternatively (although not tested, should work the same concept):

 

<?php
$sql = mysqli_query('SELECT COUNT(`f_id`) FROM `friends` WHERE IF(`u2_id` = '.$userid.', `u1_id`, `u2_id`) = '.$f_id.' ORDER BY `f_id` DESC LIMIT 1');
while($friends = array_reverse(mysql_fetch_assoc($sql)))	{

echo $friends['friend_name'];

}
?>

[Coly010]

Thanks :) i'll try that now and get back to you on whether it works :)

[Coly010]

@HauntedDawg worked a charm, thanks :)

[Lucifer.iix]

....You'd only have a % succession rate, as you're not checking it the other way around

$u1 = 1;
$u2 = 2;
$sql = "SELECT * FROM `friends`
         WHERE (`u1_id` = ". $u1 ." AND `u2_id` = ". $u2 ." ) OR (`u1_id` = ". $u2 ." AND `u2_id` = ". $u1 ." ) 
         LIMIT 1";
$query = mysqli_query($sql);

(Change the values of the variables.)

 

Good catch....

Let's fill in the friends like this:

- Check if both users are not the same user.

- Than Insert: 1st is the person with the lowest primary key and the other has a bigger primary key.

Alter your DB so the User1 + User2 are a UNIQUE index. So you can only insert every friend-relation once.

 

If you make it like this, If A know B then B is also friend of A.

And you only can be friend once with every person.

Function isFriend($Username, $FriendName, $DBLink) {

..... return True/False;

}

Usage: if(isFriend("Me", "He", $DB)==true) { startChattingWith("He"); } else { throw new chatNotPosssibleException("Your not a friend of user `he`."); }

 

Happy Hacking: Roger

And let me know how it's going... and if it's working for you...