SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
-
Nov 23, 2009, 10:26 #1
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Selecting records for today - Part 2
This is in reference to my original post, which has been moved to PHP - I didn't realise until too late:
http://www.sitepoint.com/forums/showthread.php?t=648498
However this part is MySQL purely and NOT php.
********************************
ok guys. next part to the question, which is purely SQL not php related.
I want to count messages from user 8 where the recipient of the messages is unique between these date ranges - to see how many unique people they contacted.
2nd half- I would further like to refine the above, to only count the times the unque person replied - but this part is over my head.
Eg.
if "user id: 1" contacts "user id: 2" 10 times and "user id: 2" replies ( once or more ) = 1 successful contact attempt
if "user id: 1" contacts "user id: 3" 20 times (or however many) and "user id: 3" doesnt reply = failed contact attempt
the table format is:
id (unique auto increment), from (user id of sender), to (user id of recipient), sent (time as int(10))
each message = 1 record
so if user 1 contacts user 2 = 1 record (from = 1, to = 2)
and user 2 replies to user 1 = 1 more record (from = 2, to = 1)
and user 2 messages user 3 = another record (from = 2, to = 3)
I really hope this makes sense.
So far I got:
SELECT count(DISTINCT messages.to) FROM messages WHERE messages.from = 8 and messages.sent >= 1258927200 and messages.sent <= 1259013599
But that only counts unique attempts (successful and failed contact attempts), not just successful attempts.
-
Nov 23, 2009, 10:37 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you will need a couple LEFT OUTER JOINs
Code:FROM messages AS mfrom LEFT OUTER JOIN messages AS mto ON mto.from = mfrom.to LEFT OUTER JOIN messages AS mreply ON mreply.from = mto.to AND mreply.to = mfrom.from
you also need a GROUP BY, because you want to count the number of messages and/or contact attempts
and of course you'll want to work that date range test in there, although it's not clear to me whether it should apply to the contact attempt or the reply or both
but the main thing is, you have to understand the two LEFT OUTER JOINs there...
-
Nov 23, 2009, 10:54 #3
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hmm I think I understand the joins (though they are my weak point).
Is this what you were meaning (query in full):
Code MySQL:
Also the replies mreply need to be within the above specified date range too (ie not include any previous contact that may have occurred)
so if user 10 messages user 8 yesterday.
user 8 messages user 10 today, and user 10 DOESNT message user 8 today = not successful contact for user 8 within today.
-
Nov 23, 2009, 22:24 #4
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Would this be right, taking into consideration the reply must also be in the same day - and after the original mail was sent?
Code MySQL:SELECT * FROM messages AS mfrom LEFT OUTER JOIN messages AS mto ON mto.FROM = mfrom.to LEFT OUTER JOIN messages AS mreply ON mreply.FROM = mto.to AND mreply.to = mfrom.FROM WHERE mfrom.FROM = 8 AND mfrom.sent >= 1258927200 AND mfrom.sent <= 1259013599 AND mreply.sent > mfrom.sent AND mreply.sent <= 1259013599 GROUP BY mfrom.to
-
Nov 23, 2009, 22:29 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 23, 2009, 22:48 #6
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
No results (0 records), until I delete "AND mreply.sent > mfrom.sent AND mreply.sent <= 1259013599". If I delete this I get a result, but even if the "reply" was from an earlier period, which is wrong.
-
Nov 23, 2009, 22:49 #7
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
But need the reply to be after the sent obviously. So if the recipient messaged them earlier in the day it shouldnt be counted.
-
Nov 23, 2009, 22:53 #8
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
id from to message sent
152 8 17 hello 1259037014
153 17 8 hi back 1259037050
In the perspective of "WHERE mfrom.FROM = 8" should be 1 (because the message from 17 was after (but in the same day) as the message from 8)
In the perspective of "WHERE mfrom.FROM = 17" should be 0 (because the message from 8 was before the message from 17)
-
Nov 24, 2009, 01:16 #9
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Interesting..Removing the first outer join (mto) SEEMS to have possible fixed the problem:
Code MySQL:
Would love your opinion r937.
Thanks
-
Nov 24, 2009, 07:10 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
yeah, that looks like it'll work
i'm not sure why i had two joins
of course, i was coding blind, i didn't have any data...
Bookmarks