charith charith - 1 month ago 16
MySQL Question

Count and display multiple mysql row values in different columns for PHP echo

I have using following mysql statement to count and retrieve multiple database rows in mysql database:

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


But the output of this query only contain one column and the column name is
COUNT(*)
. This query returns three counted values and I want those value to assign PHP variables. following PHP code I used to get values:

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 "";}
?>


But this code throwing me an error without showing single value. I want to assign those three mysql counts to three mysql variables. how can I do that?

Answer

Consider using an IF or CASE statement in your query to return a single row:

 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';

Now you will have 1 row with three fields.