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

Question

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

Fyi you are treating userid as a number and a string in this sql, it should be consistent. — Busches
– Busches, Commented Feb 15, 2013 at 13:04
he means your not in () should be not in (1,2,3,4,58,...) not '1','2','3' — genericHCU
– genericHCU, Commented Feb 15, 2013 at 13:19
Also, you have inconsistencies in your BETWEEN clauses, with one being 01 and 1, neither that are quoted. Since you're returning two digits with the subtstring method you'd think it would be '01'. Not sure if that would also be causing you problems. — Busches
– Busches, Commented Feb 15, 2013 at 14:23

genericHCU · Accepted Answer · 2013-02-15 22:18:24Z

6

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(*)

edited Feb 15, 2013 at 22:18

answered Feb 15, 2013 at 12:17

genericHCU

4,4462 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

blarg Over a year ago

Hi Travis, I need to collect the users from the userID table as some users have no meetings booked.

genericHCU Over a year ago

use a left outer join. from users left outer join meeting on users.userID = meeting.userID you need a left join because an inner join would exclude the users that don't have meetings.

Peter Boughton Over a year ago

"I think MySql allows you to order by the alias" - it does. You would need the count(*) (not the alias) in a HAVING clause though.

Busches Over a year ago

Because this is a left outer join, it needs to be apart of the join, otherwise it's going to filter on NULL rows and drop them out, effectively making it an inner join. If you do the filtering on the join, then you still get the benefits of the left outer. [e] To be clear, I'm referring to the meetingCode criteria, userID filtering can and should be done in the where.

Leigh Over a year ago

+1. @Travis - Busches is correct. Regardless of what the from clause says, the current query is performing an implicit inner join due to the filtering.

|

Collectives™ on Stack Overflow

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

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related