Question:

How do I export data in the tables below to a cvs excel file so all attributes in dog_attributes have their own column using mostly MySQL?

NOTES:

• I will be exporting one group_id and all dog_attributes for that group_id at a time.
• There could be thousands of dogs and a few hundred group_elements per group. I figured using mostly MySQL instead of a PHP loop would be better for speed since MySQL has a built in export function.
• My server is running MySQL with PHP and Codeigniter Framework.

Example:

There are four tables.

group_tbl - groups are setup by the admin and used to contain common elements.

+----+---------+
| id | var1    |
+----+---------+
| 1  | data    |
| 2  | data    |
+----+---------+

group_elements_tbl - contains elements setup by the admin. These are form fields filled in by the user.

+----+----------+-----------+
| id | group_id | elmt_name |
+----+----------+-----------+
| 1  | 1        | height    |
| 2  | 1        | color     |
| 3  | 2        | breed     |
+----+----------+-----------+

dogs_tbl - contains dogs that each user has created.

+----+----------+---------+
| id | group_id | name    |
+----+----------+---------+
| 1  | 1        | Rover   |
| 2  | 1        | Buck    |
| 3  | 2        | Rex     |
+----+----------+---------+

dog_attributes_tbl - contains the values of the custom elements setup in the groups_elements_tbl by the admin.

+----+--------------------+------------+
| id | group_elements_id  | attr_value |
+----+--------------------+------------+
| 1  | 1                  | 54 inches  |
| 2  | 2                  | brown      |
| 3  | 1                  | 34 inches  |
| 3  | 2                  | white      |
| 4  | 3                  | husky      |
+----+---------+------------+

Final Results:

Final Excel Spreadsheet for group 1 (group_id = 1):

+--------+----------+-----------+--------+
| dog_id | dog_name | height    | color  |
+--------+----------+-----------+--------+
| 1      | Rover    | 54 inches | brown  |
| 2      | Buck     | 34 inches | white  |
+--------+----------+-----------+--------+

Final Excel Spreadsheet for group 2 (group_id = 2):

+--------+----------+-----------+
| dog_id | dog_name | breed     |
+--------+----------+-----------+
| 3      | Rex      | husky     |
+--------+----------+-----------+

The fastest way is to handle those properties in programming way - no with SQL.

You should grab all dogs, in the second request all properties for dogs you are interested in and merge it with foreach, etc

I would create a temp table with fields:

group_id
dog_id
dog_name
elmt_name
attr_value

This is a flat representaion of the original database. Two important things:

1) this table must be generated with mysql stored procedure NOT with php AND in one transaction. Otherwise if you have thousands of dogs it will be very slow.

2) this flat table is a special cache table for your report. If dogs data are changed slowly, you do not need to delete it until changing dogs data.

Then with a simple query you can generate the excel export. Do not forget the indexes!