Number of message posted in the last 30 days

[drisate]

Hey guys i need to make a script that counts the number of message posted in the last 30 days for a certain groupe in a board.

 

This is how the user table is created

userid, usergroupid, username

 

This is how the post table looks like

postid, threadid, username, userid, dateline

 

The dateline data looks like this: 1257473259

 

I need to loop the user table then count how much message the users from usergroupid 2 posted in the past 30 days.

[Mchl]

SELECT 
  usergroupid, COUNT(*) AS messageCount
FROM 
  user AS u
CROSS JOIN
  post AS p
USING(userid)
WHERE
  dateline > UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY)
  AND u.usergroupid = 2
GROUP BY
  u.usergroupid

[seventheyejosh]

Aw, beat by Mchl

 

Something like:

 

$start=time()-(30*86400);

$group=2;

$query="SELECT COUNT(*) 
              FROM 
              `post_table` 
              INNER JOIN 
              `users` 
              ON 
              `users`.`username`=`post_table`.`username` 
              WHERE 
              `post_table`.`dateline`>'$start' && `users`.`usergroupid`='$group";

 

(untested)

[drisate]

I tryed this but it only returns the first.

 

$select = mysql_query("SELECT usergroupid, userid, COUNT(*) AS message_Count FROM vb_user AS u CROSS JOIN vb_post AS p USING(userid) WHERE dateline > UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY)  AND u.usergroupid = 22 GROUP BY u.usergroupid") or die(mysql_error());
while ($list = mysql_fetch_array($select)) {
echo "$list[userid]<br>";
}

[drisate]

Yup for somereason it only returns the first row i made a print_r and this is what i got

 

Array
(
    [0] => 22
    [usergroupid] => 22
    [1] => 113
    [userid] => 113
    [2] => 11
    [message_Count] => 11
)

 

I know for sure it should return at least 3 rows

[drisate]

More i look at it and more i thing the message_Count is equal to the total messages posted by that groupe in the last 30 days instead of being the number for that user ... could that even be the problem? Is the SQL builded to return the users, nb of post?

 

I need this to return a list

 

BLABLABLA posted X messages

BLABAL posted X messages

BLABALAB posted x messages

[jcbones]

You can return the results of each user, but you would have to have multiple queries, or select the group, and count the users via an array.  Mysql will only return 1 row for a count().

 

$select = mysql_query("SELECT usergroupid, userid, COUNT(*) AS message_Count FROM vb_user AS u CROSS JOIN vb_post AS p USING(userid) WHERE dateline > UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY)  AND u.usergroupid = 22 GROUP BY u.usergroupid") or die(mysql_error());

while ($list = mysql_fetch_array($select)) {

echo "$list[userid]<br>";

}

 

Try:

$select = mysql_query("SELECT usergroupid, userid FROM vb_user AS u CROSS JOIN vb_post AS p USING(userid) WHERE dateline > UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY)  AND u.usergroupid = 22 GROUP BY u.usergroupid") or die(mysql_error());
while ($list = mysql_fetch_array($select)) {
  $store[$list['usergroupid']][$list['userid']] += 1;
}

foreach($store as $k => $v) {
    echo 'User Group: ' . $k . '<br />';
  foreach($v as $kk => $vv) {
     echo $kk . ' has ' . $vv . ' post in the past thirty days.<br />';
  }
}

 

 

[drisate]

thanks for the help :-)

I tryed your code and i get the same result + the number of posted message is wroung

 

$select = mysql_query("SELECT usergroupid, userid FROM vb_user AS u CROSS JOIN vb_post AS p USING(userid) WHERE dateline > UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY)  AND u.usergroupid = 22 GROUP BY u.usergroupid") or die(mysql_error());
while ($list = mysql_fetch_array($select)) {  
    $store[$list['usergroupid']][$list['userid']] += 1;
}

foreach($store as $k => $v) {
    
    foreach($v as $kk => $vv) {     
        
        unset($wuser);
        $wuser = @mysql_fetch_array(@mysql_query("SELECT * FROM vb_user WHERE userid='$kk'"));   
        print ('<tr vAlign="top"><td class="thead" align="left" width="20%"><a href="board/member.php?u='.$wuser[userid].'"><font color="#FFFFFF">'.$wuser[username].'</font></a></td><td class="thead" align="left" width="80%"><font color="#FFFFFF">'.$vv.'</font></td></tr>');

    }
}
  

print ('<tr><td colspan"2"><pre>'); print_r($store); print ('</pre></td></tr>');

 

I get:

only one result out of 3 and the message count is set to one:

Array
(
    [22] => Array
        (
            [113] => 1
        )

)

 

It's weird ...

[drisate]

Oh got it i had to remove the GROUP BY u.usergroupid part

 

THX YOU SO MUCH !!

[Mchl]

To do it in just one query

SELECT
  u.username, COUNT(*) AS messageCount
FROM
  user AS u
CROSS JOIN
  post AS p
USING(userid)
WHERE
  dateline > UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY)
  AND u.usergroupid = 2
GROUP BY
  u.userid