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!
-- 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:
(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:
mysql_query calls should be executed over the same connectionmysql_queryOh, 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)
ORDER BY clause. In fact, I would even go as far as to say, "only by using an ORDER BY clause". ;) Just being a little sarcastic, sorry. I find your answer well elaborated and educating, +1.
GROUP BY allows one to reference columns by their aliases (instead of real names), and also by their position numbers. Both things are allowed only in ORDER BY in most, if not all, other RDBMSes. So, if MySQL GROUP BY allows that, it seems only consistent that it also accepts ASC & DESC and assumes the relevant functionality.
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");
I think you are over-complicating this issue. I don't see a problem with using two queries.
First query to get total posts (I'm assuming you want the total in the whole table):
SELECT COUNT(*) AS total FROM posts;
And a second, to query for your posts:
SELECT * FROM posts LIMIT 0, 5
The reason is that it's not always a bad thing to retrieve data using more than one query, especially when it makes sense.
If you were to add it as an additional column using say a subquery, you'd have the total in each row, but you'd still basically be running two queries (or would the subquery run for every row, I can't remember?)
It also provides more flexibility to get the count first since some queries will return the wrong total count depending on how complex your query is. And you may need to modify the count query so you get the correct total and can paginate correctly.
For example in the case of a one to many query, say you want to get posts and comments, you'd get the wrong count since the post would get duplicated for each comment for that post.
Abbreviated example:
post_id | comment_id
1 | 1
1 | 2
2 | 3
3 | 4
3 | 5
So to do it correctly you'd first need to run the following query to get total posts:
SELECT COUNT(*) AS total FROM posts;
And then you'd need to run:
SELECT id FROM posts limit 0, 5;
And finally you'd run:
select p.id, c.id from posts p left join comments c on c.post_id = p.id where id in(id list from above query)
Obviously your query wouldn't run into the above problem. But I'm just trying to illustrate why it may be good to get the total count in a separate query.
mysql_num_rowsmysql_num_rowsis fine) or number of rows in whole table.