PHP FOUND_ROWS and COUNT in same SELECT

Question

I have this code:

$sql = "SELECT FOUND_ROWS() AS totalRows, COUNT(*) AS totalRefunds FROM table WHERE result = 'refunded'";
$totalRows = $conn->query( $sql )->fetch();
$totalRefunds = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0], "totalRefunds" => $totalRefunds[0] ) );

I want totalRows = 7 and totalRefunds = 1 but the above returns 0 for both. If I remove either the FOUND_ROWS() or COUNT(*) statements then the other one works. I'm guessing there's something wrong in SELECT but not sure what it is. Or maybe something else is wrong???

Thanks in advance.

http://stackoverflow.com/questions/3453809/how-to-use-mysql-found-rows-in-php — Mattt, Apr 26 '14 at 09:09

score 0 · Answer 1 · answered Apr 26 '14 at 09:11

0

To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM  table WHERE result = 'refunded'";

and then SELECT FOUND_ROWS()

answered Apr 26 '14 at 09:11

sshet

1,152
1
6
15

I already have this before it: `$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM table";`. – aphextwig Apr 26 '14 at 09:24

score 0 · Answer 2 · answered Apr 26 '14 at 11:31

0

Try this way. Assuming you run the query with SQL_CALC_FOUND_ROWS prior to run this

SELECT (@variable := FOUND_ROWS()) AS totalRows, COUNT(*) AS totalRefunds 
FROM table WHERE result = 'refunded'

answered Apr 26 '14 at 11:31

Akhil

2,602
23
36

shudder · Answer 3 · 2014-04-26T12:42:26.903

FOUND_ROWS() is used for counting rows as if LIMIT was ignored. It will not append total rows to other query result (unless you select them all, but there's no point if you only need count). Do it simple if you want this information only:

$sql = "SELECT COUNT(*) as totalRows FROM table";
$totalRows = $conn->query( $sql )->fetch();
$sql = "SELECT COUNT(*) as totalRefunds FROM table WHERE result = 'refunded'";
$totalRefunds = $conn->query( $sql )->fetch();

Other way (but different context) is to split counts into all 'result' column types (refunded/not refunded/other..) using GROUP BY so the the total rows can be obtained as sum of single counts returned.

PHP FOUND_ROWS and COUNT in same SELECT

3 Answers3