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
querying problem
Moderator: General Moderators
first you will need to find out how the month is formatted
08, 8, Aug, August.....
suppose it's formatted as August
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"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'");
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");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:
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
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'");something like:
Code: Select all
SELECT username, COUNT(*)
FROM phpbb_posts
GROUP BY username, YEAR(FROM_UNIXTIME(phpbb_time)), MONTH(FROM_UNIXTIME(phpbb_time))