Find 5 greatest totals after summing (quantity x value) for each qualifying user

I currently have a table which displays the inventory values of users. It works, but it displays all users and their inventory values in a random order. I’m wanting to limit the table to the top 5 descending inventory values only.

This data cuts across three tables: DB Fiddle

1. Users
2. Inventories (contains a row for each item a user has in their inventory)
3. Items (contains the value of the items)

echo "<table>
<th>Inventory Value</th>
<th>Username</th>";

// Choose the users we want (verified users).
$refineUsers=$db->query("SELECT userID FROM users WHERE accountVerified = 'true'");
while($users=$db->fetch_row($refineUsers)) {
  // Fetch the inventories of the users we want.
  $fetchInventories=$db->query("SELECT * FROM inventories WHERE userID = '".$users['userID']."'");
  $totalInventoryValue=0;
  while($inventories=$db->fetch_row($fetchInventories)) {
    // Fetch the values of the items.
    $fetchItemsData=$db->query("SELECT value FROM items WHERE itemID = '".$inventories['itemID']."'");
    while($items=$db->fetch_row($fetchItemsData)) {
      // Calculate the values of the various items within the user's inventory.
      $totalItemsValue=($items['value'] * $inventories['quantity']);
      // Calculate the total value of the user's inventory.
      $totalInventoryValue+=$totalItemsValue;
    }
  }
  // Display the values of each user's inventory.
  echo "<td>".money_format($totalInventoryValue)."</td>";
  echo "<td>".username($users['userID'])."</td>";
  echo "<tr>";
}

echo "</table>";

The output is something like:

Inventory Value	Username
120000	Account1
112000	Accounts2
70000	Account3
169000	Accounts5

I’m guessing this could be done with a JOIN query, but I’m not very experienced and haven’t done one that cuts across three tables before. I don’t know how I can use these nested queries to get the top 5 inventory totals and arrange them in descending order.

Answer

1. Join the three table is accordance with the rules in your original nested queries.
2. Group the data on username values.
3. This will create AGGREGATE data which will need to be organized within the SUM() function so that the item quantities associate with the correct item value.
4. Now it is trivial to apply your ORDER BY and LIMIT rules.

SQL: (Demo)

SELECT username,
       SUM(
           IF(
               inventoryItemID = itemID,
               inventoryQuantity * itemValue,
               0
           )
       ) AS total
FROM users
JOIN inventory ON userID = inventoryUserID
JOIN items ON inventoryItemID = itemID
WHERE accountVerified = 'true'
GROUP BY username
ORDER BY total DESC
LIMIT 5