[SOLVED] Finding the Issue With My SELECT Statement

[hoopplaya4]

Hello all,

 

I need some help finding the 'leak' in my SELECT statement.  I currently have 3 tables (only two will be shown, because the other is not pertinent).  (uploaded images so  I wouldn't have to type entire table).

 

 

 

What I'm trying to do is output an "inbox" that displays the most recent thread at the top and group by thread (meaning, you don't see the same thread twice in your inbox).  My query works for the most part, except for the fact that it does not appear to be displaying the "most recent thread" based on the timestamp.

 

Here's my SELECT statement:

 

<?php
$data = mysql_query("SELECT pm_info.is_read, sender.usrFirst as sender_name, 
    pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid
FROM pm_info
INNER JOIN pm_data ON pm_info.message_id = pm_data.id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID
WHERE pm_data.date_sent IN(SELECT MAX(date_sent) FROM pm_data WHERE pm_info.message_id = pm_data.id GROUP BY pm_data.thread_id) 
AND pm_info.receiver_id = '$usrID' GROUP BY pm_data.thread_id ORDER BY date_sent DESC") or die(mysql_error());

 

And here's what the incorrect output looks like:

 

 

 

Any help on figuring out what's wrong with my statement is greatly appreciated!!

[kickstart]

Hi

 

Group by is the problem. It is designed for grouping rows where all / all but one of the values are the same, and then counting the results per group / summing the remaining value.

 

As such for the grouping doesn't really care about other values (most flavours of SQL will throw a nasty message if you try and use group by in the way you have).

 

For the row for the thread_id 13587 there are 2 items being returned (id 10 and 12). MySQL will land up randomly (well, not strictly random, but not of any meaningful or fixed order) returning the fields not mentioned in the group by and which are not summed / group_concated.

 

Quick play (so probably some typos) but think you want some SQL like this:-

 

SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid, thread_max_date_sent
FROM pm_info
INNER JOIN pm_data ON pm_info.message_id = pm_data.id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID
INNER JOIN (SELECT thread_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data GROUP BY thread_id) deriv1 ON pm_data.thread_id = deriv1.thread_id
WHERE pm_info.receiver_id = '$usrID' 
ORDER BY deriv1.thread_max_date_sent DESC

 

All the best

 

Keith

[hoopplaya4]

Hey Keith,

 

Thanks for the reply.  That definitely makes more sense.  Do you have any suggestions for fixing/re-arranging my statement to yield the results I'm looking for?

 

Thanks!

 

EDIT: Nevermind, just saw your edit.    I'll take a look at it and report back.

[hoopplaya4]

Ok, so I went ahead and implemented your edits.  No errors or typos, that I could see.

 

However, it now outputs the following:

 

From: Ed (pm_data.id=10)  single message      (thread_id=13587)
From: Ed (pm_data.id=12)  RE: single message  (thread_id=13587)
From: Ed (pm_data.id=   RE: Test Number 2   (thread_id=16256)

 

Any other ideas on how to fix this?

 

[kickstart]

Hi

 

Take it you just want the latest message from each thread. Like:-

 

From: Ed (pm_data.id=10)  single message      (thread_id=13587)
From: Ed (pm_data.id=   RE: Test Number 2   (thread_id=16256)

 

If so try:-

 

SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid, thread_max_date_sent

FROM pm_info

INNER JOIN pm_data ON pm_info.message_id = pm_data.id

INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID

INNER JOIN (SELECT thread_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data GROUP BY thread_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent

WHERE pm_info.receiver_id = '$usrID'

ORDER BY deriv1.thread_max_date_sent DESC

 

All the best

 

Keith

[hoopplaya4]

Hi

 

Take it you just want the latest message from each thread. Like:-

 

From: Ed (pm_data.id=10)  single message      (thread_id=13587)
From: Ed (pm_data.id=   RE: Test Number 2   (thread_id=16256)

Yes, you are correct, but actually, the latest message from each thread would look like-

From: Ed (pm_data.id=12)  RE: single message      (thread_id=13587)
From: Ed (pm_data.id=   RE: Test Number 2   (thread_id=16256)

 

Nonetheless, I tried your new edit, and now it only displays:

 

From: Ed (pm_data.id=12)  RE: single message      (thread_id=13587)

 

Interestingly enough, it's not even displaying (pm_data.id=.

 

Any ideas?  My brain is starting to hurt. 

 

[kickstart]

Hi

 

My fault.

 

The subselect it getting the latest message for a thread, but that latest message for thread 16256 is message id 9 (which has a different sender id). Can't test it without setting up a load of tables, so trying again. This should get the latest post for the thread for that particular sender.

 

SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid, thread_max_date_sent

FROM pm_info

INNER JOIN pm_data ON pm_info.message_id = pm_data.id

INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID

INNER JOIN (SELECT thread_id, sender_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data GROUP BY thread_id, sender_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent AND pm_data.sender_id = deriv1.sender_id

WHERE pm_info.receiver_id = '$usrID'

ORDER BY deriv1.thread_max_date_sent DESC

 

All the best

 

Keith

[hoopplaya4]

WOW!  That seems like it worked!  I think that's one of the most complicated SELECT statements I've ever used.  It certainly helped.  Thanks again for your help!

[kickstart]

Hi

 

No problem. Compared to some that get written that is pretty simple (and they get even worse if you land up having to use MS Access ).

 

All the best

 

Keith

[hoopplaya4]

Okay, so I thought I had this figured out, but it appears there's something I missed out on.

 

I've just found that if there are "more than 2" people involved in a thread (e.g., multiple recipients), it no longer groups the rows by "thread_id".  I'm thinking this might be due to the fact that when a 3rd person "replies" to the thread, there's a new "sender_id" in the mix.

 

Here's a quick example, adding on to what we've been talking about:

 

From: Ed (pm_data.id=12)  RE: single message      (thread_id=13587)

From: Ed (pm_data.id=  RE: Test Number 2  (thread_id=16256)

 

So, if another user named Joe (let's assume they were one of the original recipients of a message), replies to the same thread (RE: single message (thread_id-13587), I'd want the inbox to look like this:

 

From: Joe (pm_data.id=13)  RE: single message      (thread_id=13587)         //Note: same thread_id but new pm_data id.
From: Ed (pm_data.id=   RE: Test Number 2   (thread_id=16256)           //This remains unchanged.
From: Ed (pm_data.id=   RE: Test Number 2   (thread_id=16256)

 

Therefore, what I am looking to display is:  "The latest message from each thread, no matter who the sender is."  I don't want to have two rows that have the same thread_id but different sender_id.  Instead, just the most recent out of the two.

 

Is what I'm asking for possible with my current db structure?  Thank you!

[kickstart]

Hi

 

Sorry, I am getting a bit confused about what you want. Is there a typo in the example list you have above?

 

I think what you want is to group them by recipient id rather than sender id. If so this is a quick attempt at it (and not certain I have done it right, getting late and I need to go to bed!)

 

SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid, thread_max_date_sent
FROM pm_info
INNER JOIN pm_data ON pm_info.message_id = pm_data.id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID
INNER JOIN (SELECT thread_id, receiver_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data a INNER JOIN pm_info b ON a.data_id = b.message_id GROUP BY thread_id, receiver_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent AND pm_info.sender_id = deriv1.receiver_id
WHERE pm_info.receiver_id = '$usrID' 
ORDER BY deriv1.thread_max_date_sent DESC

 

All the best

 

Keith

[hoopplaya4]

Hi Keith,

 

I think you got it on this one!    I made a slight correction to your query:

 

SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid, thread_max_date_sent

FROM pm_info

INNER JOIN pm_data ON pm_info.message_id = pm_data.id

INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID

INNER JOIN (SELECT thread_id, receiver_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data a INNER JOIN pm_info b ON a.id = b.message_id GROUP BY thread_id, receiver_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent AND pm_info.receiver_id = deriv1.receiver_id

WHERE pm_info.receiver_id = '$usrID'

ORDER BY deriv1.thread_max_date_sent DESC

 

This appears to be working pretty good!

 

 

Also, I do have one additional question: I'm looking for some help on setting up a "sent messages" query as well.  It would be the same as sent messages, except, if a message is sent to multiple people (more than 1), it will not display every sent message.  Instead, it would shrink it down into one row.

 

I currently have this:

 

SELECT pm_info.*, sender.usrFirst as receiver_name, pm_data.*, thread_max_date_sent

FROM pm_data

INNER JOIN pm_info ON pm_info.message_id = pm_data.id

INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID

INNER JOIN (SELECT thread_id, sender_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data GROUP BY thread_id, sender_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent AND pm_data.sender_id = deriv1.sender_id

WHERE pm_data.sender_id = '$usrID'

ORDER BY deriv1.thread_max_date_sent DESC

 

And it displays something like this:

 

To: Ed  (thread_id=22)  Subject: Hello There

To: Joe  (thread_id=22)  Subject: Hello There

 

Instead, I'd like it to display something like:

 

To: 2 Users  Subject: Hello There

 

If I need to put this request in a new thread, I can do so.  Thanks again for your help and get some rest!

 

[kickstart]

Hi

 

I suspect the problem might be that if you have sent a message to multiple people then all of those messages will have the same date_sent (so when you try and get the latest one you will get return 2).

 

2 ways I can see round that. The cheats way would be that when sending them you record each one with a second added to the date / time (ie, first uses the right date / time, 2nd adds one second, third adds 2 seconds, etc). This would work and would allow you to use the current code, but is a bodge and does mean that you are recording slightly wrong info for the messages.

 

2nd way would be to use the id field of pm_data, if that is always higher for newer messages:-

 

SELECT pm_info.*, sender.usrFirst as receiver_name, pm_data.*, thread_max_id

FROM pm_data

INNER JOIN pm_info ON pm_info.message_id = pm_data.id

INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID

INNER JOIN (SELECT thread_id, sender_id, MAX(id) AS thread_max_id FROM pm_data GROUP BY thread_id, sender_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.id = deriv1.thread_max_id AND pm_data.sender_id = deriv1.sender_id

WHERE pm_data.sender_id = '$usrID'

ORDER BY deriv1.thread_max_id DESC

 

All the best

 

Keith

[hoopplaya4]

Hey Keith:

 

Thanks for the reply.  After some finagling, I came up with the following query:

 

SELECT pm_info.is_read, group_concat(DISTINCT receiver.usrFirst) as receiver_name,

    pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as data_id,

    MAX(date_sent) AS thread_max_date_sent

FROM pm_info

INNER JOIN pm_data ON pm_info.message_id = pm_data.id

INNER JOIN tblUsers AS receiver ON pm_info.receiver_id = receiver.usrID

WHERE pm_data.sender_id = '$usrID'

GROUP BY pm_data.thread_id

ORDER BY thread_max_date_sent DESC

 

 

This works properly in that it groups the threads, and it actually works great.  There's only one thing that I'd like to see if I can get some help on.

 

It currently displays a row as this:

 

To: Joe, Steve, Ed, Bob                  Subject: Whatever

 

However, if a thread has 30 recipients, I don't want it to display every single name.  Instead, I'd like to add a COUNT somewhere (not sure where?) in the SELECT statement that counts the recipients.  This way, I could implement in my PHP that say:

 

 <?php
if ($row['count_recipients'] > 3) {
echo "To: ".$row['count_recipients']." people.";
}
//Which would output: To 4 people

 

Any idea on how to add a COUNT to my current statement to get that data?

 

Thanks!

[kickstart]

Hi

 

Not 100% sure about having 2 agregate functions in 1 piece of SQL, but I think MySQL would probably support it (and you already have 2, so this would be a third). Try a COUNT(DISTINCT receiver.userFirst) as a clause.

 

One thing I notice is that you have pm_info.is_read as a field on your select. The value of this is likely to be randomly one of the values for one of the recipients (ie, if Joe and Steve had read it but Ed and Bob hadn't you could land up with is_read being 0 or 1). Not really a way round this without removing the group_concat and then looping through individual recipients.

 

Try this:-

 

SELECT group_concat(DISTINCT receiver.usrFirst) as receiver_name,COUNT(DISTINCT receiver.usrFirst) as receiver_count,

    pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as data_id,

    MAX(date_sent) AS thread_max_date_sent

FROM pm_info

INNER JOIN pm_data ON pm_info.message_id = pm_data.id

INNER JOIN tblUsers AS receiver ON pm_info.receiver_id = receiver.usrID

WHERE pm_data.sender_id = '$usrID'

GROUP BY pm_data.thread_id

ORDER BY thread_max_date_sent DESC

 

All the best

 

Keith

[hoopplaya4]

Hi Keith:

 

That seemed to do the trick.  I was trying to place the COUNT is the first 'group_concat(DISTINCT receiver.usrFirst).

 

Also, not sure what I was thinking with the 'is_read' portion--probably just put it in there during the dark hours of the night.

 

Thanks again, you've helped me learn tremendously!