PHP - Check if all entries in a table has the value 1 and then echo something

Question

im currently stuck with an issue. Ive built a basic "team-work platform" in which you can set tasks in a to-do list. I've implemented the functionality that can mark a task as complete by setting the value of done to 1

I need to be able to check if all of the tasks in the list are set to done, and if so echo something. My code checks for the value 1, but it settles with a single entry being set to 1. But i need it to check if all tasks have the value 1 and if they do it should echo something.

$res3 = mysql_query("SELECT * FROM tasks WHERE tasks.planet_id=1 AND        team_id=$teamid AND done=1") 
or die(mysql_error()); 
if ($res3 && mysql_num_rows($res3) > 0)
{
    echo 'Complete!'; 
}
else
{
echo 'Not done yet!';
}

I'll try to give you an example of how i want it to work: Lets say i have 10 tasks in the table. I want the code to recognise when all 10 of these tasks are marked as done with the value 1 set. And then echo "all your tasks are complete". So it needs to somehow loop through all the entries in the table and check if they are all set to 1, and when they are all set to 1 it echoes something.

Please help! :)

Answer 1

Assuming that done is an integer can can be either 0 or 1, you could do something like:

SELECT COUNT(*) total, SUM(done) totalDone FROM tasks WHERE tasks.planet_id=1 AND team_id=$teamid;

And then test in your PHP code that total == totalDone.

Alternatively, if you really want to only get a row out of the database when total == totalDone (as your comments seem to suggest), you could write something like this:

SELECT * FROM (SELECT COUNT(*) total, SUM(done) totalDone FROM tasks WHERE tasks.planet_id=1 AND team_id=$teamid) _X WHERE _X.total = _X.totalDone;

But that just adds a lot of extra complexity for no real gain, and I wouldn't recommend doing it that way.

Note that you should not use mysql_* functions in new code, and should instead use either mysqli or PDO. mysql_* is not recommended for new code.

Also, you should be careful with using variables directly in query strings. That can easily lead to sql injection vulnerabilities. Instead, use parameterized queries with mysqli or PDO.

Answer 2

The answer to 'all tasks done' is best done with the question of how many tasks where done <> 1.

I.e.

SELECT count(*) as 'incomplete'
FROM tasks 
WHERE tasks.planet_id=1
  AND team_id=$teamid and done <> 1;

Therefore you're able to use the code:

if($res3) {
  $incompleteQueryResult = mysql_fetch_assoc($res3);
  if ($incompleteQueryResult['incomplete'] > 0) {
     echo "Not done yet";
  } else {
     echo "Complete!";
  }
} else {
  echo "Could not retrieve completed tasks";
}

If you still need to retrieve both the number of completed tasks as well as the number of incomplete, you could modify the query similar to the following.

SELECT 
  IF(done = 1, 'complete', 'incomplete') as status,
  COUNT(*) AS 'number_in_status'
FROM tasks 
WHERE tasks.planet_id=1
  AND team_id=$teamid
GROUP BY done

And you'll need to modify how you retrieve it in the PHP as well if so.

If you need to know all of the above, then either execute two queries (one as an aggregate/summary and one as the full data set) or keep track of it in a variable. e.g.

$numIncompleteTasks = 0;
while($row = mysql_fetch_assoc($res3)) {
  $numIncompleteTasks += ! (bool) $row['done']; 
}
// you now know how many tasks are incomplete.

You could modify this code to track both complete and incomplete.

Deprecation notice.

I'd recommend reviewing your use of mysql_* functions - PHP deprecated and removed these functions in recent versions of PHP.

Answer 3

The answer from jbafford will not work in certains conditions. Let's imagine we have only three possible values: 0,1 and 2. In the case of 0+1+2, the algorithm will say that COUNT = SUM, when in reality, we have a {0,1,2} and not {1,1,1}. Why do I ask? Because I'm looking for a way in MYSQL to check if it is all ones, and I ruled out COUNT=SUM or in my case, taking the average, as I have this exception in my data. A way to handle this exception is to add a COUNT DISTINCT. If COUNT DISTINCT=1 and COUNT=SUM, then the dataset is only 1s.