Selecting rows as columns in mysql through php

Question

Hello i am trying to successfully select rows as columns in mysql.

Maybe someone can help me.

This is what my table looks like

+----------+----------------------------------------------------+----------+
| brand_id | brand_name                                         | linecode | 
+----------+----------------------------------------------------+----------+
| DPQT     | 1-800 Tow Truck                                    | DER      | 
| DPQT     | 1-800 Tow Truck                                    | DCF      |
| DPQT     | 1-800 Tow Truck                                    | DCA      |
| DPQT     | 1-800 Tow Truck                                    | AAA      |
| DPQT     | 1-800 Tow Truck                                    | DDD      |
| BLGR     | 1-800-Radiator                                     | Curt     |
| BGVM     | 100+ Manufacturing/Coyote                          | ASC      |
| DPQS     | 10C Technologies Inc                               | AQW      |
| DPQS     | 10C Technologies Inc                               | ASQ      |
| FDJG     | 2 Cool AirVents                                    | CAS      |

and i am trying to get the results to look like :

   +----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| brand_id | brand_name                                         | Code1 | Code2 | Code3 | Code4 | Code5 | Code6 | Code7 | Code8 | Code9 | Code10 |
+----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| DPQT     | 1-800 Tow Truck                                    | DCF   | DCA   | AAA   | DDD   | DER   | NULL  | NULL  | NULL  | NULL  | NULL   |
| BLGR     | 1-800-Radiator                                     | Curt  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |
| BGVM     | 100+ Manufacturing/Coyote                          | ASC   | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |
| DPQS     | 10C Technologies Inc                               | ASQ   | AQW   | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |
| FDJG     | 2 Cool AirVents                                    | CAS   | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL   |

As you can see when the brand_id and the brand_name are the same i want to group the line codes into separate columns

The max line codes will ever have is 10. That is why i have Code1-Code10

This is what i am doing right now, and it is not working.

$sql0 = 'set @num := 0';
$result = mysqli_query($dbh,"select brand_id,   brand_name,   
max(case when group_row_number = 1 then linecode end) Code1,   
max(case when group_row_number = 2 then linecode end) Code2,   
max(case when group_row_number = 3 then linecode end) Code3,   
max(case when group_row_number = 4 then linecode end) Code4,   
max(case when group_row_number = 5 then linecode end) Code5,   
max(case when group_row_number = 6 then linecode end) Code6,  
max(case when group_row_number = 7 then linecode end) Code7,   
max(case when group_row_number = 8 then linecode end) Code8,   
max(case when group_row_number = 9 then linecode end) Code9,   
max(case when group_row_number = 10 then linecode end) Code10 
from (  select brand_id, brand_name, linecode, @num := @num + 1 as group_row_number from linecodes_temp ) src 
group by brand_id, brand_name 
order by if(linecode = '' or linecode is null,1,0), brand_name ASC");

and the results i am getting back are

+----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| brand_id | brand_name                                         | Code1 | Code2 | Code3 | Code4 | Code5 | Code6 | Code7 | Code8 | Code9 | Code10 |
+----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| DPQT     | 1-800 Tow Truck                                    | DER   | DCF   | DCA   | AAA   | DDD   | NULL  | NULL  | NULL  | NULL  | NULL   |
| BLGR     | 1-800-Radiator                                     | NULL  | NULL  | NULL  | NULL  | NULL  | Curt  | NULL  | NULL  | NULL  | NULL   |
| BGVM     | 100+ Manufacturing/Coyote                          | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | ASC   | NULL  | NULL  | NULL   |
| DPQS     | 10C Technologies Inc                               | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | AQW   | ASQ   | NULL   |
| FDJG     | 2 Cool AirVents                                    | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | NULL  | CAS    |

I need a way so that once the brand_id and brand_name change the num variable goes back to 0.

As you can see it is doing what i want it to do but once it goes to the next row the num variable continues going up to the next column. So then that is where the next line code gets added. I am assuming i need a if statement in there some where but i don't understand how to do it.

If anyone could update my query so that this happens i would be very thankful

Thank you for your time.

Why don't you use GROUP_CONCAT to gather all the codes as an array in one field? — didierc
– didierc, Commented Dec 3, 2012 at 19:02

Jason McCreary · Accepted Answer · 2012-12-03 18:57:28Z

1

You're talking about partial transposing, which PHP does not do automagically. However, you could do this yourself with some small modifications.

In your query, use GROUP_CONCAT():

SELECT brand_id, brand_name, GROUP_CONCAT(linecode) AS codes brands GROUP BY brand_id, brand_name

In PHP, use explode() to get your different codes:

$codes = explode(',', $row['codes']);

Note: I've made some assumptions about your underlying table structure. Adjust the query accordingly.

answered Dec 3, 2012 at 18:57

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

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

3 Comments

Jason McCreary Over a year ago

The code is only an example. You'll need to adjust it. But this approach will work.

user1863457 Over a year ago

i am not even worried about php just the mysql statement to be done correctly.

user1863457 Over a year ago

unfortunately it needs to look like it is above. With creating columns for each line code.

achand8238 · Accepted Answer · 2012-12-03 19:52:44Z

0

select a.name,a.value,a.code,T.code  from test1 as a INNER JOIN test1 as T on  
a.name=T.name and a.value = T.value and a.code != T.code  group by a.name;

Try this. I have assumed that your brand name and brand id are always going to be the same. If they are going to be different , they remove the check for brand names.

This one is just an idea of what the query should be . U can tweak this to get better results

answered Dec 3, 2012 at 19:52

achand8238

1742 silver badges6 bronze badges

Comments

Dave · Accepted Answer · 2012-12-03 20:13:52Z

EDIT: if you MUST do it entirely in MySQL, this isn't pretty, but...

SELECT 
    a.brand_id, a.brand_name, b.linecode AS code1, c.linecode AS code2, d.linecode AS code3, e.linecode AS code4, f.linecode AS code4 FROM test a 
LEFT JOIN 
    test b ON b.brand_id = a.brand_id 
LEFT JOIN 
    test c ON c.brand_id = b.brand_id AND b.linecode != c.linecode
LEFT JOIN 
    test d ON d.brand_id = c.brand_id AND d.linecode != c.linecode AND d.linecode != b.linecode
LEFT JOIN 
    test e ON e.brand_id = d.brand_id AND e.linecode != d.linecode AND e.linecode != c.linecode AND e.linecode != b.linecode
LEFT JOIN 
    test f ON f.brand_id = e.brand_id AND f.linecode != e.linecode AND f.linecode != d.linecode AND f.linecode != c.linecode AND f.linecode != b.linecode
GROUP BY 
    a.brand_id
ORDER BY 
    a.brand_id

Repeat the LEFT JOINs until you have 10 code columns.

Collectives™ on Stack Overflow

Selecting rows as columns in mysql through php

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related