计算和显示PHP回送的不同列中的多个mysql行值

Question

我使用以下mysql语句来计数和检索mysql数据库中的多个数据库行：

SELECT COUNT(*) FROM leavesrecords WHERE leavetype IN ('Casual','Annual','Medical') and empno = '202'  GROUP BY leavetype

但是，此查询的输出仅包含一个列，列名为COUNT(*)。此查询返回三个计数值，我希望这些值为PHP变量赋值。下面的PHP代码是我用来获取值的：

include 'database.php';
$query = "SELECT COUNT(*) FROM leavesrecords WHERE leavetype IN ('Casual','Annual','Medical') and empno = '202'  GROUP BY leavetype; ";
$stmt = $con->prepare($query);
$stmt->execute();

$num = $stmt->rowCount();
if($num>0){
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        extract($row);
        echo $row["leavesrecords"];
    } 
}
else{ echo "";}
?>

但这段代码在没有显示单个值的情况下向我抛出一个错误。我想将这三个mysql计数分配给三个mysql变量。我怎么能这么做？

Answer 1

考虑在查询中使用IF或CASE语句返回一行：

 SELECT sum(if(leavetype='Casual',1,0)) AS `CasualCount`,
    sum(if(leavetype='Annual',1,0)) AS `AnnualCount`,
    sum(if(leavetype='Medical',1,0)) as `MedicalCount`
FROM leaverecords
WHERE leavetype IN ('Casual','Annual','Medical') and empno = '202';

现在，您将有一个有三个字段的行。

Answer 2

应该对thr列结果使用别名。

 SELECT COUNT(*) as my_count 
 FROM leavesrecords 
 WHERE leavetype IN ('Casual','Annual','Medical') and empno = '202'  GROUP BY leavetype

并引用使用键别名的列。

if($num>0){
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        // extract($row);
        echo $row["my_count"];
    } 
}

您不应该使用tablename来引用行内容，而应该引用正确的列名。