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

Try this

$activeQuery = SELECT status, count(status) as "status count" FROM `assignments` WHERE `user` = $user_id GROUP BY `status`

edit: added group by

Answer 2

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

Answer 3

Instead of doing them individually you could use the following single SQL statement

SELECT count(*), `status` 
FROM `assignments` 
WHERE `user` = $user_id
  AND `status` in (0,1,2)
GROUP BY `status`
ORDER BY `status`

The loop around the result set to extract the results.

Answer 4

Use the GROUP BY to get it in one query, but on separate rows.

$query = mysql_query("SELECT `status`, count(*) AS `num` FROM `assignments` WHERE `user` = $user_id AND `status` in (0,1,2) GROUP BY `status` ORDER BY `status` ASC");
$active_count = 0;
$failed_count = 0;
$completed_count = 0;
while ($array = mysql_fetch_assoc($query))
{
    if ($array['status'] == 0)
        $active_count = $array['num'];
    else if ($array['status'] == 1)
        $failed_count = $array['num'];
    else if ($array['status'] == 2)
        $completed_count = $array['num'];
}

Answer 5

SELECT  
(SELECT COUNT(*) FROM `assignments` WHERE `user` = $user_id AND `status` = 0) AS active,
(SELECT COUNT(*) FROM `assignments` WHERE `user` = $user_id AND `status` = 1) AS failed,
(SELECT COUNT(*) FROM `assignments` WHERE `user` = $user_id AND `status` = 2) AS completed,
FROM `assignments`
GROUP BY active, failed, completed

Haven't checked the markup, but this is near or near enough.

Answer 6

When I have the option, I'm in the habit of using prepared statement(s) to help protect against sql-injection protection and for efficiency (requires using mysqli).

But if this is not an option, then

SELECT
  CASE `status`
      WHEN 0 THEN `active`
      WHEN 1 THEN `failed`
      WHEN 2 THEN `completed`
      ELSE `unknown`
    END
    AS `statuslabel`,
  COUNT(status)
    AS `statuscount`
FROM `assignments` 
WHERE `user` = $escaped_user_id
GROUP BY `statuslabel`
ORDER BY `statuslabel`

Note the use of the $escaped_user_id, which would be created beforehand:

$escaped_user_id = mysql_real_escape_string($user_id);