mysql query to select all but limit each occurrence of parameter

Question

I'm having a hard time wording this question. I have a database of messages and I want to retrieve, for example, the latest 2 from each conversation. How can I put a limit inside the query syntax to limit each unique user to just two? Or do I have to select all and then loop inside the fetch assoc? The query looks something like this:

$to = $_REQUEST['to'];
$lastid = $_REQUEST['lastId'];
$link = mysqli_connect('localhost', 'root', '', 'messagedb');
if ($link) {
    $query = mysqli_query($link, "SELECT * FROM messages WHERE (toName='$to' OR fromName='$to') AND id>'$lastid' order by id desc");
    $messages = array();
    while ($row = mysqli_fetch_assoc($query)) {
        if (!in_array($row['toName'], $messages) || !in_array($row['fromName'], $messages)) {
            $messages[] = $row;
        }
    }
}

Of course that's not working...it just gets everything back...plus I can't put a limit on how many rows already in there. I'm hoping there's a simple solutions for the query. Thanks in advance!

The database looks something like this:

id  |  fromName   |   toName   |   theMessage
------------------------------------------------------------
 1        me           joe       some message to joe from me
 2        john         me        message from john to me
 3        sarah        me        message from sarah to me
 4        joe          me        answer back to joe from me
 5        me           john      answer back to john from me
 6        me           sarah     answer back to sarah from me
 //and so on...just conversations between people

So in my query, the $to is me, or whatever user will be using it. I want to get my/their conversations with each person but limit it to a certain number at the start...so later they can load more.

Can you describe more accurately what you're trying to achieve ? And also (if possible) can you make a schema of the table in question ? — Kei, Apr 18 '14 at 17:32
ok, one second. sorry, after I posted the question I figured people would need to see how it is constructed. — denikov, Apr 18 '14 at 17:34

score 0 · Answer 1 · answered Apr 18 '14 at 17:42

0

Use this code:

SELECT * FROM messages WHERE (toName='$to' OR fromName='$to') AND id>'$lastid' order by id desc LIMIT number_of_rows

answered Apr 18 '14 at 17:42

agent420

3,291
20
27

That doesn't work...it just gets back the last two rows. What I want is the last two rows of messages from, like in my question's database, last two rows of the conversation from me and sarah, me and joe, me and john. – denikov Apr 18 '14 at 17:53

score 0 · Answer 2 · answered Apr 18 '14 at 17:43

Firstly Copy and Paste This And Than Check..

Nothing Just you made an Error on putting Varaibles

$to = $_REQUEST['to'];
$lastid = $_REQUEST['lastId'];
$link = mysqli_connect('localhost', 'root', '', 'messagedb');
if ($link) {
    $query = mysqli_query($link, "SELECT * FROM `messages` WHERE (toName='".$to."' OR fromName='".$to."') AND id>'".$lastid."' order by id desc");
    $messages = array();
    while ($row = mysqli_fetch_assoc($query)) {
        if (!in_array($row['toName'], $messages) || !in_array($row['fromName'], $messages)) {
            $messages[] = $row;
        }
    }
}

Thanks for the answer...it's still giving me everything from the database. How can I loop the rows and just add to $messages the last 2 entries of 'toName' or 'fromName'? — denikov, Apr 18 '14 at 17:51

score 0 · Answer 3 · answered Apr 18 '14 at 17:55

0

There are many things which would need attention (like the fact you're not relying on some date/time field to organize messages). Then, as you are stating, the best method would be using PHP to take the last two rows from each combination of from/to addresses. Otherwise you could also use the top clause, but I can hardly think of a method to achieve what you want directly.

answered Apr 18 '14 at 17:55

Kei

771
6
17

What exactly do you mean when you say "take the last two rows from each combination of from/to addresses"? How is that done with PHP? – denikov Apr 18 '14 at 18:10
1

The identifier for each conversation is the pair "from/to-to/from" which is unique and identify each conversation. Using that you can loop and identify each conversation, then you should only take the first two. Also I found something that may interest you http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – Kei Apr 18 '14 at 18:40

mysql query to select all but limit each occurrence of parameter

3 Answers3