SQL LIMIT always returns the same last 3 records

Question

I have a problem in a select. I have the following table

id   id_de      id_para      mensagem           
---  ---------  -----------  -----------
1    10         20           Oi David.
2    20         10           Oi José
3    10         20           Tudo bem?
4    20         10           Sim, e com vc?
5    10         20           Estou ótimo?

Where id is the identifier of each message, id_de is the id of who is sending the message and id_to is the id of who is receiving the message.

My select is:

$select = BD::conn()->prepare("SELECT * FROM `mensagem` WHERE (`id_de`= ? AND `id_para` = ?) OR (`id_de` = ? AND `id_para` = ?) LIMIT 3");
            $select->execute(array($_SESSION['id_user'], $id, $id, $_SESSION['id_user']));

But the problem is if I send more messages from one user to another, it will always select the same 3 messages. If I take out the LIMIT from Select, it returns all messages correctly.

Please if anyone can help me, I’ll be grateful.

Answer 1

This happens pq the LIMIT will return only the first 3 records.

You can give a order by id desc, but it will bring you back forward. This is the easiest way to solve your problem.

To solve the problem of order I suggest the following:

• Query and use the order by id desc as I directed you.
• Turn this query into a sub select. This sub select sorts you by id again.

It’s ugly, but it’ll solve your problem.