Organize and populate a table :banghead:

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
leandrodimitrio
Forum Newbie
Posts: 4
Joined: Tue Jan 22, 2013 8:08 pm

Organize and populate a table :banghead:

Post 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
Last edited by leandrodimitrio on Wed Jan 23, 2013 9:58 am, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Organize and populate a table :banghead:

Post 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.
leandrodimitrio
Forum Newbie
Posts: 4
Joined: Tue Jan 22, 2013 8:08 pm

Re: Organize and populate a table :banghead:

Post 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:
leandrodimitrio
Forum Newbie
Posts: 4
Joined: Tue Jan 22, 2013 8:08 pm

Re: Organize and populate a table :banghead:

Post by leandrodimitrio »

Is there a way to combine multiple MYSQL statements in the same datatable?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Organize and populate a table :banghead:

Post 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
leandrodimitrio
Forum Newbie
Posts: 4
Joined: Tue Jan 22, 2013 8:08 pm

Re: Organize and populate a table :banghead:

Post by leandrodimitrio »

Sweet as homemade apple pie! :)

Thanks a whole bunch! ;)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Organize and populate a table :banghead:

Post by VladSun »

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
Post Reply