CODEWITHSUNDEEP
phpmysql
aphextwig
aphextwig

Reputation: 553

PHP Mysql total rows with FOUND_ROWS() and WHERE condition

I'm getting x amount of rows from the db which are totalled with the 'totalRows'. From the rows I'd also like to total just those where the result column is equal to 'win'. I'm not seeing an answer either with the echo of 'totalWins'.

Any ideas what I'm doing wrong?

Thanks in advance.

public static function getList( $numRows=10000, $order="id DESC" ) {
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM table
        ORDER BY " . mysql_escape_string($order) . " LIMIT :numRows";

$st = $conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->execute();
$list = array();

while ( $row = $st->fetch() ) {
  $article = new Article( $row );
  $list[] = $article;
}

// Now get the total number of rows that matched the criteria
$sql = "SELECT FOUND_ROWS() AS totalRows";
$totalRows = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );

// Now get the total number of rows WITH CONDITION matched
$sql = "SELECT FOUND_ROWS() AS totalWins WHERE result = 'win'";
$totalWins = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalWins" => $totalWins[0] ) );

Upvotes: 1

Views: 1831

Answers (1)

Air
Air

Reputation: 8615

Your statement SELECT FOUND_ROWS() AS totalWins WHERE result = 'win' is going to produce an error, not a result set. FOUND_ROWS() simply gives you an integer, it doesn't give you access to the table(s) in the previous query to do further filtering. Since you don't refer to any table in the statement, the column result is unknown to MySQL.

You can get both of your desired counts in one query, like so:

SELECT FOUND_ROWS() AS totalRows, COUNT(*) AS totalWins FROM table WHERE result = 'win';

The FOUND_ROWS() function will look at SQL_CALC_FOUND_ROWS from the previous query, and return an integer. The rest of the query works just as it would without including the result of FOUND_ROWS() as a field in the result set.

Note that when you ask for a constant value (like the result of FOUND_ROWS()) as a field in your query, it will repeat for all rows in the query. On the other hand, when you ask for the result of an aggregate function (like COUNT()), your query result will be limited to just one row. These are potential "gotchas" - if it's not clear exactly what I mean here, try running these queries and examining their result sets (in order):

SELECT COUNT(*), result FROM table LIMIT 1000;
SELECT FOUND_ROWS(), result FROM table LIMIT 1000;

You're also using PDO with a function from the deprecated mysql_ extension (mysql_escape_string()); better to stick with one API instead of mixing and matching. The documentation lists PDO::quote() as an alternative:

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM table 
        ORDER BY " . $conn->quote($order) . " LIMIT :numRows";

Note that the documentation also makes the following recommendation:

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

Upvotes: 1

Related Questions