mysql: What is the ROW_COUNT() information function for SELECT?

Question

I'm looking for the built-in MySQL information function that returns the number of rows just selected, like ROW_COUNT() does for INSERT, UPDATE, etc.

In a stored procedure I wish to do this:

...
select episode_name from bestLOSTepisodes
set _episodes_found = SELECTED_ROWS();
...

I've done a fair amount of digging and have been unable to find such a function like SELECTED_ROWS().

Answer 1

You may want to use the FOUND_ROWS() function.

Example:

CREATE TABLE users (id int, name varchar(20), age int);

INSERT INTO users VALUES (1, 'Bob', 19);
INSERT INTO users VALUES (2, 'Jack', 25);
INSERT INTO users VALUES (3, 'Paul', 15);
INSERT INTO users VALUES (4, 'Steve', 35);
INSERT INTO users VALUES (5, 'Mark', 17);

SELECT * FROM users WHERE age > 18;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | Bob   |   19 |
|    2 | Jack  |   25 |
|    4 | Steve |   35 |
+------+-------+------+
3 rows in set (0.00 sec)

SELECT FOUND_ROWS() as num_of_rows_found;
+-------------------+
| num_of_rows_found |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.00 sec)

Answer 2

You have to FOUND_ROWS with SQL_CALC_FOUND_ROWS in your query : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows