Organize and populate a table :banghead:
Moderator: General Moderators
-
leandrodimitrio
- Forum Newbie
- Posts: 4
- Joined: Tue Jan 22, 2013 8:08 pm
Organize and populate a table :banghead:
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
Last edited by leandrodimitrio on Wed Jan 23, 2013 9:58 am, edited 1 time in total.
Re: Organize and populate a table :banghead:
It's funny because you think that's a bad thing when in reality it's a good thing. Best way to store arbitrary key/value pairs.leandrodimitrio wrote:Apparently, they chose to verticalize the whole thing!
a) Do a series of JOINs using the meta_key, like
Code: Select all
LEFT JOIN wp_postmeta pm_email ON ... AND pm_email.meta_key = "Email 02"
LEFT JOIN wp_postmeta pm_telephone ON ... AND pm_telephone.meta_key = "Telephone 02"
LEFT JOIN wp_postmeta pm_fullname ON ... AND pm_fullname.meta_key = "Full name 02"Code: Select all
$data = array();
// get every matching $row in the table {
$data[$row["meta_key"]] = $row["meta_value"];
// }Code: Select all
$fullname = (isset($data["Full name 02"]) ? $data["Full name 02"] : "(no name)");c) Check if WordPress has some sort of function that does this for you. Quite possible, but I don't know WordPress so I couldn't tell you.
-
leandrodimitrio
- Forum Newbie
- Posts: 4
- Joined: Tue Jan 22, 2013 8:08 pm
Re: Organize and populate a table :banghead:
requinix, thank you so much for your reply!
As I said, I'm no guru here, so bear with my inexperience, if you will!
Prior to your response, I went to bed thinking about INNER JOINs for each column of my table. I know it's not the best way, but it's somewhat close to your approach.
Okay, so I got the query to organize the meta_keys... one for each key!
Don't worry about WP, I'm doing this outside its realms.
Now I'm kinda lost as to how I should use each of these SELECTs (I'm guessing about 10 of 'em) in a PHP array.

As I said, I'm no guru here, so bear with my inexperience, if you will!
Prior to your response, I went to bed thinking about INNER JOINs for each column of my table. I know it's not the best way, but it's somewhat close to your approach.
Okay, so I got the query to organize the meta_keys... one for each key!
Code: Select all
SELECT wp_posts.id
FROM wp_posts
LEFT JOIN wp_postmeta pm_first_name
ON wp_posts.id = pm_first_name.post_id AND pm_first_name.meta_key = "_billing_first_name"
WHERE pm_first_name.post_id IS NOT NULLNow I'm kinda lost as to how I should use each of these SELECTs (I'm guessing about 10 of 'em) in a PHP array.
-
leandrodimitrio
- Forum Newbie
- Posts: 4
- Joined: Tue Jan 22, 2013 8:08 pm
Re: Organize and populate a table :banghead:
Is there a way to combine multiple MYSQL statements in the same datatable?
Re: Organize and populate a table :banghead:
You don't need multiple SELECTs for the pieces of data. Just one where you put all the LEFT JOINs.
Code: Select all
SELECT
wp_posts.id,
pm_first_name.meta_value AS _billing_first_name,
pm_last_name.meta_value AS _billing_last_name,
/* etc */
FROM wp_posts
LEFT JOIN wp_postmeta pm_first_name ON wp_posts.id = pm_first_name.post_id AND pm_first_name.meta_key = "_billing_first_name"
LEFT JOIN wp_postmeta pm_last_name ON wp_posts.id = pm_last_name.post_id AND pm_last_name.meta_key = "_billing_last_name"
/* etc */
WHERE pm_first_name.post_id IS NOT NULL-
leandrodimitrio
- Forum Newbie
- Posts: 4
- Joined: Tue Jan 22, 2013 8:08 pm
Re: Organize and populate a table :banghead:
Sweet as homemade apple pie! 
Thanks a whole bunch!
Thanks a whole bunch!
Re: Organize and populate a table :banghead:
Or try building a dynamic query for "inverting the dimension" - AKA "pivot table", AKA "cross tabulation" query
There are 10 types of people in this world, those who understand binary and those who don't