count equal text from php database

Question

Good morning, my doubt is as follows: in my case I have a table in the database that allows to evaluate a service, the classification is made by colors "red, yellow and green", ie the type is text.
What I wanted was to make a query in php that allows counting how many "green", "yellow" and "red" are inserted.
This is the table:

What I want is for example no day 06-06 there were 3 greens, 2 yellows and 1 red, and no day 07-06 there was 1 green. In question to date, I have a calendar I made with jquery and Ajax.
I tried something like this

$verificar=mysqli_query($link,"SELECT classificacao, count(classificacao) from questionario GROUP by classificacao");

    while ($linha = mysqli_fetch_array($verificar)) {
        if($linha['classificacao'] == 'vermelho')
        {
            $output.='<td style="background-color:#ff6666; text-align: center; padding: 8px; font-weight: bold;">'.$linha['classificacao'].'</td>';
        } 
        elseif($linha['classificacao'] == 'amarelo')
        {
            $output.='<td style="background-color:#ffff80; text-align: center; padding: 8px; font-weight: bold;">'.$linha['classificacao'].'</td>';
        } 
        elseif($linha['classificacao'] == 'verde')
        {
            $output.='<td style="background-color:#80ff80; text-align: center; padding: 8px; font-weight: bold;">'.$linha['classificacao'].'</td>';
        }
        $output.='  
        </tr>  
        ';  
    }

However it does not return any value, can anyone tell me what is wrong? Thank you from now who can help!

Answer 1

Ana, first I advise you to use PDO. I modified your sql and php a little bit.

<?php
$pdo = new PDO("mysql:host=HOST;dbname=BASE", "USUARIO", "SENHA");

try {
  $stmt = $pdo->prepare("SELECT count(cod) as total, classificacao from questionario group by classificacao");
  $stmt->execute();
  $output = '';
  while ($linha = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $output.='<tr> ';
    if($linha['classificacao'] == 'vermelho')
    {
      $output.='<td style="background-color:#ff6666; text-align: center; padding: 8px; font-weight: bold;">'.$linha['classificacao']." = ".$linha['total'].'</td>';
    }
    elseif($linha['classificacao'] == 'amarelo')
    {
      $output.='<td style="background-color:#ffff80; text-align: center; padding: 8px; font-weight: bold;">'.$linha['classificacao']." = ".$linha['total'].'</td>';
    }
    elseif($linha['classificacao'] == 'verde')
    {
      $output.='<td style="background-color:#80ff80; text-align: center; padding: 8px; font-weight: bold;">'.$linha['classificacao']." = ".$linha['total'].'</td>';
    }
    $output.='</tr> ';

  }
  echo $output;
} catch (PDOException $e) {
  echo "<script>alert(\"Ocorreu um erro durante a listagem ".$e."\");</script>";
}
?>

Answer 2

)

It seems to me that the query does not agree with the following ifs, because the columns were aliased instead of keeping the column as "classification" and values "green", "yellow", "red".

select to use must be something like:

select classificacao, count(*) from questionario group by classificacao

for the following ifs to function

Answer 3

I guess this is it then @Ana:

$verificar=mysqli_query($link,"
    SELECT
        COUNT(CASE WHEN classificacao = 'verde' THEN classificacao END),
        COUNT(CASE WHEN classificacao = 'vermelho' THEN classificacao END),
        COUNT(CASE WHEN classificacao = 'amarelo' THEN classificacao END)
    FROM questionario        
");

Those COUNT() will count the separate colors, ai vc adapts to your current code.

Answer 4

Hello

use

$verificar=mysqli_query($link,"
SELECT
    COUNT(CASE WHEN classificacao = 'verde' THEN classificacao END) as vermelho,
    COUNT(CASE WHEN classificacao = 'vermelho' THEN classificacao END) as vermelho,
    COUNT(CASE WHEN classificacao = 'amarelo' THEN classificacao END) as amarelo
FROM questionario        

");

and in the demonstration use for example

$row = mysqli_fetch_array($verificar);
echo $row['amarelo'];

so it will work.