title: PHP/SQL - Returning query results and counts
body: <p>SQL/PHP noob.  I've looked up a number of questions, but none seem to help get me what I need.</p>
  <p>I have a front-end for a database that runs SQL queries based off the state of a checkbox.  I want the SQL query to return both the results and the result count (into a variable) with a single query.</p>
  <p>If I run:</p>
  <pre><code>SELECT DISTINCT
  event.event_id as 'Event ID',
  event.event_group_id as 'Group ID',
  abs_path.abs_path as 'Path\Filename',
  
  FROM 
  event.event_group
  LEFT JOIN event.event using (event_group_id)
  LEFT JOIN file_info.abs_path using (abs_path_id)
  
  WHERE event.timestamp &gt;= DATE_SUB(CURDATE(),INTERVAL 1 DAY)
  LIMIT 40
  </code></pre>
  <p>I get all the results to the query.  But if I run:</p>
  <pre><code>SELECT DISTINCT
  count(event.event_id) as 'rowcount',
  event.event_id as 'Event ID',
  event.event_group_id as 'Group ID',
  abs_path.abs_path as 'Path\Filename',
  
  FROM 
  event.event_group
  LEFT JOIN event.event using (event_group_id)
  LEFT JOIN file_info.abs_path using (abs_path_id)
  
  WHERE event.timestamp &gt;= DATE_SUB(CURDATE(),INTERVAL 1 DAY)
  LIMIT 40
  </code></pre>
  <p>I only get a single result with 'rowcount' being the first column, but if I take it out, I get all my results:</p>
  <p><a href="https://i.stack.imgur.com/n12Yb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/n12Yb.png" alt="sql" /></a></p>
  <p>I want to be able to get the total row count and save it into a variable, while also pulling down the results to display on the screen.</p>
  <p>I'm using the following to fetch the query:</p>
  <pre><code>$result = $database-&gt;query($sql);
  </code></pre>
  <p>How can I do the query and the count at the same time while saving just the total results into $rowcount ?  If you look at the bottom left corner of the 2nd image, SQL Query Browser displays the total number in the query.  I want that number.  How can I get it?</p>
  <p>Thanks</p>