3

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?

Community
  • 1
  • 1
emersonthis
  • 32,822
  • 59
  • 210
  • 375
  • Are you binding the actual value from the return object? What php mysql class are you using?. – Amelia Jan 06 '13 at 01:36
  • have you tried it as 'SELECT SQL_CALC_FOUND_ROWS, FOUND_ROWS() AS NUMOFROWS something FROM your_table WHERE whatever; ' – mamdouh alramadan Jan 06 '13 at 01:37
  • Does `$rows = $wpdb->get_results( "SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS();" );` get you an error? – cheesemacfly Jan 06 '13 at 01:38
  • what about using [$wpdb->query()](http://codex.wordpress.org/Class_Reference/wpdb#Run_Any_Query_on_the_Database) instead which should return an integer of the selected rows? – 32bitfloat Jan 06 '13 at 01:47
  • @32bitfloat: that worked. Nice idea. This will work as a backup if I can't get the FOUNT_ROWS() to work. I suspect that should be slightly faster, right? – emersonthis Jan 06 '13 at 02:03
  • @cheesemacfly: I turns out that yes, it does: WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS()' at line 8] – emersonthis Jan 06 '13 at 02:04
  • @mamdouhalramadan: I haven't tried that yet... – emersonthis Jan 06 '13 at 02:05
  • @Hiroto: I'm using this: http://codex.wordpress.org/Class_Reference/wpdb – emersonthis Jan 06 '13 at 02:05
  • @Emerson - can you try it? – mamdouh alramadan Jan 06 '13 at 02:07
  • @mamdouhalramadan It didn't work... `WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' FOUND_ROWS() AS NUMOFROWS FROM wsat_ib' at line 1] SELECT SQL_CALC_FOUND_ROWS, FOUND_ROWS() AS NUMOFROWS FROM wsat_ib; ` – emersonthis Jan 06 '13 at 02:16
  • sry I cannot say which is the fastest alternative. You may want to check that by using microtime(true) before and after the methods and compare. – 32bitfloat Jan 06 '13 at 02:19
  • @Emerson - sorry for bother, but the problem is that I can't test. anyway. can you try adding it like this 'SELECT SQL_CALC_FOUND_ROWS ,(SELECT FOUND_ROWS() )' ? – mamdouh alramadan Jan 06 '13 at 02:22

3 Answers3

5

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)
Community
  • 1
  • 1
hakre
  • 193,403
  • 52
  • 435
  • 836
  • This works too. How does this compare to @32bitfloat's idea in the comments above? I'm mostly interested in performance. I also notice query() returns and int while get_var() returns a string. – emersonthis Jan 06 '13 at 02:12
  • @Emerson: Read the manual, you should aim for understanding why query returns an int and get_var not from code: http://codex.wordpress.org/Class_Reference/wpdb - might look a bit much to read, just check these two methods after reading the introduction. – hakre Jan 06 '13 at 02:25
  • Sorry for not being more clear. I'm familiar with those methods. I just mentioned it to point it out for future readers. My real concern is the performance issue. Originally I was trying to avoid making another query just to get the total rows, and so now that I am doing this, I'm trying to figure out the smartest way to do it. – emersonthis Jan 06 '13 at 15:19
  • Which issue do you have with the performance? Something is slow for you? – hakre Jan 06 '13 at 15:32
  • There is no problem currently, but this project needs to be able to hold thousands and thousands of results, so I'm trying to make a smart plan from the beginning. In other words, it doesn't feel slow when it's just me on my development server with a small test DB. But I want to make sure I'm not going to have problems later when it goes live for hundreds of users with a huge db. Maybe it's still fine, but I'm not very experienced with optimizing MySQL so it's something I'm thinking about. – emersonthis Jan 06 '13 at 15:38
0

Easy. Use this:

SELECT FOUND_ROWS() as total;

And get it in PHP:

echo $query[0]->total;
Rafel
  • 1
0

Try this:

SELECT count(1) AS rowcount
FROM table
Robert Columbia
  • 6,313
  • 15
  • 32
  • 40
user10235444
  • 1