CODEWITHSUNDEEP
phpmysql
user3584968
user3584968

Reputation: 353

mysql count number of rows with completed columns and also count the number of rows with incomplete columns?

i am using this script to count how many rows exist in my table that have completed columns like so

<?php include 'config.php';

    $q = "SELECT * FROM supplier_session WHERE form1_completed = 'Yes' AND form2_completed = 'Yes' AND form3_completed = 'Yes' AND form4_completed = 'Yes'" or die (mysql_error());
    $result = mysql_query($q);
    $count = mysql_num_rows($result);
?>

<?php echo "(<font color=red>$count</font>)"; ?>

this script says that if 'form1_completed, form2_completed, form3_completeed and form4_compelted all = 'yes' then to count the number of rows.

Now what i want to do, is have a seperate count that shows the number of rows that are incomplete, so baring in mind that some rows may only have 'form1_completed' as 'yes' and 'form2_completed' as 'no'. i need to basically count any rows that do not have all four columns completed or set as yes, so 'form1_complted', 'form2_completed', 'form3_completed', 'form4_completed' if these are not all yes then to count the rows

could someone please show me how i can do this? thanks

Upvotes: 0

Views: 295

Answers (2)

CygnusH33L
CygnusH33L

Reputation: 21

If the fields do contain NULL values (say for when they haven't completed the form you could select everything and loop it as shown below though I would make the fields default to No and use Gordon Linoff answer myself.

<?php include 'config.php';
        $q = "SELECT * FROM supplier_session" or die (mysql_error());
        $result = mysql_query($q);
        $count = mysql_num_rows($result);
        $completed = 0;
        foreach($result as $check) {
            if(($check['form1_completed'] = 'Yes') && ($check['form2_completed'] = 'Yes') && ($check['form3_completed'] = 'Yes') && ($check['form4_completed'] = 'Yes')) {
             $completed++;
             }
        }

        $count // Total
        $completed // Completed
        $incomplete = $count - $completed; // Not Completed

        echo "(<font color=red>$count</font>)"; 
    ?>

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Try using conditional aggregation:

SELECT sum(form1_completed = 'Yes' AND form2_completed = 'Yes' AND
           form3_completed = 'Yes' AND form4_completed = 'Yes'
          ) as NumAllCompleted,
       sum(not (form1_completed = 'Yes' AND form2_completed = 'Yes' AND
                form3_completed = 'Yes' AND form4_completed = 'Yes'
               )
          ) as NumIncomplete
FROM supplier_session;

This assumes that the completed flags never take on NULL values.

Note: it is usually a bad idea to store "arrays" over values in multiple columns. You should have a junction/association table for each form. This would have one row for user/form combination, along with the status of that form. And it might have other information such as the date/time the form was completed.

Upvotes: 1

Related Questions