• Below is my code, I got a query echo by var_dump method, wpdb get_var and get_results don’t give me the expected results, get_var returns NULL and get_results returns array() when I run the generated query on phpmyadmin it gave me proper results. below is my code

    $minAge = xprofile_get_field_data(3471, bp_loggedin_user_id());
    $maxAge = xprofile_get_field_data(7475, bp_loggedin_user_id());
    
    $age_q = "SELECT GROUP_CONCAT(distinct ".$wpdb->base_prefix."bp_xprofile_data.user_id) as user_ids FROM ".$wpdb->base_prefix."bp_xprofile_data JOIN ".$wpdb->base_prefix."bp_xprofile_fields ON ".$wpdb->base_prefix."bp_xprofile_data.field_id = ".$wpdb->base_prefix."bp_xprofile_fields.id WHERE field_id =2 AND ".$wpdb->base_prefix."bp_xprofile_data.user_id !=".bp_loggedin_user_id();
        $agewhere = "";
    
        if($minAge){
          $agewhere .= " AND TIMESTAMPDIFF( YEAR, STR_TO_DATE( ".$wpdb->base_prefix."bp_xprofile_data.value, '%Y-%m-%d' ) , CURDATE( ) ) >= ".$minAge;
        } 
    
        if($maxAge){
          $agewhere .= " AND TIMESTAMPDIFF( YEAR, STR_TO_DATE( ".$wpdb->base_prefix."bp_xprofile_data.value, '%Y-%m-%d' ) , CURDATE( ) ) <= ".$maxAge;
        }
        var_dump($age_q.$agewhere);
        if(!empty($agewhere)){
          $ageResults = $wpdb->get_var($age_q.$agewhere);
          var_dump($ageResults);
        }

    Query Generated by var_dump function like below \

    "SELECT GROUP_CONCAT(distinct wp_bp_xprofile_data.user_id) as user_ids FROM wp_bp_xprofile_data JOIN wp_bp_xprofile_fields ON wp_bp_xprofile_data.field_id = wp_bp_xprofile_fields.id WHERE field_id =2 AND wp_bp_xprofile_data.user_id !=5 AND TIMESTAMPDIFF( YEAR, STR_TO_DATE( wp_bp_xprofile_data.value, '%Y-%m-%d' ) , CURDATE( ) ) >= 18 AND TIMESTAMPDIFF( YEAR, STR_TO_DATE( wp_bp_xprofile_data.value, '%Y-%m-%d' ) , CURDATE( ) ) <= 31"

Viewing 1 replies (of 1 total)
  • Hey chtn2109,

    Is it possible that you’re reaching the 1,024 byte max length of GROUP_CONCAT or something? I’ve ran into similar problems in the past with GROUP_CONCAT on larger data sets and I ended up just running a separate query with wp_list_pluck() to get the list of IDs first and using that instead of GROUP_CONCAT and that solved my problem.

Viewing 1 replies (of 1 total)
  • The topic ‘wpdb get_var OR get_results didn't give me the results, but I get results on php’ is closed to new replies.