Mysql - How to return data by date in this case?

function phpblock_topMembers()
{

    global $_TABLES, $_CONF;

    /***************************************************************************/
    /*  Set these configuration variables to meet your website's requirements  */
    /***************************************************************************/

    // number of top story contributors to display 
    $top_story_submitters = 10;

    // number of top comment contributors to display
    $top_comment_submitters = 10;

    // list of aliases you do not want shown at all (case insensitive)
    $exclude_list = 'Geeko';

    // title to display above the stories and comments contributor lists 
    $story_title = 'Quem postou mais artigos:';
    $comment_title = 'Quem postou mais comentários:';

    /****************************************************************************/
    /*  Please do not modify the code below unless you know what you are doing  */
    /****************************************************************************/

    // number of database queries this function makes
    $total_queries = 2;

    // initialize display variable
    $display = '';

    // sql calls to pull the data we need
    $sql[1] = "SELECT {$_TABLES['stories']}.uid, {$_TABLES['users']}.username, COUNT(*) AS total FROM {$_TABLES['stories']}, {$_TABLES['users']} WHERE ({$_TABLES['stories']}.draft_flag = 0  AND {$_TABLES['stories']}.uid = {$_TABLES['users']}.uid) GROUP BY {$_TABLES['stories']}.uid  ORDER BY total desc";

    $sql[2] = "SELECT {$_TABLES['comments']}.uid, {$_TABLES['users']}.username, COUNT(*) AS total FROM {$_TABLES['comments']}, {$_TABLES['users']} WHERE {$_TABLES['comments']}.uid = {$_TABLES['users']}.uid GROUP BY {$_TABLES['comments']}.uid ORDER BY total DESC";

    // loop through the code twice
    for ($nbr = 1; $nbr <= $total_queries; $nbr++) {

        // execute the sql call and find the number of rows
        $result = DB_query($sql[$nbr],0);
        $numrows = DB_numRows($result);

        // continue on only if there is data to process
        if ($numrows >= 1) {

            // initialize variables for output
            $namecount = 0;
            $all_done = 0;

            // if nbr = 1 process story submitters, if nbr = 2 process comment submitters
            if ($nbr == 1) {
                $display .= '<strong>' . $story_title . '</strong><br><br>';
                $all_done = $top_story_submitters;
            }
            elseif ($nbr == 2) {
                $display .= '<br /><strong>' . $comment_title . '</strong><br><br>';
                $all_done = $top_comment_submitters;
            }

            // retrieve the info from each database row and assign it to a variable
            for ($count = 0; $count < $numrows; $count++) {
                $row = DB_fetchArray($result);
                $uid = $row['uid'];
                $username = $row['username'];
                $total = $row['total'];

                // bypass those in the exclude list
                if (!stristr($exclude_list,$username)) {
                    $display .= '<a href="' . $_CONF['site_url'] . '/users.php?mode=profile&amp;uid=' . $uid . '">' . $username . '</a> ('.$total.') <br />';
                    $namecount++;

                    // if not done so already, jump out of this loop when it reaches 
                    // the desired number to display
                    if ($namecount >= $all_done)
                        break;
                }  // end if
            }  // end for
        }  // end if
    }  // end for

    // send the output to Geeklog
    return $display;

} // end function phpblock_topMembers

PHP:

Hello, this is a function for geeklog cms (geeklog.net).
Ir returns who post more stories (Articles) and who post more comments.

I want to modify this to return who post more stories in one month, or week...

How do i do that? I was trying something like

date LIKE '%2000-05'

Code (markup):

in $sql[1] but it didn't work.

This is the users table and stories table:

CREATE TABLE `gl_stories` (
  `sid` varchar(40) NOT NULL default '',
  `uid` mediumint(8) NOT NULL default '1',
  `draft_flag` tinyint(3) unsigned default '0',
  `tid` varchar(20) NOT NULL default 'General',
  `date` datetime default NULL,
  `title` varchar(128) default NULL,
  `introtext` text,
  `bodytext` text,
  `hits` mediumint(8) unsigned NOT NULL default '0',
  `numemails` mediumint(8) unsigned NOT NULL default '0',
  `comments` mediumint(8) unsigned NOT NULL default '0',
  `trackbacks` mediumint(8) unsigned NOT NULL default '0',
  `related` text,
  `featured` tinyint(3) unsigned NOT NULL default '0',
  `show_topic_icon` tinyint(1) unsigned NOT NULL default '1',
  `commentcode` tinyint(4) NOT NULL default '0',
  `trackbackcode` tinyint(4) NOT NULL default '0',
  `statuscode` tinyint(4) NOT NULL default '0',
  `expire` datetime NOT NULL default '0000-00-00 00:00:00',
  `postmode` varchar(10) NOT NULL default 'html',
  `advanced_editor_mode` tinyint(1) unsigned default '0',
  `frontpage` tinyint(3) unsigned default '1',
  `in_transit` tinyint(1) unsigned default '0',
  `owner_id` mediumint(8) NOT NULL default '1',
  `group_id` mediumint(8) NOT NULL default '2',
  `perm_owner` tinyint(1) unsigned NOT NULL default '3',
  `perm_group` tinyint(1) unsigned NOT NULL default '3',
  `perm_members` tinyint(1) unsigned NOT NULL default '2',
  `perm_anon` tinyint(1) unsigned NOT NULL default '2',
  `votes` int(11) default NULL,
  PRIMARY KEY  (`sid`),
  KEY `stories_sid` (`sid`),
  KEY `stories_tid` (`tid`),
  KEY `stories_uid` (`uid`),
  KEY `stories_featured` (`featured`),
  KEY `stories_hits` (`hits`),
  KEY `stories_statuscode` (`statuscode`),
  KEY `stories_expire` (`expire`),
  KEY `stories_date` (`date`),
  KEY `stories_frontpage` (`frontpage`),
  KEY `stories_in_transit` (`in_transit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Code (markup):

CREATE TABLE `gl_users` (
  `uid` mediumint(8) NOT NULL auto_increment,
  `username` varchar(16) NOT NULL default '',
  `remoteusername` varchar(60) default NULL,
  `remoteservice` varchar(60) default NULL,
  `fullname` varchar(80) default NULL,
  `passwd` varchar(32) NOT NULL default '',
  `email` varchar(96) default NULL,
  `homepage` varchar(96) default NULL,
  `sig` varchar(160) NOT NULL default '',
  `regdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `photo` varchar(128) default NULL,
  `cookietimeout` int(8) unsigned default '28800',
  `theme` varchar(64) default NULL,
  `language` varchar(64) default NULL,
  `pwrequestid` varchar(16) default NULL,
  `status` smallint(5) unsigned NOT NULL default '1',
  PRIMARY KEY  (`uid`),
  KEY `LOGIN` (`uid`,`passwd`,`username`),
  KEY `users_username` (`username`),
  KEY `users_fullname` (`fullname`),
  KEY `users_email` (`email`),
  KEY `users_passwd` (`passwd`),
  KEY `users_pwrequestid` (`pwrequestid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Code (markup):

I just need to return who posted more stories in a month, i don't need who post more comments.

Hope someone can help me, thanks a lot.

 

Try this:

MONTH(date) = 05 and YEAR(date) = 2000