通过PHP在MySQL中选择行作为列 [英] Selecting rows as columns in mysql through php
问题描述
您好，我正在尝试成功选择行作为mysql中的列.
Hello i am trying to successfully select rows as columns in mysql.
也许有人可以帮助我.
这是我的桌子的样子
+----------+----------------------------------------------------+----------+
| 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 |
如您所见，当brand_id和brand_name相同时，我要将行代码分组到单独的列中
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
最大行代码将永远是10.这就是为什么我拥有Code1-Code10
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");
我得到的结果是
+----------+----------------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| 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 |
我需要一种方法，以便在brand_id和brand_name更改后，num变量会回到0.
I need a way so that once the brand_id and brand_name change the num variable goes back to 0.
如您所见，它正在执行我想要的操作，但是一旦转到下一行，num变量将继续向上移动到下一列.这样便是添加下一行代码的地方.我假设我在某些地方需要if语句，但我不知道该怎么做.
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
谢谢您的时间.
推荐答案
您正在谈论的是部分转置，PHP不会自动执行.但是，您可以自己进行一些小的修改.
You're talking about partial transposing, which PHP does not do automagically. However, you could do this yourself with some small modifications.
在查询中，使用 <代码> GROUP_CONCAT() :
In your query, use GROUP_CONCAT():
SELECT brand_id, brand_name, GROUP_CONCAT(linecode) AS codes brands GROUP BY brand_id, brand_name
在PHP中，使用 explode() 可获取不同的代码:
$codes = explode(',', $row['codes']);
注意:我已经对您的基础表结构进行了一些假设.相应地调整查询.
Note: I've made some assumptions about your underlying table structure. Adjust the query accordingly.
这篇关于通过PHP在MySQL中选择行作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
