php判断学生姓名,php – 按学生姓名获取列名

是否可以在查询中获取表的列名？我正在使用PDO

stu_name maths science music art

John done done - done

Max - done - done

Peter - done - -

例如.如何获得John未完成的主题( – )？

音乐应该是结果.

这就是我现在所处的位置.我不知道怎么做.

$sql = "SELECT * FROM coll_student WHERE stu_name = :name AND ?? = '-' LIMIT 1";

$stat = $connect->prepare($sql);

$stat->execute(array(':name'=>"John"));

$output = $stat->fetchAll(PDO::FETCH_COLUMN);

解决方法:

你可以这样做：

SELECT

GROUP_concat(COALESCE(CASE WHEN maths = '-' THEN 'maths' END, ''),

COALESCE(CASE WHEN science = '-' THEN 'science' END, ''),

COALESCE(CASE WHEN art = '-' THEN 'art' END, ''),

COALESCE(CASE WHEN music = '-' THEN 'music' END, '')) AS SubjectsNotDone

FROM coll_student

WHERE stu_name = 'John'

AND ((maths = '-') OR

(science = '-') OR

(art = '-') OR

( music = '-')) ;

这会给你：

| SubjectsNotDone |

|-----------------|

| music |

但是,您的表不是normalized.通过创建新表,您可以使其更简单,更容易,性能更高.主题：

> SubjectId,

>主题名称.

然后表coll_student将是这样的：

> stu_name,

> SubjectId- Foreign key到受试者表：

constraint FK_SubjectId foreign key (SubjectId) references Subjects(SubjectId)

>状态：bit或int作为已完成或未完成的标志(如果有,则为其他状态).

然后你的查询会更简单,如下所示：

SELECT subjectname

FROM coll_student AS c

INNER JOIN Subjects AS s ON c.SubjectID = s.SubjectId

WHERE c.name = 'John' AND c.Status = 0;

标签：php,mysql,pdo

来源： https://codeday.me/bug/20190727/1557059.html