选择db列，其中包含同一行中已定义的列

Question

It might be a bit confusing but let me explain.

So I have this DB

ID | username  | status
1  | Bob       | On
2  | James     | On
3  | Jack      | Off
4  | Bob       | Off
5  | Arthur    | On

How can I echo all the names which include status on? I tried something like

$reponse = $pdo->query('SELECT username, COUNT(1) as CNT FROM   users GROUP BY  status;');
$donnees = $reponse->fetch();
foreach ( $reponse as $key => $value ) {
   echo '<li><a href="#"><i class="fa fa-circle-o">  '.$value['username'].'</i></a></li>';
}

Expected result

Echo: Bob James Arthur

But it only prints out the 1st ID who is offline (in this case Jack).

Thank you

PS: I'm learning PHP/MySQL at this moment so I feel kinda lost, If you share an answer please explain why you use your code and where I was wrong thank you.

Answer 1

As per your expected result, you don't need a group by here:

$reponse = $pdo->query('SELECT username FROM   users   WHERE  status="On"');

This will give you the result as :

username    
----------
Bob     
James   
James

If you want to count them:

$reponse = $pdo->query('SELECT username,count(1) as cnt FROM   users   WHERE  status="On" group by username');

username    cnt
----------------
Bob     1
James   2