PHP MySQL combine multiple fetchcolumn into a single query

Question

I've currently got a table with users and variable user status (1, 2, 3, 4, 5 etc). I am currently calculating the number of users in each status separately

$query = "SELECT COUNT(*) FROM users WHERE status = 1";
$result = $pdo->query($query);
$users1 = $result->fetchColumn();

$query = "SELECT COUNT(*) FROM users WHERE status = 2";
$result = $pdo->query($query);
$users2 = $result->fetchColumn();

etc

I could get a fetch, then loop through all the results and add each status but I was wondering if there is a better way to do it with a query?

ScaisEdge · Accepted Answer · 2017-04-06 16:47:39Z

2

You can use group by and get the list with the count

SELECT status, COUNT(*)  as my_count FROM users group by status;

NB with this you can't use fetchColumn .. but fetchRow .and access by key

answered Apr 6, 2017 at 16:47

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bill Over a year ago

I'll still have to loop through the results to get the $users1/2/3 etc right?

ScaisEdge Over a year ago

yes .. correct you must loop over the result fof get each row and avcces by key 'status',,,and ''my_count' to the values of each row ....(but you have select row for each status ...not for each $user) ..

krishn Patel · Accepted Answer · 2017-04-06 16:47:43Z

1

you jsut need to add status as group by

SELECT COUNT(*),status FROM users group by status

answered Apr 6, 2017 at 16:47

krishn Patel

2,5991 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

PHP MySQL combine multiple fetchcolumn into a single query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related