Implementing specific count query in php

Question

class memberclass {
   function Available()
   {
      if(!$this->DBLogin()) {
          $this->HandleError("Database login failed!");
          return false;
      }

      $ux = $_SESSION['username_of_user'];

      $qry = "Select (one='Not done') + (two='Not done') + (three='Not done') + (four='Not done') + (five='Note done') As num_not_done From $this->tablename Where  username='$ux'";
      $result = mysqli_query($this->connection, $qry);
      $result_length = mysqli_num_rows($result);

      echo "$result_length";
   }
}

I'm trying to show the amount of available items. So for every column where the value is "Not done" for a user it should sum it up in the query to form the total amount of "Not done" items. However when I try to show this number with the following code, I get the value "1" for each user for some reason:

<?= $memberclass->Available(); ?>

@jakubwrona I have tried "Select SUM((one='Not done') + (two='Not done') + (three='Not done') + (four='Not done') + (five='Note done')) As num_not_done From $this->tablename Where username='$ux'"; ` But it still gives the same result — Vitalynx
– Vitalynx, Commented Oct 15, 2016 at 18:17
I didn't mean this... could you provide an example of data from the table you are querying? — jakub wrona
– jakub wrona, Commented Oct 15, 2016 at 19:43
@jakubwrona link So what I want the code to do is count the amount of times it says "Not done" for a user (so that row). So then I can implement it in HTML as a statistic. Tasks not completed: <? php code here ?> For the row in the link I sent you it would say: Tasks not completed: 5. — Vitalynx
– Vitalynx, Commented Oct 15, 2016 at 20:11
Why do you store strings instead of 1 or 0 for DONE or NOT DONE ? This problem is about how you store the data, not about query complexity. — Heroselohim
– Heroselohim, Commented Oct 15, 2016 at 22:50

jakub wrona · Accepted Answer · 2016-10-15 22:54:07Z

2

You need cast the expressions to INT and then sum them. For MySQL database your query could look like this:

SELECT (CAST(one='Not done' AS UNSIGNED) + 
           CAST(two='Not done' AS UNSIGNED) + 
           CAST(three='Not done' AS UNSIGNED) + 
           CAST(four='Not done' AS UNSIGNED) +
           CAST(five='Not done' AS UNSIGNED)) as num_not_done 
FROM  tableName WHERE username = 'something'

edited Oct 15, 2016 at 22:54

answered Oct 15, 2016 at 22:48

jakub wrona

2,25219 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vitalynx Over a year ago

$ux = $_SESSION['username_of_user'];	 	$qry = "SELECT (CAST(one='Not done' AS UNSIGNED) + CAST(two='Not done' AS UNSIGNED) + CAST(three='Not done' AS UNSIGNED) + CAST(four='Not done' AS UNSIGNED) + CAST(five='Not done' AS UNSIGNED)) as num_not_done FROM  $this->tablename WHERE username = '$ux'";         $result = mysqli_query($this->connection, $qry);         $result_length = mysqli_num_rows($result); 		 	echo "$result_length";

I still get the result "1" for each user, your code sounds logical but it just doesn't seem to work. All I know is that the answer lies in the query.

jakub wrona Over a year ago

the number of rows will be one, but the only row will contain an index called "num_not_done" which will contain the right number. So do a fetch_array and then display the $fetched[0]['num_dot_done']

Collectives™ on Stack Overflow

Implementing specific count query in php

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related