vb.org Archive - Help with a query?

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Help with a query? (https://vborg.vbsupport.ru/showthread.php?t=278977)

successguy

02-22-2012 07:10 AM

Help with a query?

I'm wondering if anybody would be so kind as to tell me how to do this query. I want to know every member who has posted on a given day sorted in order of number of posts they made that day. So if for example I asked who posted on February 21, 2012, it would show me everybody who posted on that day. And the sorting would be done not by the member's total posts, but by their total posts for that day only. You can basically already do this in the search engine, but you can sort by total posts only.

kh99	02-22-2012 08:40 AM

Try this:

Code:

SELECT userid, username, COUNT(*) as count FROM post 

WHERE visible=1 AND 

UNIX_TIMESTAMP('2012-02-22') < dateline AND

UNIX_TIMESTAMP('2012-02-23') >= dateline

GROUP BY userid ORDER BY count DESC

I tried to come up with one that let you just enter the date once, but I'm not that familiar with the mysql functions. On the plus side, this lets you find posts between any two dates.

successguy

02-22-2012 08:55 AM

Thanks so much, really appreciate it!

nhawk

02-22-2012 04:56 PM

You were on the right track...

The way you have it, the timestamp will contain the time of 00:00:00 so this will give only the date chosen. (between 2012-02-22 00:00:00 and 2012-02-22 11:59:59)

Code:

SELECT userid, username, COUNT(*) as count FROM post 

WHERE visible=1 AND 

UNIX_TIMESTAMP('2012-02-22') >= dateline AND

UNIX_TIMESTAMP('2012-02-23') < dateline

GROUP BY userid ORDER BY count DESC

kh99	02-22-2012 05:00 PM

Quote:

Originally Posted by nhawk (Post 2302337)

You were on the right track...

The timestamp will contain the hour of 00:00:00 so this will give only the date chosen. (between 2012-02-22 00:00:00 and 2012-02-22 11:59:59)

Code:

SELECT userid, username, COUNT(*) as count FROM post 

WHERE visible=1 AND 

UNIX_TIMESTAMP('2012-02-22') >= dateline AND

UNIX_TIMESTAMP('2012-02-23') < dateline

GROUP BY userid ORDER BY count DESC

That doesn't work, you've got the comparisons backward. What I meant was I tried to think of a way where, if you wanted to change the date you'd only have to change it in one place instead of two.

nhawk

02-22-2012 05:17 PM

You're right, a little brain freeze here...

should be this to account for 00:00:00 time..

Code:

SELECT userid, username, COUNT(*) as count FROM post 

WHERE visible=1 AND 

dateline >= UNIX_TIMESTAMP('2012-02-22') AND

dateline < UNIX_TIMESTAMP('2012-02-23')

GROUP BY userid ORDER BY count DESC

Because dateline will be greater than or equal to unix timestamp of '2012-02-22 00:00:00'.

And dateline will be less than a unix timestamp of '2012-02-23 00:00:00'.

kh99	02-22-2012 05:45 PM

Well, that's equivalent to what I had except for where you put the '=', but fair enough, it belongs on the lower date to have the query results match the posts with the given date in the dateline. But it would only affect posts that were posted exactly at midnight (to the second).

All times are GMT. The time now is 02:39 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01043 seconds Memory Usage 1,726KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: