----- Original Message ----- From: "William Piper" > Bob wrote: <snipped some message here> >> 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
Hi William, That did the trick. I take your point about naming tables to stop clashes in a join. I deliberately made all items different in an attempt to eliminate this, as I was struggling with it. As you say, it's not mandatory if no conflicting names, but good practice. Hadn't come across using a table alias (as your example) but just found it in my books. Saves a lot of typing. I'm trying to learn the correct way, so I will leave the table alias in. Thanks, Bob.
