----- Original Message ----- From: "James Keeline" > --- 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.
Hi James, The msg_id, reply_id and reply_date where in the format (20080129173937) and DATE_FORMAT() is working fine. I have set them all to DATETIME (2008-02-24 19:46:30) now though, as you recommend. The 1st DATE_FORMAT() is working fine. My trouble is when I try to add the 2nd DATE_FORMAT(): No matter where I add it, the query fails. Tried it in different places etc. $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 DATE_FORMAT(reply_date,'%W %D %M %Y at %l:%i %p') AS myreplydate ORDER BY msg_id DESC LIMIT $pageoffset, $maxperpage "; If I take the 2nd DATE_FORMAT() out, it excepts the query, but obviously the date isn't formatted. Is there a comma or something missing from the 2nd DATE_FORMAT() for the LEFT JOIN, or have I got it in the wrong place? Regards, Bob.
