Mysql - Rows into Columns

Question

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     |
+--------+----------+-----------+

hsz · Accepted Answer · 2011-10-27 10:26:00Z

2

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

answered Oct 27, 2011 at 10:26

hsz

153k63 gold badges268 silver badges320 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zechdc Over a year ago

Thanks for answering so quickly. Based on the answer you provided I see that my question wasn't stated correctly. I have revised the question. Hopefully this makes more sense now. Please let me know if you have any questions.

zechdc Over a year ago

@Fred So it would be better to run thousands of rows through a couple foreach loops to get it all sorted out then to do a MySQL statement? I've always been told otherwise, but I'm fine with that if it really is better.

andep · Accepted Answer · 2011-10-28 05:53:17Z

0

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!

answered Oct 28, 2011 at 5:53

andep

8,1631 gold badge16 silver badges5 bronze badges

Collectives™ on Stack Overflow

Mysql - Rows into Columns

Question:

Example:

Final Results:

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Question:

Example:

Final Results:

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related