Page 1 of 1
querying problem
Posted: Fri Aug 05, 2005 2:41 am
by zyberjock
I hope this is not off topic but I have a problem with this.
I am using the phpbb_posts table from the phpbb database
my question is how can i query the post_time table to get a result that displays a per month basis for example i want to get only the posts for july.
thanks
Posted: Fri Aug 05, 2005 3:10 am
by s.dot
first you will need to find out how the month is formatted
08, 8, Aug, August.....
suppose it's formatted as August
Code: Select all
$result = mysql_query("SELECT * FROM phpbb_posts WHERE monthcolumn = 'August'");
Posted: Fri Aug 05, 2005 3:14 am
by zyberjock
scrotaye wrote:first you will need to find out how the month is formatted
08, 8, Aug, August.....
suppose it's formatted as August
Code: Select all
$result = mysql_query("SELECT * FROM phpbb_posts WHERE monthcolumn = 'August'");
post_time in phpbb is formatted as a whole number like 1115883987, how can i produce a result such that i can extract only the month in the date format stored in the database like "August"
Posted: Fri Aug 05, 2005 3:19 am
by zyberjock
here is my query but it only displays all the posts what i want is to display the total posts of each users on a per month basis
Code: Select all
$result = mysql_query("select phpbb_users.username, count(phpbb_posts.post_id) as user_posts from phpbb_posts inner join phpbb_users on phpbb_users.user_id=phpbb_posts.poster_id group by phpbb_users.username order by user_posts");
Posted: Fri Aug 05, 2005 3:25 am
by s.dot
That appears to be a unix timestamps which is measured in seconds since the unix epoch.
There may be a function to convert timestamps to date formats but I'm not aware of it.
The only way I could suggest to do this is to find out the seconds that contstituted the month of july.
Perhaps something like:
Code: Select all
$startdate = strtotime("July 01, 2005");
$enddate = strtotime("July 31, 2005");
$result = mysql_query("SELECT * FROM phpbb_posts WHERE phpbb_time > '$startdate' AND phpbb_time < '$enddate'");
Edit: to get even more specific you could specify the $startdate as 12:00:00 AM July 01, 2005 and the end date as 11:59:59 PM July 31, 2005
Posted: Fri Aug 05, 2005 7:50 am
by timvw
something like:
Code: Select all
SELECT username, COUNT(*)
FROM phpbb_posts
GROUP BY username, YEAR(FROM_UNIXTIME(phpbb_time)), MONTH(FROM_UNIXTIME(phpbb_time))