GROUP BY ENUM来汇编PHP

Question

I have some sql table table

+----+------+--------+------------+------------+
| id | code | name   | Instructor | PlanePilot |
+----+------+--------+------------+------------+
|  1 |  001 | sasha  | N          | N          |
|  2 |  002 | sasha2 | Y          | N          |
|  3 |  003 | sasha3 | N          | Y          |
|  4 |  004 | sasha4 | Y          | Y          |
|  5 |  005 | sasha5 | Y          | Y          |
|  6 |  006 | sasha6 | N          | N          |
|  7 |  007 | sasha7 | Y          | N          |
|  8 |  008 | sasha8 | Y          | N          |
+----+------+--------+------------+------------+

I want to sort all of it to PHP Array with GROUP by ( Instructor , PlanePilot )

I results I want to get some php array like:

$array =[
withoutInstructorPLANEPilot:[sasha,sasha6],
Instructor:[sasha2,sasha4,sasha5,sasha7,sasha8],
PlanePilot:[sasha3,sasha4,sasha5]
]

How do it without 3 select queries like :

SELECT * FROM mytable where Instructor= 'Y'

I want to do it with 1 queries and convert it to PHP array

Answer 1

Is this what you want?

select group_concat( (case when Instructor = 'N' and PlanePilot = 'N' then name end) ) as neither,
       group_concat( (case when Instructor = 'Y' then name end) ) as instructors,
       group_concat( (case when PlanePilot = 'Y' then name end) ) as planepilots
from t;