MySql row count selection as optional column?

Question

I need a MySql statement that will select all rows as well as the number of volumes.

I used

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

... trying to add a counter:

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

... but this only returns one row.

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

Answer 1

I need a MySql statement that will select all rows as well as the number of volumes.

Literally, this is not possible. The result of the SQL query is a (virtual) table; the column in each row in this result table contains a value that is associated only with that row, and the rows act independently of each other.

rowcount , , , , . ( )

, :

:

• , .
• , , LIMIT ( 5 )

( )

. , COUNT (*) , - MySql ? .

COUNT . , . , mysql SELECT. , , , MySQL .

COUNT (*) , .

, , , . !

, .

, PHP, , MySQL limit, php mysql_num_rows (http://www.php.net/manual/en/function.mysql-num-rows.php) mysql_query.

, LIMIT, 2 : -, , , MySQL FOUND_ROWS. (. http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows)

:

$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()');

SQL_CALC_FOUND_ROWS MySQL LIMIT, FOUND_ROWS() . :

• mysql_query
• mysql_query

, : LIMIT , . LIMIT . , , , LIMIT . , ORDER BY. (http://dev.mysql.com/doc/refman/5.5/en/select.html)

Answer 2

, : . , , :

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

Answer 3

, . .

( , ):

SELECT COUNT(*) AS total FROM posts;

, :

SELECT * FROM posts LIMIT 0, 5

, , , .

, , , , , ( , ?)

, , , . , .

, , , , , .

:

post_id | comment_id
1       | 1
1       | 2
2       | 3
3       | 4
3       | 5

, , , :

SELECT COUNT(*) AS total FROM posts;

:

SELECT id FROM posts limit 0, 5;

, , :

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 request will not work in the above problem. But I'm just trying to illustrate why it might be useful to get the total in a separate request.