0

I have a custom WP_Users_Query that I have been using on a site.

It has worked fine for a while. I went to add some pagination to the query using the number parameter as per the docs here. But for some reason, when I add that parameter I get an SQL error, unless I set it to -1 where it returns everything.

Here is my code that works:

$user_fields = array( 'ID', 'display_name', 'user_email' );
$args = array(
    'role__in' => array('Subcriber', 'Editor'),
    'meta_query' => array(
        array(
            'key' => 'list_in_directory',
            'value' => 1,
            'compare' => '='
        )
    ),
    'fields' => $user_fields,
    'paged' => get_query_var('paged')
); // End Args

if ($args){
    $user_query = new WP_User_Query( $args );
}

If I add the line:'number' => '2' to my args array (I have tried it both as a string and an integer, both with the same result) I get this error in my log:

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 '-2, 2' at line 15 for query SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.display_name,wp_users.user_email FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) WHERE 1=1 AND ( ( ( ( wp_usermeta.meta_key = 'list_in_directory' AND wp_usermeta.meta_value = '1' ) ) AND ( ( ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"Subcriber\"%' ) OR ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"Editor\"%' ) ) ) ) ) ORDER BY user_login ASC LIMIT -2, 2 made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/pand/page-templates/membership-directory.php'), WP_User_Query->__construct, WP_User_Query->query

Also, I have tried using different numbers in place of 2 all with the same result. The only time I don't get the error is if I set it to -1. Then it works just fine(but no pagination).

I'm scratching my head on this one so any help would be much appreciated!

0

I was able to reproduce the error. Here's what I think is happening.

'number' defines the number of users per page, and 'paged' tell the query which page to return.

For example:

$args = array('number' => 2, 'paged' => 2)

will return the second page at 2 per page.

If you don't have a query string set, you'll get an error because it's trying to show 2 users from a page that is undefined.

The solution is to leave out the 'paged' parameter and let it start on page 1 (the default).

My experience ends here with regard to how to implement pagination, but it looks as if it's a function of the 'offset' parameter like:

'offset' => get_query_var($paged) * $number

(assuming the query var exists)

  • It seems that the paged argument and the get_query_var is what is causing the error. I'm still working on why and what the solution is. Thanks for the help pointing me in the right direction! – Dustin Yoxall Sep 8 '16 at 19:52
0

Ok, so I figured out what my issue was. The issue was with the paged argument. I think it was because I was checking for it but in the event it wasn't found it was causing issues. So here is the code I have now that is working.

$user_fields = array( 'ID', 'display_name', 'user_email' );
$paged = ( get_query_var('paged') ) ? get_query_var('paged') : 1;
$args = array(
    'number' => '1',
    'role__in' => array('Subcriber', 'Editor'),
    'meta_query' => array(
        array(
            'key' => 'list_in_directory',
            'value' => 1,
            'compare' => '='
        ),
    ),
    'fields' => $user_fields,
    'paged' => $paged
); //End Args
if ($args){
    $user_query = new WP_User_Query( $args );
}

You will notice on the second line I am checking for the paged query var and setting it to 1 if it's not found. That seems to have fixed the SQL error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.