Sorting on user-defined "Status" field but for ALL products, SQL query needed?

[trappedatuf]

I already posted this as a ticket to Matt, and I'm going to put his reply in... but I though to post it here in the forums so maybe someone can help with the SQL query needed.

 

I created an user-defined field drop-down box on all my Hosting service accounts called "Status". This contains options like "In Design", "On Hold", "Complete"... etc.

 

Now, I want to see all the accounts that are "In Design", but there is a problem! If I go to "List Hosting Accounts" and click on "Search/Filter" then I can sort on "Custom Field"... BUT... it segments the status by account type. For example I have a lot of different types of accounts such as "Website Express", "Website Pro", "Ecommerce", etc... so the sort is showing "Website Pro - Status", "Website Express - Status"... etc.

 

Now this doesn't help me... how can I sort on just the "Status" field regardless of account type.

[trappedatuf]

Here's Matt's response:

 

It's not possible to do that as each custom field is a seperate entity in the database

 

Regards,

Matt

[trappedatuf]

And now this is my main follow-up question... maybe someone here can help with the SQL query needed to find this info:

 

Does this just pull data from the database via an SQL query? Why can't we edit the query to use a JOIN statement and have it join all the status fields for all the different type of products. I'm no expert on SQL but I think if I knew where to look in the database for this info I could come up with something. Can you let me know where this data is held and how that function gets it's data from the DB then I'm sure we could come up with a solution... this is really CRUCIAL for me to be able to easily see the status of all my accounts so I can see which ones are finished and more importantly which clients are still in the design phase.

 

Thanks,

Dave

[openmind]

I'm not sure on the query but it does sound to me like you would better linking whmcs into some kind of CRM software to track the projects maybe?

[trappedatuf]

Not really ... all I need is te simplistic view of what status all the hosting accounts have. The current search view that WHMCS has would work great because all I want to do is quickly sort on the Status field to see how many projects are "In Design" at the current moment and which one's those are.

 

So really all I need is an SQL query that will do what is currently done when you search one "Hosting Plan - Status" with all the others so you can view everything at once instead of piecemeal.

 

Matt... what is the current SQL query used? Maybe if I had that I can figure out the JOINS myself?

[trappedatuf]

Ok, so Matt was kind enough to let me know the following crucial info:

 

The fields are stored in tblcustomfields and the values of those fields in tblcustomfieldvalues. Those then relate to the product in tblhosting based on relid=id. So the join would need to link to tblhosting, tblcustomfieldvalues and tblcustomfields and then the query select based on multiple field IDs matching the search crtieria.

 

So then I did some testing to try and create a SQL query that provided me with the results I required. So I started with the most simplistic statement and built upon that. I'm going to outline the steps so you guys can follow how I came up with the final query and then comment on how/if you would have done it differently/more efficiently.

 

1. This statement identifies only the custom fields called 'Status':

SELECT * FROM `tblcustomfields` WHERE `tblcustomfields`.`fieldname` LIKE 'Status'

 

2. From that I see what those field's ID numbers are and I can use that to relate it to the `tblcustomfieldsvalues` table. So I added a (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id) to that statement and get:

SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`)

 

3. That gives me all the 'Status' fields correlated with the `value` of that status field (value being Design Complete, In Design, etc -- which is what I am looking for). So now I must find out, out of that list, which one's have the value of 'In Design'.

SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%')

 

4. Now I must use the `tblcustomfieldsvalues`.`relid` field and relate that to `tblhosting`.`id` so I can see what hosting accounts those actually are.

SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues`, `tblhosting` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%') && (`tblhosting`.`id` = `tblcustomfieldsvalues`.`relid`)

 

5. And last but not least I want to ignore all but "Active' clients in my query so I put that in:

SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues`, `tblhosting` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%') && (`tblhosting`.`id` = `tblcustomfieldsvalues`.`relid`) && (`tblhosting`.`domainstatus` = 'Active')

 

6. Now to only show the hosting account's domain name and not all fields so I am not given unneeded info:

SELECT `tblhosting`.`domain` FROM `tblcustomfields`, `tblcustomfieldsvalues`, `tblhosting` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%') && (`tblhosting`.`id` = `tblcustomfieldsvalues`.`relid`) && (`tblhosting`.`domainstatus` = 'Active')

 

Ok, so that final query gives me the output I want. Feel free to run these queries on any custom fields you may have that are the same through all your different hosting products.