Jordan Ramstad
Jordan Ramstad

Reputation: 179

Top level MySQL statistics

Have not been able to find any information on this, I could do this in its own but I feel keeping it in the query might be the best option, if its possible.

Basically I want to try to add a top level "statistics" portion of a query.

So when I get the results I will see it like so

num_rows = 900
distinct_col = 9
results = array()

This way I can loop the results normally, and then pull out information that I would only need once outside of it. Is this possible?

EDIT: I am not looking for the normal mysql statistics like num_rows exactly. But in a case where lets say you limit the results to ten, num_rows would return 10, but you want total results, so 900. In most cases I would just use another query and look just for the amount, however combining it all into one query logically seems faster for me. There is also more then just the num_rows I may need, say they are all products and have a specific category, I would need to count the amount of categories all items fall under. So looping the raw results when there is only one result for those columns is sillyness.

EDIT 2: To clarify further I need to get some counts on some columns, and maybe a min-max result on a join. Having it return on every loop would work, but the same exact return uselessly returning on every loop when its only needed once does not seem logical. I am no MySQL expert and am mainly just trying to make sure I come up with the most logical and fastest method to get the required data.

Here is a PHP return example:

array(
  [num_rows] => 900,
  [categories] => 9,
  [min_price] => 400,
  [max_price] => 900,
  [results] => array(
   [0] => //row array
   [1] -> //row array
  )
);

Mysql returns its default num rows before you "fetch" the results, having custom results added there may be sufficient.

Upvotes: 0

Views: 65

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157880

Dunno why do you need that but that's very easy to get

Assuming you are using safeMysql (though you can use whatever way to get data into array)

$results = $db->getAll("SELECT * FROM t");
$num_rows = count($results);
$num_cols = count($results[0]);

that's all

I am mainly just trying to make sure I come up with the most logical and fastest method to get the required data.

Yes, you are.
Nothing wrong with getting aggregated data with every loop.

As for the count beyond LIMITs - when you need it, you can use mysql's SQL_CALC_FOUND_ROWS / FOUND_ROWS() feature

Upvotes: 1

Related Questions