Topic: Top News submitters
wicked

Noob
Noob
Posts: 11

Posted:
May 24, 2005

I try to create a block that shows the top news submitters on the last [period time] and it doesn't show anything :S

This is the sql that i execute...

Code
  1. $newDayRaw = (time()-(86400 * 7));
  2. $newDB = Date("Y-m-d", $newDayRaw);
  3.  
  4. $sql  = "SELECT $userscolumn[uid], $userscolumn[uname], $userscolumn[counter], $newscolumn[time] FROM $userstable, $newstable AND $newscolumn[time] LIKE '%$newDB%'";
  5.  


Where 7 is the number of days.

Thanks for your support :)
Chestnut
avatar
Site Admin
Posts: 1320

Posted:
May 24, 2005

I'm not expert of this sort of things but from what I see... you are asking in your sql what news is LIKE 7days before and ... think I see another error. You are missing a where...

Think something like this would be more appropriate :

Code
  1. $newDayRaw = (time()-(86400 * 7));
  2. $newDB     = date("Y-m-d H:i:s", $newDayRaw);
  3.  
  4. $sql  = "SELECT $userscolumn[uid],
  5.                 $userscolumn[uname],
  6.                 $userscolumn[counter],
  7.                 $newscolumn[time]
  8.                 count(*) as ncount,
  9.          FROM   $userstable,
  10.                 $newstable
  11.          WHERE  $usercolumn[uname] = $newscolumn[informant]
  12.          AND    $newscolumn[time] >= '".pnVarPrepForStore($newDB)."'";
  13.  


This is to tell the sql that you want the news where the username is like the news informant
Code
  1. WHERE  $usercolumn[uname] = $newscolumn[informant]
  2.  




And this is to get all what happened since 7 days ago
Code
  1. AND    $newscolumn[time] >= '".pnVarPrepForStore($newDB)."'";
  2.  


The number of news for each user will be given in the "ncount" later in your code.

Code
  1. list($uid, $uname, $newscounter, $newstime, $ncount) = $result->fields;
  2.  


Try this and see if it works...
Chestnut ! Cool
Site Admin