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

Question

I am looking for a built-in MySQL info function that returns the number of rows selected, e.g. ROW_COUNT () for INSERT, UPDATE, etc.

In the stored procedure, I want to do this:

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

I did quite a bit of digging and couldn't find a function like SELECTED_ROWS ().

Answer 1

You can use the function FOUND_ROWS().

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

In the query, you must FOUND_ROWS with SQL_CALC_FOUND_ROWS: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Answer 3

FOUND_ROWS().

CREATE TABLE Emp(EmpID int,EMPNAME varchar(20),SALARY int);
INSERT INTO Emp VALUES (1,'Astin',6500);
INSERT INTO Emp VALUES (2,'Blade',4500);
INSERT INTO Emp VALUES (3,'Cabal',6500);
INSERT INTO Emp VALUES (4,'Brant',5200);
INSERT INTO Emp VALUES (5,'Sunny',7000);
SELECT * FROM EMP WHERE SALARY=6500; 
SELECT FOUND_ROWS() as SelectedRowCount;