CODEWITHSUNDEEP
phpmysqlsqldatabase
user2793987
user2793987

Reputation: 199

SQL Friends List Creation

I'm unsure of the direction on how to actually insert a SQL command to create a "friendship" between two users. I have created the tables in PHPMyAdmin I'm at a loss for what to do in order to create a friends list similar to the links below. Let me know if you need more information.

  1. Buddy List: Relational Database Table Design
  2. Facebook database design?

PHP Query: 

if(!empty($_POST)) 
{
$searchParams=$_POST['search'];
$appUser = $_POST['username'];

//check if username and or email is already registered
$query = " SELECT 1 FROM Users WHERE username = :user OR email = :email";
//now lets update what :user should be
$query_params = array(
    ':user' => $searchParams,
    ':email'=> $searchParams,
);

try {
    // These two statements run the query against your database table. 
    $stmt   = $db->prepare($query);
    $result = $stmt->execute($query_params);
}
catch (PDOException $ex) 
{

}

//check results of above query and determine if username or email already exists, output results
$row = $stmt->fetch();
if ($row) {

    echo '{"success":2}';

$currentUser = mysqli_result(mysqli_query("SELECT UserID FROM Users WHERE username = $appUser"),0);
$addedUser = mysqli_result(mysqli_query("SELECT UserID FROM Users WHERE username = $searchParams"),0);


   $query2 = "Insert INTO Friends (IDUser,FriendID) VALUES (:username,:friend)"; 

   $query_params = array(
    ':username' => $currentUser,
    ':friend' => $addedUser
    );

 $stmt   = $db->prepare($query2);
 $result = $stmt->execute($query_params);

  echo '{"success":1}';  
}
}
 else
{

}

My tables are as follows:

User:

enter image description here

Friends:

enter image description here

Current User table data:

enter image description here

Upvotes: 1

Views: 5725

Answers (1)

Chris Farmer
Chris Farmer

Reputation: 25415

Assuming you have, say, data in your user table like this:

insert into users (iduser, username) values (1, 'amy');
insert into users (iduser, username) values (2, 'bill');
insert into users (iduser, username) values (3, 'chris');

And assuming that you are representing a "friendship" between users via a record in your friends table, you would create a friendship like so:

insert into friends (iduser, friendid) values (1, 2);

This would establish a relationship between user id 1 (amy) and user id 2 (bill).

Then, to subsequently find all the friends of a user, you'd do something like this:

select username from users u
                  inner join friends f on f.friendid = u.iduser
where f.iduser = 1

This would give you the names of all friends of amy.

In response to your comment about dynamically adding friends based on their names, you could do something like this. Here amy is logged into your site, so to simplify the query we will assume that you already have access to her user id of 1.

insert into friends (iduser, friendid)
select 1, iduser from users where username = :friendusername;

Upvotes: 1

Related Questions