0

I have a chart where I want to display numbers of users banned and activated. I want to do it as simple as using php to count numbers of rows where banned = 1 and another where both activated = 1 and banned = 0.

Most of the solutions I've found dont work for what ever reason, some is outdated, and some I cant figure out. I use PHP7 but dont know how much thats gone change any solutions other than the oldest versions with mysqli and mysql

1.

 $result = $db->query("SELECT COUNT(*) AS banned FROM users WHERE banned = '1'");
    $row = $result->fetch_assoc();
    echo $row['banned']." banned users.";

    $result->close();

2.

 $num_banned = $db->query("SELECT COUNT(*) FROM `users` WHERE `banned` = `1`");
    $row = $num_banned->fetch_row();
    echo '#: ', $row[0];

  1. $num_banned = mysqli_query($db, "SELECT * FROM users WHERE banned='1'"); $num_rows = mysqli_num_rows($num_banned);

chris85
  • 23,846
  • 7
  • 34
  • 51
martin j
  • 131
  • 4
  • 13

1 Answers1

1

You should be able to use cases to count your records.

select 
count(case when (banned = 1 and activated = 1) then 1 end) as activeban,
count(case when (banned = 0 and activated = 1) then 1 end) as activenoban,
count(case when (banned = 1) then 1 end) as banned
from users;

Demo: http://sqlfiddle.com/#!9/450d04/13

Running that query you should have, counts for activated users that are banned as activeban, inactivated users that are banned as activenoban, and a count of all banned users banned.

chris85
  • 23,846
  • 7
  • 34
  • 51
  • Why `(select 1)` instead of just `1`? – Barmar Oct 21 '16 at 22:28
  • after trying it out (atleast how I understood it). i get an error `mysqli_query() expects parameter 1 to be mysqli, object given in` – martin j Oct 21 '16 at 22:41
  • @Barmar I was getting errors on SQLFiddle when running it earlier but it works now there. – chris85 Oct 22 '16 at 00:16
  • @martinj Whats your PHP? Example 1, 2, or 3 (note in 3 you've switched to procedural). – chris85 Oct 22 '16 at 00:19
  • @chris85 Those are the examples I've used. All I want to do is to count rows where `x = 1`, I dont have a php code for it. I had some problems making number 3 a full "code" `ctrl + k` stopped working for some reason – martin j Oct 22 '16 at 11:52
  • You only need to execute this once. Which example did you use and get that error on? Add you current execution to the question. Yea, the code formatting sometimes faults with ordered lists. – chris85 Oct 22 '16 at 14:13