PHP - Bring the result of a table according to the most recent message from another table?

Question

So.. I have 3 table:

user:
    | ID | NOME | SOBRENOME |
    | 01 | Igor | Ferreira  |
    | 02 | João | Henrique  |
    | 03 | Rose | Beltão    |

following:
    | ID | user1_id | user2_id |
    | 01 |    01    |    02    |  User 1 > Segue User 2
    | 02 |    02    |    01    |     -- User 2 > Segue User 1
    | 03 |    01    |    03    |  User 1 > Segue User 3
    | 04 |    03    |    01    |     -- User 3 > Segue User 1
    | 05 |    03    |    02    |  User 3 > Segue User 2
    | 06 |    02    |    03    |     -- User 2 > Segue User 3

chat:
    | ID | user1 | user2 |      mensagem      |       data       |
    | 01 |   02  |   03  | ola Rose.          | 30/07/2015 08:25 |  De João > Para Rose
    | 02 |   03  |   02  | Oi João, como tas? | 30/07/2015 08:28 |  De Rose > Para João
    | 03 |   02  |   03  | Estou bem, e você? | 30/07/2015 08:29 |  De João > Para Rose
    | 04 |   01  |   02  | Rose você esta ai? | 30/07/2015 09:11 |  De Igor > Para Rose

In a div I’m listing the Friends that the user follows:

$sqln = mysqli_query($conn,"SELECT * FROM users 
                            WHERE (id = '$id') 
                            OR (id IN (SELECT user2_id FROM following WHERE user1_id='$id'))
                            ORDER BY id ASC");

But the only flaw is that it only lists the users who follow it, and what I really need is that it (div), YES, list the users who follow her, but more dynamically bringing the users she follows, but the ones who talked to her and who TOP to the LAST according to the latest messages**..

It is basically to make the research in the query which lists the interconnected tables (User and Following) also contact the table Chat as if he were carrying a ORDER according to recent messages between users that the user will follow.

I’ve seen examples like this but it didn’t work:

SELECT u.id, u.nome, MAX(c.data_hora) AS last_msg FROM chat c 
INNER JOIN users u ON c.id_para = u.id
WHERE c.id_de = 1
GROUP BY c.id_para ORDER BY last_msg DESC;

Answer 1

SELECT
    u.id,
    u.foto,
    u.username,
    u.nome_us,
    u.sobrenome_us,
    u.tell
FROM users u
   RIGHT JOIN following f
      ON f.user1_id = '$id'
   INNER JOIN chat c
      ON ((c.user1 = '$id' and c.user2 = f.user2_id) or (c.user1 = f.user2_id and c.user2 = '$id'))
WHERE
   u.ID = f.user2_id
GROUP BY
   f.user2_id
ORDER BY
   c.data DESC, c.hora DESC

When executing the select above you will be bringing the columns "id", "photo", "username", etc. These column names should be used to read the obtained data:

$id_q = $num_q['id'];
$foto = $num_q['foto'];
$username = $num_q['username'];

Answer 2

Pedro, try this query below. However, I don’t know what you need. I needed to create a temporary bank to test better.

If you want to use the Sqlfiddle to create a temporary bank and give us the LINK to help you better.

SELECT
    NOME, SOBRENOME
FROM
    USER
INNER JOIN
    FOLLOWING ON FOLLOWING.USER1_ID = USER.ID
INNER JOIN
    CHAT ON CHAT.USER2 = FOLLOWING.USER2_ID
ORDER BY 
    DATA 
DESC