$wpdb->get_results returns empty array if called more than once

  • briancostea

    (@briancostea)


    10 years ago

    If I call $wpdb->get_results() twice in a row it returns an empty array the second time. I know the SQL is good b/c it is successful.

    $totalsdata = $wpdb->get_results( $wpdb->prepare( "call dl_db_totals('%s', '%s', %d);", $startdate, $enddate, $current_user->ID ) );
print_r($totalsdata);
$totalsdata = $wpdb->get_results( $wpdb->prepare( "call dl_db_totals('%s', '%s', %d);", $startdate, $enddate, $current_user->ID ) );
print_r($totalsdata);

    The first result prints a full array of values, the second prints an empty array (i.e. Array ()).

    The context for this when a you need to call $wpdb->get_results() in a shortcode and the author includes more than one shortcode in a post.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter briancostea

    (@briancostea)

    Sorry, my question is why does this happen? Does WP limit the number of get_results() calls?

    Moderator bcworkz

    (@bcworkz)

    WP has no such limit. All WP really does is pass your query string to the PHP function mysqli_query(). If you are getting inconsistent results, I would suspect something in your dl_db_totals() definition.

    Thread Starter briancostea

    (@briancostea)

    Thanks, bcworkz. To verify that it was not the sproc, I created this one:

    CREATE DEFINER=root@localhostPROCEDUREsimple_test`()
    BEGIN
    select * from wp_users
    END

    Then I called it twice in a row using get_results():

    $totalsdata = $wpdb->get_results( “call simple_test()” );
    print_r($totalsdata);
    $totalsdata = $wpdb->get_results( “call simple_test()” );
    print_r($totalsdata);`

    And I get the same error. I did a little more research and this appears to be a bug in the $wpdb class when calling sprocs. Even the trying to flush() the results doesn’t help. The root cause is that a sproc returns a minimum of 2 results set and WP doesn’t clear them all out causing the error.

    *Note: if you set debug = true in the wp-config.php file you can see the actual php error.

    Moderator bcworkz

    (@bcworkz)

    Yes I see now. It’s not exactly a bug, more like $wpdb is not set up to handle multiple result sets, it’s just not that sophisticated. If you made two mysqli_query() calls in a row, you get the same problem, without $wpdb entering into the picture at all. It appears you would need to forgo $wpdb and make mysqli calls yourself, being sure to use mysqli_store_result() so you can run a new query right after the first.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘$wpdb->get_results returns empty array if called more than once’ is closed to new replies.