SQL: Select all customers belonging to certain Client Group

[stumblrmonk3]

Hello WHMCS Community!

 

I am trying to generate a report that lists all accounts belonging to a specific Client Group (groupid=3) but for some reason I'm not getting the full list (only getting like 30 or so).

 

I'm assuming my SQL query is wrong:

 

SELECT     DATE_FORMAT(tblhosting.regdate ,'%m/%d/%Y') AS RegDate,
                       tblclients.id, tblclients.firstname, tblclients.lastname, tblproducts.name,
                       tblhosting.orderid, tblcustomfieldsvalues.value,
                       tblhosting.id AS hostingid

            FROM         tblclients, tblhosting, tblproducts, tblcustomfieldsvalues

            WHERE         tblhosting.userid = tblclients.id AND tblcustomfieldsvalues.relid = tblhosting.id AND
                       tblcustomfieldsvalues.fieldid = '2' AND
                        tblclients.groupid = '3' AND tblhosting.packageid = tblproducts.id AND
                       tblhosting.domainstatus='Active'

            ORDER BY     tblhosting.regdate

 

 

I tried to remove all WHERE parameters except for "tblclients.groupid='3'" but that is killing MySQL server and I have to restart MySQL service to be back again.

 

Can someone please help me? What parameters are restricting my resultset to 20-30 (instead of 87, like it should be)?

 

Thanks!

[stumblrmonk3]

I have no idea what was going on. I simplified my SQL query to this:

 

SELECT tblclients.id, tblclients.firstname, tblclients.lastname FROM tblclients WHERE tblclients.groupid = 3 ORDER BY tblclients.id

 

When I was running EXPLAIN on that statement up there, I got the tblhosting table listed as "Using temporary; Using filesort"

 

I think this was making MySQL running out of memory and stuff.

 

Thanks! You may close the ticket.