title: MySQL - Is it possible to merge select queries, so it doesn't have to be executed in while loop?
body: <p>So i have a query which will get all information about matched rows where items are not visible. </p>
  
  <p>Now inside while loop i had to use select count query to count how many not visible items every user has and it takes too much time if there are so many results in a loop. </p>
  
  <p>Is there a way to query it without running select query inside while loop?</p>
  
  <p>For example:</p>
  
  <pre><code>SELECT  categories.name, 
          quality.name, 
          users.regdate,
          categories.name,
          items.name, 
          items.visible,
          items.owner,
          users.username
          FROM items
          LEFT JOIN users ON items.owner = users.id 
          LEFT JOIN categories ON items.category = categories.id 
          LEFT JOIN quality on items.category_quality = quality.id
          WHERE items.visible = 'no'
  </code></pre>
  
  <p>Now in while loop to get number of items, i had to query</p>
  
  <pre><code>while($row = mysqli_fetch_assoc($sql))
  {
  $all_items_by_user = "SELECT COUNT(items.name)
  FROM items
  WHERE items.owner = ".$row['owner']."";
  
  $visible_items_by_user = "SELECT COUNT(items.name)
  FROM items
  WHERE items.owner = ".$row['owner']." AND items.visible = 'yes'";
  
  // rest of code here.
  // when there are 200 items in loop executing time goes crazy 
  // (6+ secs, where with 1 item it's around 0.05)
  }
  </code></pre>
  
  <p><strong>Edit with example:</strong></p>
  
  <p>Some people asked to see what final code would be, I'll try to give the best i can.</p>
  
  <p>Ignore left joins as this is not relavent, I just added those from the whole query.</p>
  
  <pre><code>Id | item name | owner | Visible
  1 | Item 1 | 2 | no
  2 | Item 2 | 2 | yes
  3 | Item 3 | 2 | no
  4 | Item 4 | 2 | no
  5 | Item 5 | 2 | no
  6 | Item 6 | 3 | no
  7 | Item 7 | 3 | no
  8 | Item 8 | 3 | no
  9 | Item 9 | 4 | no
  10 | Item 10 | 4 | no
  </code></pre>
  
  <p>In while loop for every user it should count how many rows user owns.</p>
  
  <p>For example:
  user 2 owns 5 rows total and 4 hidden, ($all_items_by_user will count everything, while $visible_items_by_user will count only 1)
  user 3 owns 3 rows, 
  user 4 owns 2 rows.</p>
  
  <p>How to count per user items because if i do it outside of while loop, it will count only for first user, not for every single user.</p>
  
  <p>The final result should be</p>
  
  <pre><code>while($row = mysqli_fetch_assoc($sql))
  {
  $all_items_by_user = "SELECT COUNT(items.name)
  FROM items
  WHERE items.owner = ".$row['owner']."";
  
  $visible_items_by_user = "SELECT COUNT(items.name)
  FROM items
  WHERE items.owner = ".$row['owner']." AND items.visible = 'yes'";
  
  echo $row['owner'] . "(Visible items: number | All items: number";
  // Output: 2 (Visible items: 1 | All items: 5)
  }
  </code></pre>