Grouping by date

[noginn]

I am writing a small personal web page where I will have articles shown.

The format will be similar to http://www.snowgoon.co.uk
With a date line grouping all the posts for that day.

I have been thinking about how I could do it for some hours now and tryed various methods but can't get my head around it.

I use a MySQL timestamp(14) field for the post date of each article.

Thanks in advance.

[tekky]

I would recommand using an INT field, and storing a UNIX timestamp in it, being they are easily compared via substraction or addition...

then you can simply do a str_to_time("3 september 2004") and get all times that happened between then an whatever that timestamp is + 86400 (I think thats the right value for 1 day...)

[noginn]

Thanks for the reply, it gave me an idea of how to do it with using MySQL timestamps. I have got it to work, sort ot 😉

However at the moment, I have 2 records both posted on the same day but different times. So the way I have grouped by date it will echo a group of 2 records twice, resulting in 4 records, 2 duplicates.

If I didnt explain that very well, heres an example:

2nd April 2004

An article

Another article

---

2nd April 2004

An article

Another article

And here is the code I used to do this.

$dates = new dbconnect();
$dates->query("SELECT DISTINCT date, DATE_FORMAT(date, '%M <B>%D</B> %Y') AS datestamp FROM blog ORDER BY date DESC");
while($date = $dates->fetch_object()) {
	$content .= "<H1>$date->datestamp</H1>\n";
	$blog = new dbconnect();
	$blog->query("SELECT 
				blog.bid, 
				blog.cid, 
				blog.title, 
				blog.blog, 
				DATE_FORMAT(blog.timestamp, '%W %D %M, %H:%i%p') as timestamp,
				users.username,
				users.email
				FROM blog, users
				WHERE blog.uid = users.uid
				AND blog.timestamp LIKE '$date->date%'

			ORDER BY blog.timestamp DESC");
while ($row = $blog->fetch_object()) {
	$comments = new dbconnect();
	$comments->query("SELECT bcid FROM blog_comments WHERE bid = '$row->bid'");
	$content .= "<H2>$row->title</H2>\n";
	$content .= "$row->blog <BR />- <I><A href=\"mailto:$row->email\" title=\"Email $row->username\">$row->username</A></I>\n";
	$content .= "<DIV id=\"footer\">Added $row->timestamp<br>\n";
	$content .= "<A href=\"#\">(".$comments->num_rows().") comments so far</A> - <A href=\"#\">Add a comment </A></DIV>\n";
}
}

I know its something to do with the $dates query, but I am quite new and have no idea how to make MySQL only get 1 record for each 'date' field which is the same. (date field is timestamp(8), so in format YYYYMMDD)

Thanks for any help, and thanks again for getting me started tekky.

[tekky]

thats why i suggested unixtimestamps, you can sort em by time/date whatever as they are all just an incrementing value the later in the day it gets... then you select between 0000 and 2359 for the day you want, and you sort em asc/desc for whichever way you want them displayed, no worrys about duplicate dates, it sorts em by time added regardless

[noginn]

I've sorted it now, possibly a quite crude way of doing so however It works.

I use MySQL timestamp(8) which is in the format YYYYMMDD to store when the article was posted.

I set a variable $currentdate
Then I iterate the results, on each checking whether the output date is == $currentdate, if it is then it will skip returning that group and at the end I will set $currentdate to the last output date. If that makes sense. It works anyway, so I get groups of posts for each date there are ones posted.

Now my next text is to create an archive link set only showing the months where their are posts. Wish me luck! and thanks for theh elp tekky. Much appreciated!

-noginn