need help with vB variables or queries regarding new posts/daily posts

[Slapyo]

i was wondering if there was a built in variable for vB that would tell you how many new posts there have been since you last visit, and how many posts there have been that day.

View New Posts (234) | Today's Posts (234)

i want something like that. now if there is a variable like $bbuserinfo or $pmbox that could give me this information that would be great. if not, could someone give me the 2 queries i would use to gather this information.

[Xenon]

nope, there isn't such a variable, as it would produce extra queries which should be avoided

the sql would be:

SELECT COUNT(postid) AS newposts FROM post WHERE dateline > $bbuserinfo[lastvisit]

[Slapyo]

ok cool, what about how to select for daily threads.

i'm not worried about extra queries because it is going on a non-vb page that only has a few so it won't be adding anything extra to the message board.

[Slapyo]

i got an error when i ran this:


$countnewthreads = $DB_site->query_first('SELECT COUNT(postid) AS newposts FROM post WHERE dateline > $bbuserinfo[lastvisit]');
$totalnewthreads = number_format($countnewthreads['newposts']);

Invalid SQL: SELECT COUNT(postid) AS newposts FROM post WHERE dateline > $bbuserinfo[lastvisit]
mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '[lastvisit]' at line 1

mysql error number: 1064

[Xenon]

use doublequotes instead of single quotes and it will work :)

[Slapyo]

ok that worked thanks. now what do i change in the query to make it so it gives the count for the getdaily search?

the reason i ask is i think from looking at the database the times are stored in unix_timestamp format. but i don't think php has a unix_timestamp function, just mysql.

$countnewthreads = $DB_site->query_first('SELECT COUNT(postid) AS newposts FROM post WHERE dateline > $unix_timestamp');
$totalnewthreads = number_format($countnewthreads['newposts']);

i know that the ending of the query would be the only difference. my problem is being able to give the unix timestamp for today. like september 9 2004 0:0:0am ... just not sure how to go about that.

[Slapyo]

i think i might have it. lemme know if this is correct:

$todaysdate = date("d F Y");
$timetoday = strtotime($todaysdate);
$countdailythreads = $DB_site->query_first("SELECT COUNT(postid) AS dailyposts FROM post WHERE dateline > $timetoday");
$totaldailythreads = number_format($countdailythreads['dailyposts']);

[Xenon]

you can use the mktime() function ;)