The real problem I'm trying to solve, is how to get all the clients from WHMCS database with all their custom fields. I thought I could use a query in phpmyadmin in order to finally get the result in a csv.
Short explanation of database scheme structure: WHMCS, has two extra tables (tblCustomFields and tblCustomFieldsValues) to facilitate custom fields created by the application users. For every new custom field created, the name and its id are stored in tblCustomFields. When a custom field in a client's profile is filled, the value is stored with the id of the client and the id of the field name in a row of tblCustomFieldsValues.
I'll try to describe a simplified generic version of the question which may apply to many similar situations.
I have three tables:
+----------------------------------------------------+
| tblClients |
+----------------------------------------------------+
| id | company | standard_field1 | standard_field2 |
+----+-----------+-----------------+-----------------+
| 1 | A company | abc | yz |
+----+-----------+-----------------+-----------------+
| 2 | B company | xyz | foo |
+----+-----------+-----------------+-----------------+
| 3 | C company | zyx | bar |
+----+-----------+-----------------+-----------------+
+--------------------------+
| tblCustomFields |
+--------------------------+
| id | fieldname |
+----+---------------------+
| 1 | Custom Field Name 1 |
+----+---------------------+
| 2 | Custom Field Name 2 |
+----+---------------------+
| 3 | Custom Field Name 3 |
+----+---------------------+
+----------------------------------------------------+
| tblCustomFieldsValues |
+----------------------------------------------------+
| relid | fieldid | value |
| (corresponds to | (corresponds to | |
| a client id) | a custom field id) | |
+-----------------+--------------------+-------------+
| 1 | 1 | any value |
+-----------------+--------------------+-------------+
| 1 | 2 | some value |
+-----------------+--------------------+-------------+
| 1 | 3 | field value |
+-----------------+--------------------+-------------+
| 2 | 1 | data |
+-----------------+--------------------+-------------+
| 2 | 2 | whatever |
+-----------------+--------------------+-------------+
| 2 | 3 | anything |
+-----------------+--------------------+-------------+
| 3 | 1 | and so on |
+-----------------+--------------------+-------------+
| 3 | 2 | and on |
+-----------------+--------------------+-------------+
I need the data of all the clients, exported from the database to a csv. Important: Every client should appear in only one line. The expected result should look similar to this:
+-----------------------------------------------------------------------------+
| query result, clients with their custom fields as extra columns |
+-----------------------------------------------------------------------------+
| id | company | standard | standard | Custom | Custom | Custom |
| | | field1 | field2 | Field | Field | Field |
| | | | | Name 1 | Name 2 | Name 3 |
+----+-----------+----------+----------+-----------+------------+-------------+
| 1 | A company | abc | yz | any value | some value | field value |
+----+-----------+----------+----------+-----------+------------+-------------+
| 2 | B company | xyz | foo | data | whatever | anything |
+----+-----------+----------+----------+-----------+------------+-------------+
| 3 | C company | zyx | bar | and so on | and on | |
+----+-----------+----------+----------+-----------+------------+-------------+
I thought that a solution would be to enter a query in phpmyadmin and then easily get the result in a csv.
What would be the mysql query to get all the clients with their custom fields in separate columns ? Is there a better way to reach the objective stated above?
If you have any ideas on how to rephrase the title or any part of the question in order to make it more clear, please comment!
You can use the Pivot to get the expected output but as Mysql doesn't support pivot you have to use the case when clause. SQL query is as follows: Static SQL Query:
Select TB.*, TC.`standard_field1`, TC.`standard_field2`
from tblClients TC
Inner Join(
SELECT TBD.Id, TBD.company,
Max(CASE WHEN (TBD.fieldname ='Custom Field Name 1') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 1',
Max(CASE WHEN (TBD.fieldname ='Custom Field Name 2') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 2',
Max(CASE WHEN (TBD.fieldname ='Custom Field Name 3') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 3'
FROM(
Select TC.Id, TC.Company,
TCF.fieldname, TCFV.Value
from tblCustomFieldsValues TCFV
Inner Join tblClients TC
ON TCFV.relid = TC.ID
Inner Join tblCustomFields TCF
ON TCFV.fieldid = TCF.Id) TBD
Group BY TBD.Id, TBD.company) TB
On TB.Id = TC.Id
I am also providing the SQL fiddle link which will help you to get complete data along with output.
Dynamic SQl Query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('Max(CASE WHEN (TBD.fieldname =''', UN.fieldname,
''') THEN TBD.value ELSE NULL END) AS ''',UN.fieldname,''''))
INTO @sql
FROM (
Select * from tblCustomFields) UN;
SET @sql = CONCAT('Select TB.*, TC.`standard_field1`, TC.`standard_field2`
from tblClients TC
Inner Join(SELECT TBD.Id, TBD.company, ', @sql, '
FROM(
Select TC.Id, TC.Company,
TCF.fieldname, TCFV.Value
from tblCustomFieldsValues TCFV
Inner Join tblClients TC
ON TCFV.relid = TC.ID
Inner Join tblCustomFields TCF
ON TCFV.fieldid = TCF.Id) TBD
Group BY TBD.Id, TBD.company) TB On TB.Id = TC.Id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SQL Fiddle Link
I hope this will help you.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments