Show result of a sql by group query on PHP page

Asked

Viewed 22 times

-4

Good evening, this code works exactly the way I need to run directly in sql, but as I do for it to appear in a php page, I tried several things, I even managed to print but it does not appear right.

SELECT estado, COUNT(*) As Total FROM emailc WHERE status = 1 GROUP BY estado

Result in SQL: print sql

The closest I came to the same result as the image was with this code:

$estados = mysqli_query($conexao,"SELECT * FROM `emailc` where status = 1");

while($res = mysqli_fetch_assoc($estados)){

    $nomeEstado = $res['estado'];

    $estadoConta = mysqli_query($conexao,"SELECT COUNT(estado) AS TOTAL FROM emailc WHERE estado LIKE '%$nomeEstado%'");

    while($row = mysqli_fetch_assoc($estadoConta)){

            echo '<li>'.utf8_encode($nomeEstado).' <strong>'.$row['TOTAL'].'</strong></li>'.PHP_EOL;

But repeat the state, but do the right count as shown in the image below:

resultado no php

I’m grateful for your help.

php mysql sql

  • obviously in the last query is that the status, this is going to be difficult obviously. Besides, in there a LIKE in the query, pq does not use the = ?

  • Adding the status the result remains the same, and if I put the = instead of the like, the result is 0, it does not count how many times it has the acronym RJ for ex.

1 answer

0

---SOLVED-IT I’ll leave here the solution I found can give birth to someone.

$estados = mysqli_query($conexao,"SELECT * FROM `emailc` where status = 1");

$stateName = array();

while($Row = $estados->fetch_assoc()) { $stateName[$Row['status']][] = $Row; } foreach($stateName as $status => $values){ $stateConta = mysqli_query($connected,"SELECT COUNT(status) AS TOTAL FROM emailc WHERE status = '$status'");

while($row = mysqli_fetch_assoc($estadoConta)){

        echo '<li>'.utf8_encode($estado).' <strong>'.$row['TOTAL'].'</strong></li>'.PHP_EOL;

}}

Upshot: funcionou

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.