MySQL -- How to do this better?

Question

$activeQuery = mysql_query("SELECT count(`status`) AS `active` FROM `assignments` WHERE `user` = $user_id AND `status` = 0");
$active = mysql_fetch_assoc($activeQuery);

$failedQuery = mysql_query("SELECT count(`status`) AS `failed` FROM `assignments` WHERE `user` = $user_id AND `status` = 1");
$failed = mysql_fetch_assoc($failedQuery);

$completedQuery = mysql_query("SELECT count(`status`) AS `completed` FROM `assignments` WHERE `user` = $user_id AND `status` = 2");
$completed = mysql_fetch_assoc($completedQuery);

There has to be a better way to do that, right? I don't know how much I need to elaborate as you can see what I'm trying to do, but is there any way to do all of that in one query? I need to be able to output the active, failed, and completed assignments, preferably in one query.

Answer 1

You can try something like this query

SELECT Status , COUNT(*) StatusCount 
FROM assignments
WHERE Status IN (0, 1, 2)
AND User = $user_id 
GROUP BY Status