0

Hello,

Created a query for getting users list excluding the logged in user well I thought about creating a simple one but huh that didn't worked for me. Okay so what logic and for what did I created for. I tried to show the users that are friends with each other so in messeging box obviously to send a message users will only see thier friends with the elimination of logged in user name

Let suppose
user 1 is friend of user 2
user 1 is friend of user 3

So in the messages page I want to show the list of users i mean friends like this

user 2
user 3

Make sense? so this was the scenario I was looking to create but what happens here by using my logic when user 1 is logged in it show the messages tab like this

user 2
user 3

This is okay working fine but when I logged in as user 2 to send a reply to user 1 what happens in the list user 2 is not showing up how ??

Here is my sql table for messages

Messages table

msg_id | user_1 | user_2 | message | time 
------------------------------------------  
  1    | user 1 | user 3 | Hello   | timestamp
  2    | user 1 | user 2 | Hello   | timestamp
  3    | user 1 | user 3 | Hello   | timestamp
  4    | user 1 | user 3 |   Hi    | timestamp

Friends Table

  userid | user_1 | user_2 | status
    --------------------------------
      1    | user 1 | user 2 |   1   
      2    | user 1 | user 3 |   0   

Sor retreiving the users list I created the sql query as

<?php
    $get = mysqli_query($connection, 'SELECT * FROM friends INNER JOIN users ON friends.user_1 = users.uid WHERE status = "1" AND uid != "'.$_SESSION["uid"].'"');
    while($user = mysqli_fetch_assoc($get)) {
    if($user["user_1"] == $_SESSION["uid"]) {
    $pid = $user["user_1"];
    } elseif ($user["user_2"] == $_SESSION["uid"]) {
    $pid = $user["user_2"];
    }
    echo "<li><a href='#".$pid."'";
    ?>
    onclick='ajax_post(<?=$pid?>)'
    <?php echo "data-toggle='tab'>".$user["username"]."</a></li>";
    }
?>

When user 1 is logged in I can see the users list but when user 2 is logged in the users list is not showing up can anyone help me out with this. I am sorry I am not good in explanations so I cannot explain in so much brief

4
Contributors
12
Replies
50
Views
2 Years
Discussion Span
Last Post by UK-1991
Featured Replies
  • 1
    diafol 3,720   2 Years Ago

    I'm just confused with the `friends` table schema. What's userid? This suggests the id field in the users table. Not sure you even need it. I think... sender_id | recipient_id | status With `sender_id` and `recipient_id` together as a PK should work? Read More

0

@pirates this one is different query and that one is different please read this thread as this is totally different from my previous one

0

Can you show users table? I don't understand which values will assume uid, to me it should be an integer, but looking at your code it seems it would be a string, like user 1, otherwise those conditional statements are wrong:

if($user["user_1"] == $_SESSION["uid"])
0

Okay so uid is integar and user is also an integer uid is logged in user id and user is the friend user id

Users Table

  uid  | username | passowrd | fullname | email 
------------------------------------------  
  1    |  user 1  |  pass 1  |   Alan   | abc@xyz.com
  2    |  user 2  |  pass 2  | testuser | abc@xyz.com
  3    |  user 3  |  pass 3  |   smith  | abc@xyz.com
  4    |  user 4  |  pass 3  |   john   | abc@xyz.com
0

@pirates yes sorry about that it was mistaken I ouldnt find the way to reactive that is why I reposted my question my appology

0

Sure? So friends table is not like this:

userid | user_1 | user_2 | status
---------------------------------
  1    | user 1 | user 2 |   1   
  2    | user 1 | user 3 |   0   

but:

userid | user_1  | user_2  | status
-----------------------------------
  1    |    1    |    2    |   1   
  2    |    1    |    3    |   0   

Correct? Because, if the values of user_1 and user_2 are strings, when you do:

$user["user_1"] == $_SESSION["uid"]

you're comparing integers to strings: 'user 1' == 1 and it will not work. Same for messages table. Sorry if I insist on this point, but it's unclear to me.

0
userid | user_1 | user_2 | status
---------------------------------
  1    |    1   |    2   |   1   
  2    |    1   |    3   |   0   

I haven writtern user with 1 just to show you remaining it's nothing i is integer not string while inserting I didn't inserted the username i inserted uid from users table is that clear to you now ??

0

Ok, that's clear. But, excuse me, I'm a bit lost. At the moment I can only suggest you to try the query from a MySQL client and see if you get results, without involving PHP and sessions. Then you could share tables, data and queries through a service like SQLfiddle, to help us (or at least me :D) to understand better the issue. Here's an example:

Anyone wants to help?

1

I'm just confused with the friends table schema. What's userid? This suggests the id field in the users table.
Not sure you even need it. I think...

sender_id | recipient_id | status

With sender_id and recipient_id together as a PK should work?

0

@diafol well that is a good refactor I mean it is easy to get and make things clear well the userid i used as i would be requiring that as in future so it is an autoincremented value so I changed my table with some proper names and here is my refactored table

Messages table

        msg_id   | friend_id_1 | friend_id_2 | message | time 
----------------------------------------------------------  
  INT(11) AI  |   INT(11)   |   INT(11)   | text    | DATETIME

Friends Table

    friends_id | friend_id_1 | friend_id_2 |    status
  ------------------------------------------------------
  INT(11) AI  |   INT(11)   |   INT(11)   | ENUM('0', '1')

Users Table

  user_id   |   username   |   passowrd   |   fullname   |    email 
----------------------------------------------------------------------  
INT(11) AI | VARCHAR(145) | VARCHAR(145) | VARCHAR(145) | VARCHAR(145)

So now the scenario is made like this

friend_id_1 OR friend_id_2 is taken from users table as friend_id_1 = user_id OR friend_id_2 = user_id so 3 users let suppose is in the users table user 1 send a friend request to user 2 what will happen user_id will be taken from users table and will be dropped in to friends table if second users accept it the staus in the database table will be set to 1 from 0 default will be 0 for pending friend request

So now the simple which was not happening or I am unable to do so is let supose Mark sends a friend request to John and John accepts his friend request okay so Mark and John are friends and can send private messages to each other right ? so when John is logged in on friends page John will see Mark in his message users list and When Mark is logged in he will see John in his message users list make send so this is the simple thing I am trying to do so. okay now take this scenario Mark and John are friends I will show you how I tried to pull up the users list

Users Table

  user_id | username | passowrd |  fullname   |    email 
--------------------------------------------------------------
        1    |  mark.k  |  mark123 |  Mark Alan  | mark@domain.com
        2    |  John.d  |  john123 |  John Doe   | mark@domain.com

Friends Table

    friends_id | friend_id_1 | friend_id_2 |    status
  ------------------------------------------------------
              1     |      1      |      2      |       1 

Messages table

    msg_id | friend_id_1 | friend_id_2 |    message    | time 
----------------------------------------------------------  
        1    |      1      |      2      |   Hello John  | 0000-00-00
        2    |      1      |      2      |   Hello Mark  | 0000-00-00


<div class="tabs-left userlist">
<ul class="nav nav-tabs">
    <?php
        $get = mysqli_query($connection, 'SELECT * FROM friends INNER JOIN users ON friends.user_1 OR friends.user_2 = users.uid WHERE status = "1"');
        while($user = mysqli_fetch_assoc($get)) {
            $pid = $user["user_1"];
            echo "<li><a href='#".$pid."'";
            ?>
            onclick='ajax_post(<?=$pid?>)'
            <?php echo "data-toggle='tab'>".$user["username"]."</a></li>";
        }
    ?>
</ul>
</div>

Make sense ?

0

Thank You so much all of you guys it's donw I done by using logic of @diafol i was misplacing the queries by not using proper names though well thank you once again and sorry for taking your precious time

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.