Organize and populate a table :banghead:
Posted: Tue Jan 22, 2013 8:22 pm
Hello gentlemen,
I've recently been given a challenge: create a simple report table to identify all customers and their purchases.
The chosen system (then) was Woocommerce (WP based).
And even though I'm far from being a DB master, I'm pretty sure they really did a crappy job!
They're using 2 tables: wp_posts (where every purchase is created and given an ID) and wp_postmeta (where, for every specific, there's a new line).
For example: the client makes a new purchase. wp_posts gives you an ID (in this example, 162). Then, wp_postmeta gives you ONE LINE PER DETAIL. E.g.:
meta_id | post_id | meta_key | meta_value
3732 | 162 | Email 02 | alan******100@hotmail.com
3733 | 162 | Telephone 02 | 011-9**********
3734 | 162 | Full name 02 | Fabio Augusto *
Apparently, they chose to verticalize the whole thing!
Now, MY CHALLENGE, is to condense it all into individual lines, with every "meta_key" as a column. For example:
ID FULLNAME EMAIL TELEPHONE
162 Fabio Augusto aladfasd@adfasd.com 234234234
Any clues? lol
I've recently been given a challenge: create a simple report table to identify all customers and their purchases.
The chosen system (then) was Woocommerce (WP based).
And even though I'm far from being a DB master, I'm pretty sure they really did a crappy job!
They're using 2 tables: wp_posts (where every purchase is created and given an ID) and wp_postmeta (where, for every specific, there's a new line).
For example: the client makes a new purchase. wp_posts gives you an ID (in this example, 162). Then, wp_postmeta gives you ONE LINE PER DETAIL. E.g.:
meta_id | post_id | meta_key | meta_value
3732 | 162 | Email 02 | alan******100@hotmail.com
3733 | 162 | Telephone 02 | 011-9**********
3734 | 162 | Full name 02 | Fabio Augusto *
Apparently, they chose to verticalize the whole thing!
Now, MY CHALLENGE, is to condense it all into individual lines, with every "meta_key" as a column. For example:
ID FULLNAME EMAIL TELEPHONE
162 Fabio Augusto aladfasd@adfasd.com 234234234
Any clues? lol