top poster during certain period of time

[shinstudio]

I'm trying to run my forum's promotion and in need to get top most poster during April.
I started to look at db structure and attempted to use "post" table, but "dateline" column is mystery to me.. Could anyone explain how dateline columns works? Is it possible to pick the most top poster during Aprial per say in vb?

Thanks!

[lycheepassion]

I'd be interested in knowing how to see this too, I was a fool and ran a 2 wk giveaway but I dont know how to tell now

[kh99]

I think this should work:

SELECT username, COUNT(*) as count FROM prefix_post 
WHERE dateline BETWEEN UNIX_TIMESTAMP(YYYYMMDD) AND UNIX_TIMESTAMP(YYYYMMDD)
   AND visible = 1
GROUP BY userid
ORDER BY count DESC

(of course you have to insert your own table prefix, and the dates you want in place of the YYYYMMDD)

ETA: ...and you probably need "AND visible = 1" in there to eliminate deleted posts (so I went ahead and added it).

[lycheepassion]

Hmm I am a bit of a noob, what do I do exactly lol sorry, do I go into phpmyadmin and execute that and then for dates put in the date period?

[kh99]

Right. If you have a table prefix you'd add it in place of prefix_ (if you don't know already, it should be obvious by looking at the table names in your db), then change the two YYMMDD's to real dates, so if you were interested in the first two weeks of Dec. they'd be 20101201 and 20101214.

[lycheepassion]

thanks I'll give it a try, rly appreciate it

--------------- Added [DATE]1293468430[/DATE] at [TIME]1293468430[/TIME] ---------------

prefix_post should be prefix_table names? hmmm what is this though for user sor forum? Ill pull it up and see if ic an tell

--------------- Added [DATE]1293468639[/DATE] at [TIME]1293468639[/TIME] ---------------

Hmm prefix_post so prefix is that like _forum db name or whatever?
or is it forum DB NAME_post

[kh99]

Sorry. The name of the table is 'post' but if you have a TABLE_PREFIX defined in includes/config.php, then that gets added to the beginning of the name. So, you can either look at includes/config.php, or if you can use phpmyadmin to list the tables in your database you should see that, if you have a prefix defined, they all begin with the same thing.

It's possible you don't have a prefix defined, in which case you'd just use 'post' as the table name. I guess if you're confused about this then you probably don't have a prefix, so just try 'post' and see if that works.

[lycheepassion]

OK I will check the file not sure I have a prefix defined what does prefix mean?

--------------- Added [DATE]1293469091[/DATE] at [TIME]1293469091[/TIME] ---------------

// ****** TABLE PREFIX ******
// Prefix that your vBulletin tables have in the database.
$config['Database']['tableprefix'] = '';

So its default?

--------------- Added [DATE]1293469132[/DATE] at [TIME]1293469132[/TIME] ---------------

so it should be prefix_post? sorry thanks

[kh99]

Prefix just means a string that is at the beginning. I think it's so you can have multiple vBulletin installations (or vbulletin and something else) in the same database without worrying about the table names being the same.

Like I said above, if it's not obvious what I mean from lookig at the list of tables in your database, then you probably just need to use 'post'.

--------------- Added [DATE]1293469271[/DATE] at [TIME]1293469271[/TIME] ---------------

Originally Posted by lycheepassion

so it should be prefix_post? sorry thanks

no, just post. Forget all about the prefix stuff, just use this:

SELECT username, COUNT(*) as count FROM post 
WHERE dateline BETWEEN UNIX_TIMESTAMP(YYYYMMDD) AND UNIX_TIMESTAMP(YYYYMMDD)
   AND visible = 1
GROUP BY userid
ORDER BY count DESC

(but still replace the dates, of course).

[lycheepassion]

thank you im going to try it!

--------------- Added [DATE]1293470357[/DATE] at [TIME]1293470357[/TIME] ---------------

You are awesome!!!! Thank you so so so so so so so so very much, this worked wonderfully! Repped

oops dont know how to give reps here but I would