SQL Query - Total posts by user in date range - need help

  Post new topicReply to topicPrintable Version
<< View previous topic View next topic >>
Share: Del.icio.us  Digg  Google  Spurl  Blink  Furl  Y! MyWeb  
#1   SQL Query - Total posts by user in date range - need help
mjhufford
CZ Active Member
 Codezwiz Site Donator
mjhufford has been a member for over 20 year's 20 Year Member
usa.gif arkansas.gif
Occupation: IT Industry
Age: 47
Gender: Male
Fav. Sports Team: Da Bears.
Website:
Status: Offline
Joined: Jul 01, 2004
0.04 posts per day
Posts: 288
Points: 15,094
  MSN Messenger 
Hey guys,

I'm not the best with SQL queries yet, but I know what I want, so that's a start! I'm trying to tweak one of my modules so that it is only accessible to users who have posted in the forums "X" times in the last 7 days.

I'm having trouble building the query that will check if they've posted "X" times in

$today() - 7 - if that's how you write that..?


I noticed that there is a mod here that shows "Posts per day". That may work if I could get that code and dump it into an if statement.

If user posts per day is > X then bla bla else bla bla..
Any help is appreciated...I'll keep Google searching in the meantime.




_________________
"The pursuit of easy things makes men weak."
-David O. McKay
Back to top Reply with quote
#2   re: SQL Query - Total posts by user in date range - need hel
mjhufford
CZ Active Member
 Codezwiz Site Donator
mjhufford has been a member for over 20 year's 20 Year Member
usa.gif arkansas.gif
Occupation: IT Industry
Age: 47
Gender: Male
Fav. Sports Team: Da Bears.
Website:
Status: Offline
Joined: Jul 01, 2004
0.04 posts per day
Posts: 288
Points: 15,094
  MSN Messenger 
Okay guys & gals,

I think I figured it out. This is what I put in the index.php file of the module I wanted to do this for and it seems to be working. I adapted this from the posting required mod for the arcade. If you find that my code could use some work, by all means, let me know!


//My little mod to make people post in the forums in order to use this module
global $db, $prefix, $user;
getusrinfo($user);

if (is_user($user)) {$uid = $userinfo['user_id'];}

$secs = 86400;
$days = 5;
$posts = 5;
$current_time = time();
$old_time = $current_time - ($secs * $days);
$result = $db->sql_query("SELECT * FROM ".$prefix."_bbposts WHERE poster_id = $uid and post_time BETWEEN $old_time AND $current_time");
$Amount_Of_Posts = $db->sql_numrows( $result );


if ($Amount_Of_Posts < $posts)
   {include("header.php");
   $diff_posts = $posts - $Amount_Of_Posts;
   OpenTable();           
   echo "You need at least $posts forum posts in the last $days days to access this module.<br/>You need $diff_posts more posts.";
   CloseTable();
   include("footer.php");
//END MY MOD
} else {
Rest of module code here
}




_________________
"The pursuit of easy things makes men weak."
-David O. McKay
Back to top Reply with quote
#3   
pnclthnmstsh
CZ Active Member
pnclthnmstsh has been a member for over 20 year's 20 Year Member
usa.gif nevada.gif
Age: 53
Gender: Male
Website:
Status: Offline
Joined: Sep 17, 2004
0.04 posts per day
Posts: 267
Points: 13,641
   
That's a great little tweak! Thanks for posting it




_________________
[ Register or login to view links on this board.]
Back to top Reply with quote
#4   re: SQL Query - Total posts by user in date range - need hel
mjhufford
CZ Active Member
 Codezwiz Site Donator
mjhufford has been a member for over 20 year's 20 Year Member
usa.gif arkansas.gif
Occupation: IT Industry
Age: 47
Gender: Male
Fav. Sports Team: Da Bears.
Website:
Status: Offline
Joined: Jul 01, 2004
0.04 posts per day
Posts: 288
Points: 15,094
  MSN Messenger 
No prob. You can put it in any module you want to lock down that way. I've tweaked it some more to allow different user levels access and other user levels have to post in the forums before they get access. Let me know if you want the code.




_________________
"The pursuit of easy things makes men weak."
-David O. McKay
Back to top Reply with quote
Display posts from previous:      
Add To: Del.icio.us  Digg  Google  Spurl  Blink  Furl  Y! MyWeb  
<< View previous topic View next topic >>
Post new topicReply to topic

Jump to 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum