SQL LIMIT always returns the same last 3 records

Question

SQL LIMIT always returns the same last 3 records

Navigation

#1 by (2 votes)

2

I have a problem with 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 do the select of the same 3 messages. If I take LIMIT's SELECT, it returns all messages correctly.

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

php mysql pdo

asked by anonymous 18.06.2015 / 02:03

1 answer

How to call a function after the user release the mouse click? Ruby on Rails and Javascript

score 2 · Accepted Answer

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

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

To solve the order problem I suggest the following:

Do your query and use order by id desc as directed.
Turn this query into a sub select. This sub select you sort by id again.

It's ugly, but it will solve your problem.