Problem with creating own module Block with an SQL query

Question

I’m trying to create a custom module that implements a block to display something I haven’t been able to do with the Views 2 module.
I’ve tried to look at some guides and tutorials, but I haven’t got any result yet.

I can print out the first SELECT attribute in the first row. The first row make sense since I don’t loop through it but still I’m confused on how to do the rest.

function sn_most_contributed_block($op='list', $delta=0) {
  if ($op == "list") {
    // Generate listing of blocks from this module, for the admin/block page
    $block = array();
    $block[0]["info"] = t('Most Contribution this week (custom module');
    return $block;
  } 
  elseif ($op == 'view') {    
    $mce = db_result(
      db_query("SELECT node.uid, COUNT(nid), users.picture AS users_picture, users.uid AS users_uid, users.name AS users_name, users.mail AS users_mail FROM node node INNER JOIN users users ON node.uid = users.uid WHERE (node.type IN ('leads',  'qa',  'qa_comments',  'til_salg',  'tips_tricks')) AND node.created >= UNIX_TIMESTAMP( CURDATE( ) - INTERVAL 1 WEEK ) AND node.created <= UNIX_TIMESTAMP( CURDATE( ) + INTERVAL 1 DAY ) GROUP BY uid ORDER BY COUNT(nid) DESC LIMIT 0 , 5")
    );

    $block_content .= "Most contributed members are: " . $mce';
    $block_content .= "<div class='author picture profile_image'>In here should be the user image</div>";
    $block_content .= "<div id='username'>In here should be the username</div>";

    if ($block_content == '') {   
      $block['subject'] = 'contribution test';
      $block['content'] = 'yay';
      return $block;
    }
    else {
      // set up the block  
      $block = array();
      $block['subject'] = 'Most contribution this week';  
      $block['content'] = $block_content;
      return $block;
    }
  }
}

I’ve tested my query in the database environment for my Drupal site and it works and print everything it should out correctly.

For looping the query, I tried this code with no result.

while ($contriUser = db_fetch_object($mce)) {
  $contriUser [] = $contriUser->uid;
}

I have also tried the following code.

while ($contriUser = db_fetch_object($mce)) {
  var_dump($contriUser);
}

Any help on how to solve this or if someone has an alternative solution to get the most contributing members last seven days it would be much appreciated.

Answer 1

For most contributing members you may want to look at the user points modules, they give you a lot of very nice features.

A couple of quick pointers with your code as you are asking.

Your query should use parameters and be formatted according to drupal coding standards, this helps for security and portability. The coder module can help you with this.

To get the results you will probably want something like db_fetch_array in a loop

 $result_set = db_query("SELECT ...");
 while ($result = db_fetch_array($result_set)) {
   $block_content .= ...;
 }

I hope this helps a little.

Answer 2

I made some changes to the interesting part. Check out this untested code:

$block_content = "";
$result_set = db_query("SELECT node.uid, 
  COUNT(nid), 
  users.picture AS users_picture, 
  users.uid AS users_uid, 
  users.name AS users_name, 
  users.mail AS users_mail 
  FROM {node} 
  INNER JOIN {users} ON node.uid = users.uid 
  WHERE (node.type IN ('leads',  'qa',  'qa_comments',  'til_salg',  'tips_tricks')) 
  AND node.created >= UNIX_TIMESTAMP( CURDATE( ) - INTERVAL 1 WEEK ) 
  AND node.created <= UNIX_TIMESTAMP( CURDATE( ) + INTERVAL 1 DAY ) 
  GROUP BY uid 
  ORDER BY COUNT( nid ) DESC 
  LIMIT 0 , 5"
);

$block_content .= t("Most contributed members are: ");
while ($result = db_fetch_array($result_set)) {
  $block_content .= '<div class="author picture profile_image"><img src="'.$result['users_picture'].'"/></div>';      
  $block_content .= '<div id="username">'.$result['users_name'].'</div>';
}

Answer 3

If you have views 3, dereine has kindly posted an import for this http://drupal.org/node/1186598#comment-4593972