Returning total number of rows from an SQL query (wordpress $wpdb) ignoring the Limit

Question

Is it possible in Wordpress to return the total number of results from $wpdb->get_results ignoring the limit?

Example:

$datas = $wpdb->get_results("
  SELECT DISTINCT wp_posts.ID, wp_posts.post_title, wp_posts.post_excerpt FROM wp_posts
  INNER JOIN wp_postmeta ON
    wp_posts.ID = wp_postmeta.post_id
    WHERE (
            wp_posts.post_title LIKE '%hello%' OR
            wp_postmeta.meta_value LIKE '%hello%'
          )
          AND wp_posts.post_status = 'publish'
          AND wp_posts.post_type IN ('post', 'page')

    ORDER BY wp_posts.post_date DESC
    LIMIT 0, 24
", ARRAY_A);

Or would I need to run two queries, one with count(*) for the total number of posts and then another as the above query to get the actual post content?

@revo, I'd like to return 24 posts at a time but I'd like to know what the total count is without the limit as well, so I was wondering if that can be done in one query or would I need to run two queries — PavKR
– PavKR, Commented Jun 20, 2018 at 11:24

revo · Accepted Answer · 2018-06-20 11:55:38Z

1

You could include SQL_CALC_FOUND_ROWS option in your select query:

SELECT SQL_CALC_FOUND_ROWS ...

Then invoke FOUND_ROWS() immediately:

SELECT FOUND_ROWS();

However, it's up to you to compare performing time of running a separate query over using SQL_CALC_FOUND_ROWS option. Sometimes the former outperforms the latter.

answered Jun 20, 2018 at 11:55

revo

49k15 gold badges84 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

PavKR Over a year ago

How would I go about incorporating this functionality into $wpdb->get_results?

revo Over a year ago

After adding that option to your current select query, you would do $wpdb->get_results( "SELECT FOUND_ROWS()" );

PavKR Over a year ago

I tried this but when I add SQL_CALC_FOUND_ROWS to the first query, it returns nothing

revo Over a year ago

Did you call FOUND_ROWS() afterward?

revo Over a year ago

Adding SQL_CALC_FOUND_ROWS to the select query SHOULD NOT return anything. You have the number of total rows just after invoking FOUND_ROWS() function. Please read manual. I'm not sure about wordpress get_results method. There may be something wrong with it.

|

Collectives™ on Stack Overflow

Returning total number of rows from an SQL query (wordpress $wpdb) ignoring the Limit

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related