oops, I stand corrected. apologies proxi. Guess what i am going to be re-reading for the next while.
create table profile(
id int unsigned not null primary key auto_increment,
fname varchar(100) not null,
lname varchar(100) not null,
email varchar(200) not null,
p_link varchar(200) not null,
constraint `uq_profile_email` unique(email)
)engine = innodb;
create table news(
id int unsigned not null primary key auto_increment,
profile_id int unsigned not null,
title varchar(200) not null,
message text not null,
created_date timestamp not null default current_timestamp,
constraint `fk_news_profile_id` foreign key (profile_id) references profile (id)
) engine = innodb;
The data for news table is for internal users to post about anything. Like a blog. There is no limit in the amount of posts they can make. So if a user posts 5 “news” posts, I would not like the user to repeat from the query. Only distinct user posts.
This also leads me to my next question. i’ve been trying to write up a query in order to show a count of how many news posts have been posted. Ideally I would like to count based on an interval.
The table would look something like this:
id - profile_id - title - message - created_date
1:1340:“I love this beer!”:“…etc…”:“2011-01-20 01:25:09”
2:936:“New inc”:“…etc…”:“2011-01-20 01:25:12”
3:38:“School restarts”:“…etc…”:“2011-01-20 01:25:20”
4:7352:“Advancing”:“…etc…”:“2011-01-20 01:25:30”
5:1340:“I love this beer!”:“…etc…”:“2011-01-20 01:26:01”
6:1340:“Oh… here we go again ;)”:“…etc…”:“2011-01-20 01:26:16”
If you notice the pattern in the time stamp. There are on an average 5 posts per minute. I would like to group the count of those posts within that minute.
The query in this case should return 2 rows. First row with a count of 4 and the second row of count 2.
If anyone could point me to the right direction I would appreciate it
You can scratch my second question. I got it working properly.