PHP MySQL Count rows from INNER JOIN - GROUP BY

MayoMayn

BestDev
Oct 18, 2016
1,423
683
What I'm trying to do here, is I want to output the count of the `id` and `user_id` columns from `items`, by users which have the rank `1`.
Let's say there's 8 rows in `items` which have a `base_item` of 400, and these 8 rows, where 2 rows of them has a `user_id` of 4 and the existing left has a `user_id` of 6
The output should then be:
`$countItems = 8` and `$countUsers = 2`.
PHP:
    $stmt = $conn->pdo->prepare("
        SELECT COUNT(*),`user_id` FROM `items`
        INNER JOIN `users`
        ON `users`.`id` = `items`.`user_id`
        WHERE `base_item` = :i AND `rank` = 1
        GROUP BY `user_id`,`items`.`id`
    ");
    $stmt->bindParam(':i', '400', $db->PARAM_INT); // item_id in `values` table
    $stmt->execute();
    if($inv = $stmt->fetch($db->FETCH_ASSOC)) {
       $countItems = $inv['id'];
       $countUsers = $inv['user_id'];
    }
I have tried several methods, but I still keep getting output `2` and `2` even though the results should be `8` and `2`.

Table Info for `values`:
YaXn4.png


Table Info for `items`:
Ez2X6.png

Tried Stackoverflow, but their users are a pain in the ass.
 

Users who are viewing this thread

Top