Bob wrote: > > > ----- Original Message ----- > From: "James Keeline" > > --- Bob <[EMAIL PROTECTED] <mailto:helldrivers%40gmail.com>> 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 > > <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.
You need to group your selects together, also it is a good idea to name your tables in the join to keep from ambiguity issues though not mandatory. See below: SELECT *, DATE_FORMAT(h.msg_id,'%W %D %M %Y at %l:%i %p') AS msgdate, DATE_FORMAT(r.reply_date,'%W %D %M %Y at %l:%i %p') AS myreplydate FROM familyhistory h LEFT JOIN familyreplies r ON h.msg_id = r.reply_id ORDER BY msg_id DESC LIMIT $pageoffset, $maxperpage Regards, William Piper
