If posted today (should work right?)

[V]

I'm trying to display a message next to a category that has a new post.

 

The code I'm using seems pretty logical to me but I'm not sure why it isn't working. It tells how many posts are in a topic/category and alongside with that I want to check if the post date (that uses datetime in MYSQL) is equal to the current date. If it is, show a "new post today!" message.

 

$sql = "SELECT *, COUNT(post_title) as total_posts FROM posts WHERE topic_id = {$row["topic_id"]}";				
$posts_result = $connection->query($sql) or die(mysqli_error($connection));

while ($row = $posts_result->fetch_assoc()) {	

	$today = date("F j, Y");
	$post_date=$row['post_date'];				

		//find out how many posts are in topic
		echo "(";
		echo $row['total_posts'];
		echo ")";

			//new post alert
			if ($post_date==$today) {
				echo "new post today!<br />";
			}

}//close Posts loop

 

[ram4nd]

Use foreach. Use single quotes instead of doubles. Seems that $today and $post_date are not equal. Try to echo them both, see what they are. They have to match exactly char by char.

[bobby317]

I think you will need to convert your phpdate to a unix timestamp and then use the FROM_UNIXTIME() function in your quary to convert it to the DATETIME format and then use the UNIX_TIMESTAMP() function in your select query to convert it back and then work with it that way.

 

This artical may help it helped me.

 

http://www.richardlord.net/blog/dates-in-php-and-mysql

[ram4nd]

Just show us an example of post_date. Is it a timestamp or something else.

[V]

@ram4nd I echoed the two. For today's date I get

 

June 19, 2010

 

and post date

 

2010-06-19 14:37:54

 

also, it's not picking the date for the most recent post within the topic/category. I used ORDER BY the most recent post in the query like this

 

$sql = "SELECT *, COUNT(post_title) as total_posts FROM posts WHERE topic_id = {$row["topic_id"]} ORDER BY post_date desc";	

 

but when echoing the post title, it shows me an older post.. I think I need the most recent post to compare the dates

 

@ bobby317 Thanks for the link! I'll try and figure out the right settings for my situation.

 

 

[V]

Oops sorry for the double post!

[ram4nd]

Then you probably want the date part only from the database time. Put the database time in the second parameter of date, first parameter the same and you should be good to go.

[V]

Then you probably want the date part only from the database time. Put the database time in the second parameter of date, first parameter the same and you should be good to go.

Hmm I'm not sure how you mean

[ram4nd]

Change:

$today = date("F j, Y");
$post_date=$row['post_date'];

 

to:

$today = date('Y-m-d');
$post_date = substr($row['post_date'], 0, -9);

[V]

ram4nd that's awesome! Thanks. I have both dates formatted the same now but it's still picking up the oldest post under each category. So instead of

 

ORDER BY post_date DESC

 

I used

 

GROUP BY post_date DESC

 

but now however it displays all the post dates,  :-\ I need just the latest. Should I use a limit for the GROUP BY code?

 

[ram4nd]

Use order by and Limit, with limit you can say how many rows you take. Groups are other things.

[V]

Thanks ram4nd!