Reply to thread

Message

<blockquote data-quote="MayoMayn" data-source="post: 389773" data-attributes="member: 71840">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 6The output should then be:`$countItems = 8` and `$countUsers = 2`.[PHP]&nbsp; &nbsp; $stmt = $conn-&gt;pdo-&gt;prepare(&quot;&nbsp; &nbsp; &nbsp; &nbsp; SELECT COUNT(*),`user_id` FROM `items`&nbsp; &nbsp; &nbsp; &nbsp; INNER JOIN `users`&nbsp; &nbsp; &nbsp; &nbsp; ON `users`.`id` = `items`.`user_id`&nbsp; &nbsp; &nbsp; &nbsp; WHERE `base_item` = :i AND `rank` = 1&nbsp; &nbsp; &nbsp; &nbsp; GROUP BY `user_id`,`items`.`id`&nbsp; &nbsp; &quot;);&nbsp; &nbsp; $stmt-&gt;bindParam(':i', '400', $db-&gt;PARAM_INT); // item_id in `values` table&nbsp; &nbsp; $stmt-&gt;execute();&nbsp; &nbsp; if($inv = $stmt-&gt;fetch($db-&gt;FETCH_ASSOC)) {&nbsp; &nbsp; &nbsp;&nbsp; $countItems = $inv['id'];&nbsp; &nbsp; &nbsp;&nbsp; $countUsers = $inv['user_id'];&nbsp; &nbsp; }[/PHP]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`:<img src="https://i.stack.imgur.com/YaXn4.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />Table Info for `items`:<img src="https://i.stack.imgur.com/Ez2X6.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />Tried Stackoverflow, but their users are a pain in the ass.</blockquote>

[QUOTE="MayoMayn, post: 389773, member: 71840"] 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 [B]400[/B], and these 8 rows, where 2 rows of them has a `user_id` of [B]4[/B] and the existing left has a `user_id` of [B]6[/B] The output should then be: `[I]$countItems = 8[/I]` and `[I]$countUsers = 2[/I]`. [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']; } [/PHP] I have tried several methods, but I still keep getting output `2` and `2` even though the results should be `8` and `2`. [B]Table Info for `values`:[/B] [IMG]https://i.stack.imgur.com/YaXn4.png[/IMG] [B]Table Info for `items`:[/B] [IMG]https://i.stack.imgur.com/Ez2X6.png[/IMG] Tried Stackoverflow, but their users are a pain in the ass. [/QUOTE]

Verification

Reply to thread

Connect with us

Newest members