count the same database text in php

Question

count the same database text in php

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

2

Good morning, my question is this: 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 to do is query in php that lets you count how many "green", "yellow" and "red" are inserted.
This is the table:

WhatIintendisforexampleon06-06therewere3green,2yellowand1red,andon07-06therewere1green>.Inissuetodate,IhaveacalendarIdidwithjqueryandAjax.HomeItriedsomethinglikethis

$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>  
        ';  
    }

But it does not return any value, can someone tell me what is wrong? Thank you right now who can help!

php mysql html query

asked by anonymous 07.06.2018 / 10:22

3 answers

0

)

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

The select to use should be something like:

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

for the following ifs to work

07.06.2018 / 13:27

0

I believe this to be here 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        
");

These COUNT() will count the separated colors, so you adapt to your current code.

07.06.2018 / 13:48

Separating a dataframe by some criteria - Python Pandas How to compare two columns of a worksheet, and keep information that is the same?

score 2 · Accepted Answer

Ana, I first advise you to use PDO. I have rather modified your sql and php at a glance.

<?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>";
}
?>