CODEWITHSUNDEEP
phpmysqlsqlwordpress
emersonthis
emersonthis

Reputation: 33378

MySQL how to store FOUND_ROWS() in a PHP variable

I'm trying to get the total number of rows returned by my query before I limit it. I'm attempting to use the advice found here, which uses: SQL_CALC_FOUND_ROWS and then FOUND_ROWS() in a second query;

They give this example:

SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever;
SELECT FOUND_ROWS( ) ;

What I can't figure out is how to capture the second value from FOUND_ROWS() into a PHP variable. I'm working within Wordpress, so I'm using the global $wpdb to handle MySQL interactions. Here's a simplified version of what I'm trying:

$rows = $wpdb->get_results( "SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever;" );
$count = $wpdb->get_results( "SELECT FOUND_ROWS();" );

The first query works, but $count is always empty. If I run them both in phpMyAdmin, the queries run without error.

I've also tried this:

$rows = $wpdb->get_results( "SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever;
SET @count = FOUND_ROWS();" );

...but it fails entirely.

What am I doing wrong?

Upvotes: 4

Views: 3225

Answers (3)

user10235444
user10235444

Reputation: 1

Try this:

SELECT count(1) AS rowcount
FROM table

Upvotes: 0

Rafel
Rafel

Reputation: 1

Easy. Use this:

SELECT FOUND_ROWS() as total;

And get it in PHP:

echo $query[0]->total;

Upvotes: 0

hakre
hakre

Reputation: 198118

If this is Wordpress, you could do alternatively for the second statement, otherwise you were asking for an array an not the number:

$count = $wpdb->get_var('SELECT FOUND_ROWS()');

Also take care that the database class of wordpress is open to change of any query due to filters and it suppresses errors by default doing the query and fetching from the resultset.

You should enable screaming to see if you run into specific errors.

  • $wpdb->show_errors = true shows errors automatically, if WP_DEBUG is set to true. (ref)

Upvotes: 6

Related Questions