COUNT function with criteria

Question

How do I make this count meet some criteria?

ALBERTO

     CONCLUÍDO | 8

     REPROVADO | 3

     DUPLICADO | 4

     EM ANÁLISE| 5

I need you to return this result to the screen... where:

The number represents how many records exist with this DATA in the STATUS field for the NAME field ( "ALBERTO" I will insert as filter criterion )

$conn = mysqli_connect($servidor, $usuario, $senha, $dbname);

if(!$conn)
{
    echo "ERROR 1 - ERROR 1 - ERROR 1";
}

$criterioDeFiltro = "ALBERTO";

$criterioDeFiltro = mysqli_real_escape_string($conn, $criterioDeFiltro);

$sql = "SELECT count(id) AS total, nome, situacao FROM tbl_test where nome = '{$criterioDeFiltro}' group by nome, situacao";

$result = mysqli_query($conn,$sql);

while ($values = mysqli_fetch_assoc($result)) {
    $num_rows = $values['total'];
    echo "Nome = " . $values["nome"] . "\n";
    echo "Situação = " . $values["situacao"] . "\n";
    echo "Total = " . $num_rows . "\n";
    echo "\n";
}

mysqli_free_result($result);
mysqli_close($conn);

    echo "ERROR 2 - ERROR 2 - ERROR 2";

?>

Answer 1

To count records, we use the function count, as you have already put in the example query.

To bring the other data you need, just inform these two fields in the query, but how the query uses a count you will need to utilize a group by with these fields, thus grouping the results, see an example:

select count(id) as total, nome, situacao from tbl_test group by nome, situacao

With this query, you will already have the result you want.

---

You say the name ALBERTO will be a filter criterion, I believe then will be a where in the query and can then stay that way:

$criterioDeFiltro = mysqli_real_escape_string($conn, $criterioDeFiltro);

$sql = "select count(id) as total, nome, situacao from tbl_test where nome = '{$criterioDeFiltro}' group by nome, situacao";

---

Your complete code will then be more or less as follows:

<?php

$servidor = "server";
$usuario = "user";
$senha = "password";
$dbname = "database";

$conn = mysqli_connect($servidor, $usuario, $senha, $dbname);

if(!$conn)
{
    echo "Error";
}

$criterioDeFiltro = "ALBERTO";

$criterioDeFiltro = mysqli_real_escape_string($conn, $criterioDeFiltro);

$sql = "SELECT count(id) AS total, nome, situacao FROM tbl_test where nome = '{$criterioDeFiltro}' group by nome, situacao";

$result = mysqli_query($conn,$sql);

while ($values = mysqli_fetch_assoc($result)) {
    $num_rows = $values['total'];
    echo "Nome = " . $values["nome"] . "\n";
    echo "Situação = " . $values["situacao"] . "\n";
    echo "Total = " . $num_rows . "\n";
    echo "\n";
}

if ($result) {
    mysqli_free_result($result);
}

if ($conn) {
    mysqli_close($conn);
}

See online: http://tpcg.io/Y5YxhbIz

---

Documentation: https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html