help fix this error...

[squawell]

first im not know much about code so maybe the code have a little strange....

ok here we go...

i want to show the top poster in last week in my main page so i use this code in index.php


$d= mktime (0,0,0,date("m"),date("d")-7,date("Y"));
$post7day = $DB_site->query_first("SELECT DISTINCT(userid),COUNT(postid) AS posts FROM post WHERE userid > 0 AND post.dateline>=$d GROUP BY userid ORDER BY posts DESC LIMIT 1");
while ($xxx=mysql_fetch_array($post7day)){
$post7dayuser=$DB_site->query_first("SELECT username FROM user WHERE userid=$xxx[userid]");}
eval("\$topthread = \"".gettemplate("top_thread")."\";");


and i creat a template name:top_thread(contant with username and posts) also put the $topthread
in forumhome.....when i goto my main page i found the error message in my header...


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/squawell/public_html/XXX/forum/index.php on line 61


this is line 61
while ($xxx=mysql_fetch_array($post7day)){

want help....thankz......

[Xenon]

$DB_site->query_first never returns a mysql result, it returns a normal php array.

remove the _first to get it working..

edit: just the query before the while loop is meant ;)

[squawell]

Today at 07:36 PM Xenon said this in Post #2 (https://vborg.vbsupport.ru/showthread.php?postid=395700#post395700)
$DB_site->query_first never returns a mysql result, it returns a normal php array.

remove the _first to get it working..

edit: just the query before the while loop is meant ;)
thankz Xenon u always give me big help ...now the error is gone.....:D:D:D

but it only show up usernamethe post number did not below is the template in top_thread


<a href=member.php?s=$session[sessionhash]&action=getinfo&userid=$xxx[userid]>$post7dayuser[username]</a><BR>$post7dayuser[posts]


do i use wrong variable??thankz agian.....

[Xenon]

$post7dayuser=$DB_site->query_first("SELECT username FROM user WHERE userid=$xxx[userid]");}

you jhust read out username, not posts here ;)

[squawell]

Today at 08:08 PM Xenon said this in Post #4 (https://vborg.vbsupport.ru/showthread.php?postid=395704#post395704)
$post7dayuser=$DB_site->query_first("SELECT username FROM user WHERE userid=$xxx[userid]");}

you jhust read out username, not posts here ;)
oh.....how should i change my code to show them both???

[Xenon]

$d = mktime (0,0,0,date("m"),date("d")-7,date("Y"));
$post7day = $DB_site->query_first("
SELECT DISTINCT(post.userid), COUNT(postid) AS posts, user.username
FROM post
LEFT JOIN user USING(userid)
WHERE post.userid > 0 AND post.dateline>=$d
GROUP BY post.userid
ORDER BY posts DESC
LIMIT 1
");
eval("\$topthread = \"".gettemplate("top_thread")."\";");


template:
<a href=member.php?s=$session[sessionhash]&action=getinfo&userid=$xxx[userid]>$post7day[username]</a><BR>$post7day[posts]

[squawell]

Today at 06:47 AM Xenon said this in Post #6 (https://vborg.vbsupport.ru/showthread.php?postid=395897#post395897)
$d = mktime (0,0,0,date("m"),date("d")-7,date("Y"));
$post7day = $DB_site->query_first("
SELECT DISTINCT(post.userid), COUNT(postid) AS posts, user.username
FROM post
LEFT JOIN user USING(userid)
WHERE post.userid > 0 AND post.dateline>=$d
GROUP BY post.userid
ORDER BY posts DESC
LIMIT 1
");
eval("\$topthread = \"".gettemplate("top_thread")."\";");


template:
<a href=member.php?s=$session[sessionhash]&action=getinfo&userid=$xxx[userid]>$post7day[username]</a><BR>$post7day[posts]
thankz Xenon...it works!!!

another question...if i want another code also use in same template should i put the code above this

eval("\$topthread = \"".gettemplate("top_thread")."\";");

am i right?

[Boofo]

Xenon, how would you do this on a per forum type basis?

[Xenon]

@squawell: yes right, every variabole appearing in the template has to be before that eval!

@boofo:

it would slow down the query but i think this should do the trick:

$post7day = $DB_site->query_first("
SELECT DISTINCT(post.userid), COUNT(postid) AS posts, user.username
FROM post
LEFT JOIN user USING(userid)
LEFT JOIN thread USING(threadid)
WHERE post.userid > 0 AND post.dateline>=$d AND thread.forumid=xx
GROUP BY post.userid
ORDER BY posts DESC
LIMIT 1
");

[squawell]

Today at 07:08 PM Xenon said this in Post #9 (https://vborg.vbsupport.ru/showthread.php?postid=396089#post396089)
@squawell: yes right, every variabole appearing in the template has to be before that eval!

Thankz reply..Xenon :) :)

above ur code it will affect when u apply the code right?and the

post number will recount after 7 days later is that right too?? :)

[Xenon]

above ur code it will affect when u apply the code right?
huh? what do you mean?

no the postnumber won't recount every 7 days, it will always be dynamical for the last seven days, so every day you'll see another value

[squawell]

Today at 08:16 PM Xenon said this in Post #11 (https://vborg.vbsupport.ru/showthread.php?postid=396113#post396113)
huh? what do you mean?

no the postnumber won't recount every 7 days, it will always be dynamical for the last seven days, so every day you'll see another value
i mean i use ur code it will affect when i apply it right...

so it cant count last 7 days the most posts user and change the stats every 7days later.....

[Xenon]

yes the code will work right.

nope every new day the value will be recalculated.
it will not have weekly stats but stats the last 7 days

[squawell]

05-17-03 at 09:52 PM Xenon said this in Post #13 (https://vborg.vbsupport.ru/showthread.php?postid=396139#post396139)
yes the code will work right.

nope every new day the value will be recalculated.
it will not have weekly stats but stats the last 7 days
so Xenon the variable mean last 7 days

$d = mktime (0,0,0,date("m"),date("d")-7,date("Y"));

if i want last 30 days should i change like this

$d = mktime (0,0,0,date("m"),date("d")-30,date("Y"));

is that right?

[Xenon]

exactly

[Boofo]

05-17-03 at 06:08 AM Xenon said this in Post #9 (https://vborg.vbsupport.ru/showthread.php?postid=396089#post396089)
@squawell: yes right, every variabole appearing in the template has to be before that eval!

@boofo:

it would slow down the query but i think this should do the trick:

$post7day = $DB_site->query_first("
SELECT DISTINCT(post.userid), COUNT(postid) AS posts, user.username
FROM post
LEFT JOIN user USING(userid)
LEFT JOIN thread USING(threadid)
WHERE post.userid > 0 AND post.dateline>=$d AND thread.forumid=xx
GROUP BY post.userid
ORDER BY posts DESC
LIMIT 1
");

I tried this code and this is the error I got.

Database error in vBulletin 2.2.9:

Invalid SQL: SELECT DISTINCT(post.userid), COUNT(postid) AS posts, user.username
FROM post
LEFT JOIN user USING(userid)
LEFT JOIN thread USING(threadid)
WHERE post.userid > 0 AND post.dateline>=1052895600 AND thread.forumid=34
GROUP BY post.userid
ORDER BY posts DESC
LIMIT 1

mysql error: Unknown column 'user.threadid' in 'on clause'

mysql error number: 1054

Date: Wednesday 21st of May 2003 06:31:41 AM
Script: http://www.bearfacts2.com/forum/forum/forumdisplay.php?forumid=34
Referer: http://www.bearfacts2.com/forum/
Repair Report: This type of error cannot be automatically repaired.

[Xenon]

hmm than that way:

$post7day = $DB_site->query_first("
SELECT DISTINCT(post.userid), COUNT(postid) AS posts, user.username
FROM post
LEFT JOIN user USING(userid)
LEFT JOIN thread ON(post.threadid=thread.threadid)
WHERE post.userid > 0 AND post.dateline>=$d AND thread.forumid=xx
GROUP BY post.userid
ORDER BY posts DESC
LIMIT 1
");

[Boofo]

That ran without any errors but it wouldn't show the stats.

[squawell]

Yesterday at 10:58 PM Boofo said this in Post #18 (https://vborg.vbsupport.ru/showthread.php?postid=398089#post398089)
That ran without any errors but it wouldn't show the stats.
that code i use in index.php not forumdisplay.php......

and it works..................... :D

[Boofo]

But it also should work in the forumdisplay.