Member Avatar for R_4

Hi all,
I have assigned a column called "friendshipstatus" as a default value =0 in addfriends table that user can add other users to it to be friends as the code bellow which will give any users added as friend in addfriendss table the "friendshipstatus" value of 1 

<?php
include "Config.php";
//To check if user is existing or not in addfriends table by their id.
$id = $_GET['id'];
  $sql = "SELECT * FROM addfriends WHERE id = $id"; 
$result = $conn->query($sql);
if($result->num_rows >= 1) {
    echo '<script>alert("You Are Already Friends.")</script>';
    header("Refresh:0; url=index.php");
 }else{
//Then if user is not exist in the addfriends table add it by its id.
 $sql = "INSERT INTO addfriends (id,fname, lname, email,reg_date,friendshipStatus)
SELECT id, fname, lname, email, reg_date,1
FROM users WHERE id = $id"; 
      if ($conn->query($sql) === TRUE){ 
    echo '<script>alert("You Are Friends Now.")</script>';
    header("Refresh:0; url=index.php");       } else { 
           echo "Error: " . $sql . "<br>" . $conn->error; 
           } 
}
?>

Now I want to know how to detect when "friendshipstatus" =0 and when it =1
using mysql php
Thanks in advance

  • 2 Contributors
  • 11 Replies
  • 451 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by Dani

Dani AI

Generated

— quick diagnosis and a compact fix that ties into 's remark.

The current addfriends workflow copies user rows into a separate table without recording which account added the friend. That makes it impossible to know "who" the friendship belongs to. was right to say the lookup must consider the friendshipStatus field, but the real requirement is a mapping table that records owner -> friend (plus status).

A simple, safe schema for the relationship:

CREATE TABLE user_friends (
  owner_id INT NOT NULL,
  friend_id INT NOT NULL,
  friendshipStatus TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (owner_id, friend_id)
);

Fetch the three new users together with a flag that shows whether the logged-in account (example: $currentUserId) has them as friend — one query avoids N+1 lookups:

SELECT u.id, u.fname, u.lname,
       CASE WHEN uf.friendshipStatus = 1 THEN 1 ELSE 0 END AS is_friend
FROM users u
LEFT JOIN user_friends uf
  ON uf.friend_id = u.id AND uf.owner_id = ?
WHERE DATE(u.reg_date) = CURDATE()
ORDER BY u.id DESC
LIMIT 3;

Loop the result set and render the appropriate label. Using PDO (prepared statements) and escaping output prevents injection/XSS:

$stmt = $pdo->prepare($sql);
$stmt->execute([$currentUserId]);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo '<tr><td><a href="DisplayBasedOnID.php?id='.$row['id'].'">'
       .htmlspecialchars($row['fname'].' '.$row['lname']).'</a></td><td>';
  if ($row['is_friend']) {
    echo '<span class="label label-default">You are Friends</span>';
  } else {
    echo '<a class="label label-primary" href="AddFriend.php?id='.$row['id'].'">Add Friend</a>';
  }
  echo '</td></tr>';
}

Notes: keep the current user id from session (cast to int), use prepared statements for any inserts/selects, and add the UNIQUE primary key on (owner_id, friend_id) to prevent duplicates. If migration of existing addfriends rows is needed, populate owner_id appropriately before switching to the new pattern.

Recommended Answers

All 11 Replies

Member Avatar for R_4

Hello
Any help please !!

Member Avatar for Dani

Hi there. Sorry for taking so long to see this thread. I'm looking at your code and I'm a little confused what you want to do.

It looks like on line 12, you're inserting a row into the addfriends table with the information of user $id and setting friendshipStatus to 1.

However, I think what you're trying to do is on line 5, you want to: SELECT * FROM addfriends WHERE id = $id AND friendshipStatus = 1 because right now you are just checking if the user $id has a row in the table, and not if the friendshipStatus column is set to 1.

Please let me know if I can help further.

Member Avatar for R_4

Hello Dani,
Thanks for your concern.
What I am trying to do is to detect if user in addfriends table or not.
So if it is in addfriends table to put beside its name a "Add Friend" button .But if it is not in the addfriends table to put beside its name a "You are Friends" button instead.
In the following query I accomplished the first part of what I want tyo do tha t to add "Add Friend" button beside user name if it is in addfriends table. But now how to add "You are Friends" button instead if it is not in the addfriends table?

<?php
include 'config.php';
//Show The Three New Users that have registered today.
$sql = "SELECT * FROM `users`
 WHERE DATE(`reg_date`) = CURDATE()
 ORDER BY id desc
 LIMIT 3";

$result = $conn->query($sql);
      echo "<br />";
      echo "<span class=\"label label-danger col-sm-12\">New Users</span></th>";
      echo "<br />";

while($row = $result->fetch_assoc()) {
    //To display user's profile when click on it.
  echo "<tr>";
  echo "<td><a href='DisplayBasedOnID.php?id=" . $row['id'] . "'>
       ".$row["fname"]." ".$row["lname"]."
       </a></td>"; 
   //To display "Add Friend" button beside each username.
  echo "<td>
  <span class=\"label label-primary pull-right\">
  <a href='AddFriend.php?id=" . $row['id'] . "'style=\"color:White\">
   Add Friend
    </a>
    </span>
    </td>"; 
    echo "</tr>";
  echo "<br />";
    }
Member Avatar for R_4

Upppp

Member Avatar for Dani

I haven’t slept well the past couple of days and I’ve been working nonstop. I’m already in bed now so I’ll check this out tomorrow.

Member Avatar for R_4

I am really sorry then.
Take your time .

Member Avatar for Dani

Soooo sorry. I know I told you I would have checked this out by now but I've just been so busy the past couple days and I have a very bad migraine today.

Member Avatar for R_4

I am really sorry again. And I hope you get well soon.

Member Avatar for R_4

Helllo

Member Avatar for R_4

uppppp

Member Avatar for Dani

Gahhhh! Thanks for the bump (reminder). I’ll look at this tomorrow. I promise. Sorrrryyyyyyy.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.