07-26-2023, 06:43 PM
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.*
#Simplified / Generic version of the problem#
I'll try to describe a simplified generic version of the question which may apply to many similar situations.
###Hypothesis###
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 |
+-----------------+--------------------+-------------+
###Objective###
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 | |
+----+-----------+----------+----------+-----------+------------+-------------+
###The idea###
I thought that a solution would be to enter a query in phpmyadmin and then easily get the result in a csv.
###The final question###
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!
**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.*
#Simplified / Generic version of the problem#
I'll try to describe a simplified generic version of the question which may apply to many similar situations.
###Hypothesis###
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 |
+-----------------+--------------------+-------------+
###Objective###
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 | |
+----+-----------+----------+----------+-----------+------------+-------------+
###The idea###
I thought that a solution would be to enter a query in phpmyadmin and then easily get the result in a csv.
###The final question###
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!