[thesite] Fw: Someone is talking...

[rudy]

> And I mentioned that if you make this one query you lose
> the ability to make the author's email different than someone
> who just commented on an article.

um, not unless you use a clever CFIF    =o)

here are the two queries --

 <cfquery name="getarticleauthor" datasource="#data#">
  SELECT
   users.userid,
   users.email,
   users.notify,
   users.firstname
  FROM
   users
  WHERE
   users.userid = #Val(form.bid)#
 </cfquery>

 <cfquery name="getauthor" datasource="#data#">
  SELECT DISTINCT
   users.userid,
   users.email,
   users.notify,
   users.firstname,
   replies.contentid,
   replies.userid
  FROM
   users,
   replies
  WHERE
   users.userid = replies.userid
  AND replies.contentid = #Val(form.aid)#
  AND users.userid != #Val(form.uid)#
 </cfquery>

note that in the second query, the user who is making the comment is
excluded (neat)

having looked at the query a little closer, i can see that we won't be
needing UNION (which involves a sort) and can instead use UNION ALL

here's the combined query --

 <cfquery name="getboth" datasource="#data#">
  SELECT
   users.userid,
   users.email,
   users.notify,
   users.firstname,
   0 as contentid,
   0 as commenter
  FROM
   users
  WHERE
   users.userid = #Val(form.bid)#
UNION ALL
  SELECT DISTINCT
   users.userid,
   users.email,
   users.notify,
   users.firstname,
   replies.contentid,
   replies.userid
  FROM
   users,
   replies
  WHERE
   users.userid = replies.userid
  AND replies.contentid = #Val(form.aid)#
  AND users.userid != #Val(form.uid)#
  AND users.userid != #Val(form.bid)#
 </cfquery>

the 2nd subselect now conveniently excludes the author (last line)

i suppose this means we can also just insert this one line to fix the
original problem, eh?  but where's the fun in that?    =o)

okay, carrying on...

note that UNIONs demand the same number and datatype of columns in each
subselect, hence the addition of two extra columns in the first query,
conveniently set to 0

now, when sending the mail, you can detect the author by those same 0's!!

   #session.user.name# has commented on an article
    <cfif getboth.commenter is 0>you wrote
      <cfelse>you commented on</cfelse>

a couple observations about the original 2nd query --

can't figure out why the contentid is being returned, it's not needed -- it
is in the where clause, but that doesn't mean it has to be selected

also, watch out for two identically named columns in the select list, i'm
surprised CF lets you get away with that, because how would you cfoutput
the 2nd occurrence of userid?


rudy