--- Bob <[EMAIL PROTECTED]> wrote: > The following is working, but it's the first time I've used a table join, so > I may have it all wrong. > > CREATE TABLE `familyhistory` ( > `msg_id` bigint(14) unsigned NOT NULL default '0', > `name` varchar(50) NOT NULL default '', > `family` varchar(50) NOT NULL default '', > `message` text NOT NULL, > PRIMARY KEY (`msg_id`), > ) TYPE=MyISAM; > > CREATE TABLE `familyreplies` ( > `reply_id` bigint(14) unsigned NOT NULL default '0', > `reply_date` bigint(14) unsigned NOT NULL default '0', > `reply_name` varchar(50) NOT NULL default '', > `reply_message` text NOT NULL, > PRIMARY KEY (`reply_id`), > ) TYPE=MyISAM; > > My query statement: > $sql = > " > SELECT *, > DATE_FORMAT(msg_id,'%W %D %M %Y at %l:%i %p') AS msgdate > FROM familyhistory > LEFT JOIN familyreplies ON msg_id = reply_id > ORDER BY msg_id DESC > LIMIT $pageoffset, $maxperpage > "; > > msg_id, reply_id and reply_date are TIMESTAMPs. > There are no conflicting names in either table, so I haven't used (table dot > var) format. > > How can I include this in my query: > DATE_FORMAT(reply_date,'%W %D %M %Y at %l:%i %p') AS reply_date > for the LEFT JOIN? > > I read that it's more efficient to let MySql do the work, and not to pull the > data and format it yourself. > Thanks, Bob.
Your data type for reply_date is an integer. I guess you intend to store the number of seconds since the epoch (ie 1 Jan 1970 00:00:00)? This is also known as unixtime in MySQL and there is a function called from_unixtime() http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime which will convert the integer value to a date string. For my money I'd use a datetime datatype because it allows a full range of dates from the year 1000 to 9999 (ie must be 4 digits) and it works more easily with date_format() and other date-time functions in MySQL. You can always convert to unixtime if you need to do a comparison for a post 1969 date. James
