Page 1 of 1

Organize and populate a table :banghead:

Posted: Tue Jan 22, 2013 8:22 pm
by leandrodimitrio
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

Re: Organize and populate a table :banghead:

Posted: Tue Jan 22, 2013 9:07 pm
by requinix
leandrodimitrio wrote:Apparently, they chose to verticalize the whole thing!
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.

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"
b) In PHP query for all the rows in wp_postmeta and build up an array using the meta_keys as array keys.

Code: Select all

$data = array();
// get every matching $row in the table {
    $data[$row["meta_key"]] = $row["meta_value"];
// }
then

Code: Select all

$fullname = (isset($data["Full name 02"]) ? $data["Full name 02"] : "(no name)");
Even better, move the "Full name 02" into a global/class constant.

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.

Re: Organize and populate a table :banghead:

Posted: Wed Jan 23, 2013 6:00 am
by leandrodimitrio
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!

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 NULL
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.

:banghead:

Re: Organize and populate a table :banghead:

Posted: Wed Jan 23, 2013 10:57 am
by leandrodimitrio
Is there a way to combine multiple MYSQL statements in the same datatable?

Re: Organize and populate a table :banghead:

Posted: Wed Jan 23, 2013 1:27 pm
by requinix
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

Re: Organize and populate a table :banghead:

Posted: Wed Jan 23, 2013 7:56 pm
by leandrodimitrio
Sweet as homemade apple pie! :)

Thanks a whole bunch! ;)

Re: Organize and populate a table :banghead:

Posted: Thu Jan 31, 2013 5:52 pm
by VladSun
Or try building a dynamic query for "inverting the dimension" - AKA "pivot table", AKA "cross tabulation" query