MySql selecting count of rows as extra column?

Question

I need a MySql statement that will select all the rows, as well as how many total rows there are.

I was using

mysql_query("SELECT * FROM posts LIMIT 0, 5");

...trying to add the count:

mysql_query("SELECT *, COUNT(*) AS total FROM posts LIMIT 0, 5");

...but that only returns a single row.

Also, if there is a better way to get the total than to add an extra column to each row, then I would like that instead. Thank you!

Answer 1

I'm not sure on which total you are thinking: total currently selected or number of all rows in whole table. This returns numbers of all rows in whole table, of course in each row same value:

mysql_query("SELECT *, (select COUNT(*) from posts) AS total FROM posts LIMIT 0, 5");

Answer 2

-- Please see @Ittai 's comment below. The proposed solution relies on a feature that has been deprecated -- https://dev.mysql.com/worklog/task/?id=12615

I need a MySql statement that will select all the rows, as well as how many total rows there are.

When taken literally, this is not possible. The result of a SQL query is a (virtual) table; the column in each row in that result table provides a value that is associated with that row only, and the rows are in effect independent of each other.

There are many ways to grab the rowcount of the entire result, but it's either done in two statements or with a query that is conceptually different from what you have. (Solutions below)

There is one aspect in your original question that could be interpreted in multiple ways:

as well as how many total rows there are

This could mean either:

1. Count each row returned in the result.
2. Count each row that would have been returned if it weren't for the LIMIT clause (which truncates the result to 5 rows in this case)

(I'll come up with answers for both below)

but that only returns a single row. I'm guessing it's because COUNT(*) is going to be the same for each row, and for some reason, MySql is only returning rows with unique values for it? I have no clue.

COUNT is an aggregate function. By using an aggregate function, you're asking the database to bunch up groups of rows and project some aspects of it into a single row. What is confusing is that mysql also you to mix non-aggregate and aggregate expressions in the same SELECT list. Most other databases don't allow this and give you an error for this, but alas MySQL does not.

But COUNT(*) does still however aggregate all rows into a single row, that represents the entire group of rows.

Also, if there is a better way to get the total than to add an extra column to each row, then I would like that instead. Thank you!

yes, there are several ways.

If you want to get the number of rows returned to PHP, so, after MySQL applied its limit clause, I suggest simply calling the php function mysql_num_rows (http://www.php.net/manual/en/function.mysql-num-rows.php) after doing the mysql_query call.

If you want to get the number of rows that would have been returned in absence of the LIMIT clause, I suggest doing it in 2 steps: first, execute a slighly modified version of your original query, and then, immmediately after that, call MySQL's FOUND_ROWS function. (see http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows)

It would look like this:

$result = mysql_query('SELECT SQL_CALC_FOUND_ROWS * FROM posts LIMIT 0, 5');
//do stuff with the result, but don't do any other queries

//get the total number of rows (disregarding the LIMIT clause) 
$result = mysql_query('SELECT FOUND_ROWS()');

The SQL_CALC_FOUND_ROWS modifier tells MySQL to keep track of the total number of rows before applying the LIMIT clause, and FOUND_ROWS() returns that number. Keep 2 things in mind:

1. both these mysql_query calls should be executed over the same connection
2. Don't execute another query inbetween these calls to mysql_query

Oh, final note: when using LIMIT, you typically want the results to be ordered in a particular way. Usually people use LIMIT for pagination. If you don't order the rows, the order is indeterminate and subsequent queries may return rows already returned by previous statements, even if the LIMIT offset is different. You can explicitly order the result by using an ORDER BY clause. (http://dev.mysql.com/doc/refman/5.5/en/select.html)