PHP MySQL Convert Multiple Columns into Multiple Rows

Question

So I have a database table that is structured something like this...

NAME | PHONE | EMAIL1 | EMAIL2 | EMAIL3

Those would be my columns, imagine this is a row in that database..

John | 555-555-5555 | [email protected] | [email protected] | [email protected]

I need to be able to dump these entries to a CSV which I have the code for working already BUT the format is the issue.

I need each email to be displayed on a single row with the corresponding name and number, in the following format...

John | 555-555-5555 | [email protected]
John | 555-555-5555 | [email protected]
John | 555-555-5555 | [email protected]

I need to retrieve thousands of rows and display them this way, is there some way using a PHP while loop I can display the rows from my database table in this format? Any help is greatly appreciated.

Yes, there is a way. But I'm afraid it's on you to do the first step and actually try something. (just loop through the rows and add 3 items per row to an array. And maybe test if you have something in each column) — Jeff
– Jeff, Commented May 18, 2018 at 22:55
Why do you have your table like this in the first place? What if someone has more than 3 emails? You should normalize the data by putting emails into a separate table with 1 row per email. — Barmar
– Barmar, Commented May 18, 2018 at 23:27
@Barmar As an aside, I know no one in that enviable position. — Strawberry
– Strawberry, Commented May 19, 2018 at 9:30
@Strawberry True dat. I've had the luxury of normalizing a schema at my job just once, because the application was being replaced and I could do it right from scratch. But like telling people not to use mysql_* functions, we're legally obligated to comment about it. :) — Barmar
– Barmar, Commented May 19, 2018 at 12:15

Raymond Nijland · Accepted Answer · 2018-05-18 23:13:32Z

1

This process is called unpivoting.
And is most commonly done with UNION ALL within MySQL.

SELECT 
   NAME
 , PHONE
 , EMAIL1 
FROM 
 table

UNION ALL 

SELECT 
   NAME
 , PHONE
 , EMAIL2 
FROM 
 table

UNION ALL 

SELECT 
   NAME
 , PHONE
 , EMAIL3 
FROM 
 table

answered May 18, 2018 at 23:13

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

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

1 Comment

spencer7593 Over a year ago

We could also omit rows that have NULL values for email column by adding a WHERE clause to each SELECT. To apply an ORDER BY to the entire set, we can enclose each SELECT in parens, and then add an ORDER BY clause after the last closing paren. +10.

Collectives™ on Stack Overflow

PHP MySQL Convert Multiple Columns into Multiple Rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related