:: View topic - Top News submitters

Blocks - Top News submitters

wicked - May 24, 2005 - 01:55 AM
Post subject: Top News submitters

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:

$newDayRaw = (time()-(86400 * 7));
$newDB = Date("Y-m-d", $newDayRaw);

$sql = "SELECT $userscolumn[uid], $userscolumn[uname], $userscolumn[counter], $newscolumn[time] FROM $userstable, $newstable AND $newscolumn[time] LIKE '%$newDB%'";

Where 7 is the number of days.

Thanks for your support Smile

Chestnut - May 24, 2005 - 07:03 AM
Post subject: Re: Top News submitters

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:

$newDayRaw = (time()-(86400 * 7));
$newDB = date("Y-m-d H:i:s", $newDayRaw);

$sql = "SELECT $userscolumn[uid],
$userscolumn[uname],
$userscolumn[counter],
$newscolumn[time]
count(*) as ncount,
FROM $userstable,
$newstable
WHERE $usercolumn[uname] = $newscolumn[informant]
AND $newscolumn[time] >= '".pnVarPrepForStore($newDB)."'";

This is to tell the sql that you want the news where the username is like the news informant

Code:

WHERE $usercolumn[uname] = $newscolumn[informant]

And this is to get all what happened since 7 days ago

Code:

AND $newscolumn[time] >= '".pnVarPrepForStore($newDB)."'";

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

Code:

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

Try this and see if it works...