help fix this error...

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

PHP Code:

$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]

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
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
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
@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:

PHP Code:

$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/foru...php?forumid=34
Referer: http://www.bearfacts2.com/forum/
Repair Report: This type of error cannot be automatically repaired.

[Xenon]

hmm than that way:

PHP Code:

$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
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.....................

[Boofo]

But it also should work in the forumdisplay.