CODEWITHSUNDEEP
mysqlsortingcoldfusion
blarg
blarg

Reputation: 3893

How to sort a table generated from a looped Mysql query in ColdFusion?

I have an HTML table that is generated from a query of the 'user' table that finds all users on a system.

A second query uses 'select count' to count how many meetings a user has booked by checking how many times their userID appears in the meetings table.

<cfquery datasource="iPad" name="Two">
 SELECT COUNT(userID) AS meetingsCount from meeting where userID = '#One.userID#'
 </cfquery>

I would like to be able to reorder the table based on the meetingsCount by clicking a link on the page. The problem is I'm not sure how to query this information as it doesn't technically exist on the table in MYSQL.

Edit; code using left join and validation.

select user.userID, user.contactName, user.email, count(meeting.userID)
as meetingsCount 
 from user where user.userID = 30 
AND user.userID NOT IN ('1', '2', '3', '4', '58', '59', '62',
'63', '64', '66', '69', '71',    '72', '73', '78', '107')
AND SUBSTRING( meeting.meetingCode, 5, 2 ) 
BETWEEN 12 AND 22 
AND SUBSTRING( meeting.meetingCode, 7, 2 ) 
BETWEEN 1 AND 12 
AND SUBSTRING( meeting.meetingCode, 9, 2 ) 
BETWEEN 01 AND 31
left outer join meeting on user.userID = meeting.userID

Upvotes: 0

Views: 149

Answers (1)

genericHCU
genericHCU

Reputation: 4446

You should do this with one query using group by

EDIT: I added your where clause to my answer but instead of hard coding userIDs to exclude you should consider assigning them to a role so you can manage the role in a single place instead of every query that excludes these people. You also don't need the not in if you are asking for a single ID already. I know you're just trouble shooting for now, but keep that in mind. If that isn't working still, I'd check your substring logic.

<cfquery>
    select
          user.userID
        , firstName
        , lastName
        , count(*) as meetingCount
    from
        user
        left outer join meeting on users.userID = meeting.userID
          AND (    SUBSTRING( meeting.meetingCode, 5, 2 ) BETWEEN 12 AND 22 
               AND SUBSTRING( meeting.meetingCode, 7, 2 ) BETWEEN 1 AND 12 
               AND SUBSTRING( meeting.meetingCode, 9, 2 ) BETWEEN 1 AND 31)
      -- the ( ) above are optional but I like them to show groups of like filters
      -- if you don't want to use them feel free not to.
    where 
         user.userID NOT IN (1,2,3,4,5,59,62,63,64,66,69,71,72,73,78,107)     
    group by
          user.userID
        , firstName
        , lastName
    order by
        meetingCount
</cfquery>

I think MySql allows you to order by the alias, I can't remember. if not, change the order by to count(*)

Upvotes: 6

Related Questions