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

Question

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

Navigation

#1 by (3 votes)
#2 by (1 votes)

5

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");

Buttheonlyflawisthatitonlyliststheuserswhofollowit,andwhatIreallyneedisthatit(div),YES,listuserswhofollowit,butmoredynamicwaybringingtheusersshefollowsbuttheoneswhotalkedtoherandTOPfortheLASTaccordingtothemostrecentmessages**..

It's basically making the search on the query that lists the linked tables ( User and Following ) also contact the Chat as if it were a ORDER according to the recent messages among users that the user will follow.

I saw examples like this but it did not 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;

php html mysqli

asked by anonymous 30.07.2015 / 13:51

2 answers

1

Pedro, try this query below. But I do not know is what you need. I needed to set up a temporary bank so we could test better.

If you want to use SQLFiddle to create a temporary bank and give us 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

30.07.2015 / 14:29

Index cardinality does not update Centralize elements using margin

score 3 · Accepted Answer

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 above select you will be bringing the columns "id", "photo", "username", etc. These column names that should be used to read the data obtained:

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