querying problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
zyberjock
Forum Newbie
Posts: 6
Joined: Mon Nov 10, 2003 2:13 am

querying problem

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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'");
zyberjock
Forum Newbie
Posts: 6
Joined: Mon Nov 10, 2003 2:13 am

Post 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"
zyberjock
Forum Newbie
Posts: 6
Joined: Mon Nov 10, 2003 2:13 am

Post 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");
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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))
Post Reply