CODEWITHSUNDEEP
phpmysqlsql
Chiqui
Chiqui

Reputation: 39

I need to get the list of users who have previously been sent message (MySQL)

I am making an instant messaging web page, I have assembled the entire database and made 100% of the front-end of the entire page, I only have to do the start menu ... So in the start menu I just want to show users that ** a message has been sent to before **, I haven't managed to do this, and right now I'm showing a list of ** all registered users ** .. Something like the following is what I am showing

PROFILE PHOTO 1 - User Nº1 - Last message sent - Status (Offline / Online)
PROFILE PHOTO 2 - User Nº2 - No messages have been sent - Status (Offline / Online)
PROFILE PHOTO 3 - User Nº3 - Last message sent - Status (Offline / Online)
PROFILE PHOTO 4 - User Nº4 - Last message sent - Status (Offline / Online)

When what I want to show is something like the following:

PROFILE PHOTO 1 - User Nº1 - Last message sent - Status (Offline / Online)
PROFILE PHOTO 3 - User Nº3 - Last message sent - Status (Offline / Online)
PROFILE PHOTO 4 - User Nº4 - Last message sent - Status (Offline / Online)

Currently the design of my table is as follows:

> messages
msg_id | incoming_msg_id | incoming_msg_id | outgoing_msg_id | msg

> users
user_id | unique_id | fname | lname | email | password | img | status

Clarification, the incoming_msg_id field is the ID of the user who receives the message, and the outgoing_msg_id is the one that sends it, which in this case matches the user_id field of the users table.

Currently my code is the following, which, as I have told you, shows what I leave below.

<?php
    session_start();
    include_once "config.php";
    $outgoing_id = $_SESSION['unique_id'];
    $sql = "SELECT * FROM users WHERE NOT unique_id = {$outgoing_id} ORDER BY user_id DESC";
    $query = mysqli_query($conn, $sql);
    $output = "";
    if(mysqli_num_rows($query) == 0){
        $output .= "No users are available to chat";
    }elseif(mysqli_num_rows($query) > 0){
        include_once "data.php";
    }
    echo $output;
?>

PROFILE PHOTO 1 - User Nº1 - Last message sent - Status (Offline / Online)
PROFILE PHOTO 2 - User Nº2 - No messages have been sent - Status (Offline / Online)
PROFILE PHOTO 3 - User Nº3 - Last message sent - Status (Offline / Online)
PROFILE PHOTO 4 - User Nº4 - Last message sent - Status (Offline / Online)

Finally I leave you two real and current photos of my boards:

EDITED

Upvotes: 1

Views: 691

Answers (1)

Adamszsz
Adamszsz

Reputation: 581

Firstly you need to get field user_id in table (messages). Later you can join this table and write select like :

select * from users usr
join messages msg on msg.user_id = usr.user_id
where msg.incoming_msg_id is not null

It will show later all users who sended message.

@UPDATE

If you said the field outgoing_msg_id is the user id there select will be like this:

select DISTINCT usr.user_id,usr.fname,usr.lname from users usr
join messages msg on msg.outgoing_msg_id = usr.user_id

This select will show all users who sended message.

@EDIT 3

So your php code should be like :

<?php
    session_start();
    include_once "config.php";
    $outgoing_id = $_SESSION['unique_id'];
    $sql = "SELECT * FROM users WHERE NOT unique_id = {$outgoing_id} and unique_id in (select outgoing_msg_id from messages)  ORDER BY user_id DESC";
    $query = mysqli_query($conn, $sql);
    $output = "";
    if(mysqli_num_rows($query) == 0){
        $output .= "No users are available to chat";
    }elseif(mysqli_num_rows($query) > 0){
        include_once "data.php";
    }
    echo $output;
?>

Upvotes: 1

Related Questions