The First Query In The Message Board Example

[Shoun]

Sir,

The first query in the message board example you used, is having a part:

 

MAX(DATE_FORMAT($last, '%e-%b-%y %l:%i %p')) AS last,

MIN(DATE_FORMAT($first, '%e-%b-%y %l:%i %p')) AS first

 

$first/$last = "CONVERT_TZ(p.posted_on, 'UTC', '{$_SESSION['user_tz']}')";

 

It seems like it should work properly, but it doesn't.

 

For Example, suppose:

posted_on column in the posts table has three dates for a thread(I mean only three posts)

2007-10-29 04:15:52

2007-10-30 04:20:52

2007-11-02 04:30:30

 

your query should return the last one, right? Because it is the last date

but instead the second one is returning. I think the function is working only on the day of the month rather than the whole date.

Can anyone help me with that? How can I through this? I'm stuck. I have been experimenting with this the whole day. Please....

Is there any printed error the query. or is there any other way to do so. I really want to do that 'cause I think showing last reply is important.

[Antonio Conte]

You only need the most recent post? Forget about that code, and do it all in the query

 

SELECT *
FROM table_with_posts
ORDER BY posted_on DESC
LIMIT 1

 

That should produce the latest post in the table according to post date. Same for first post. use ASC instead of DESC in the order by-clause. Replace the astrix (*) with the columns you would like to display, and alter the table name. Then you are ready to go.

 

If you need usernames, etc, a simple join is enough.

 

 

SELECT posts.*, users.*

FROM table_with_posts AS posts
INNER JOIN table_with_users AS users ON (posts.user_id = users.users_id)

ORDER BY posted_on DESC
LIMIT 1

 

 

Edit: 100 posts. Enjoying the moment. Moment's passed. Life goes on.

[Shoun]

Congratulations for you century.

In the message book example Mr. Larry used a complex query. I spent hours to understand it properly

 

Thanks what you wrote in your post. But I want to do it as Book does all in one query,

which is efficient rather that making three of four simple queries. The query was OK and I want all of them as one

not the last reply only as the book does.

 

The example you sent( to Antonio) is totally fine.

But Mr. Larry used MAX(), DATE_FORMAT(), CONVERT_TZ()functions to make it more user friendly.

Problems are there! Did you used those functions?

 

Thanks for your reply, It taught me a new things though.

[Antonio Conte]

Shoun. I tried to sort on datetime column now. Max(date_column) is returning the most recent date. Min(date_column) is returning the oldest date.

 

Date_format is for displaying dates different. You could use European form like (dd.mm.yyyy) or anything you want. It's only for display.

 

CONVERT_TZ() is for timezone conversions. It will allow you to use a GMT datetime and add/subract automaticly to your timezone. Do you store the user's timezone? Else, it's useless for you.

 

This will do what you need:

 

SELECT DATE_FORMAT(MAX(posted_on), '%e-%b-%y %l:%i %p') AS last, DATE_FORMAT(MIN(posted_on), '%e-%b-%y %l:%i %p') AS first
FROM table_with_your_posts

 

I have tested it myself, and it works perfectly with my data. (32 football players with birthdates saved) The problem was that MAX() and MIN() calculates the dates wrong if it's formated BEFORE finding the MAX and MIN. Because the query starts with the most inner function, this would be the right way to do it. If you really need timezone conversions, add it inside the MAX() and MIN()-functions to get it correct.

[Shoun]

Antonio, Thanks a lot for your help. Hey, It's really working now!!!

As you did, I tried to use like that

DATE_FORMAT(MAX(CONVERT_TZ........

Instead of

MAX(DATE_FORMAT(CONVERT_TZ........

It was so nice of you thanks.

[Antonio Conte]

Glad it worked out for you. And good you are determent to get it done "your way". I've also learned something about this.

 

Remember this: If a time function returns null for some unexplainable reason (like with CONVERT_TZ(), ADDTIME(), etc) you should add IF_NULL() inside it. I learned this just this week.