0

In below function column_default I have several cases doing almost the same query.

Is it possible to use only 1 query for all the 3 cases? And if so how do I use it in below example? Thanks for helping me.

function column_default($item, $column_name){
        global $wpdb;

 /*             
  if ( ! in_array( $column_name, ['id', 'user_id', 'user_email', 'answer', 'answer_time', 'ip_address' ] ) )
    return '';

$sql = "SELECT $column_name as result 
        FROM {$wpdb->prefix}rdp_participants_answers 
        WHERE user_id = %d ORDER BY id DESC LIMIT 1 ";

$query = $wpdb->get_row( $wpdb->prepare( $sql, $item[ 'ID' ] ) );

if ( is_object( $query ) )
    return $query->result;
    */      
    switch($column_name){
//      case 'id':              return $item['ID'];
    case 'user_id':             return $item['ID'];
    case 'user_email':          $user_info = get_userdata($item['ID']);
                                return $user_info->user_email;
    case 'display_name':        $user_info = get_userdata($item['ID']);
                                return $user_info->display_name;            
    case 'city':                return get_user_meta($item['ID'], 'city', true);
    case 'age':                 return get_user_meta($item['ID'], 'age', true);

    case 'user_status':             $user_status = get_user_meta($item['ID'], 'status', true);

            if($user_status == 1) { 
                return '
                <img src="'.RDP_URL.'images/icon_status_green.gif" />
                <a href="'.get_admin_url().'admin.php?page=rdp_admin_participants&off='.$item['ID'].'&status=update"><img src="'.RDP_URL.'images/icon_status_red_light.gif" /><a/> 
                '; } 
                elseif($user_status == 0) { 
                return '<a href="'.get_admin_url().'admin.php?page=rdp_admin_participants&on='.$item['ID'].'&status=update"><img src="'.RDP_URL.'images/icon_status_green_light.gif" /><a/> 
                <img src="'.RDP_URL.'images/icon_status_red.gif" />';
            }
    case 'answer':                  
            $arrAnswer = $wpdb->get_row( "SELECT answer FROM {$wpdb->prefix}rdp_participants_answers WHERE user_id = ".$item['ID']." ORDER BY id DESC LIMIT 1 " );
                    if(count($arrAnswer) == null)
                    return 0;
                    else
                    return $arrAnswer->answer;  
    case 'answer_time':         
            $arranswerTime = $wpdb->get_row( "SELECT answer_time FROM {$wpdb->prefix}rdp_participants_answers WHERE user_id = ".$item['ID']." ORDER BY id DESC LIMIT 1 " );
                    if(count($arranswerTime) == null)
                    return 0;
                    else
                    return $arranswerTime->answer_time; 
    case 'ip_address':          
            $arrIPAddress = $wpdb->get_row( "SELECT ip_address FROM {$wpdb->prefix}rdp_participants_answers WHERE user_id = ".$item['ID']." ORDER BY id DESC LIMIT 1 " );
                    if(count($arrIPAddress) == null)
                    return 0;
                    else
                    return $arrIPAddress->ip_address;   
    case 'user_registered': return $item['user_registered'];
    case 'actions':                 return $item[$column_name];
    default:                        return '<a href="'.get_admin_url().'admin.php?page=rdp_admin_participants&edit_rdp='.$item['ID'].'" class="page-title-action">Edit</a><a href="'.get_admin_url().'admin.php?page=rdp_admin_participants&view_rdp='.$item['ID'].'" class="page-title-action">View</a>'; 
    }
}
$query = "SELECT * FROM wp_users AS usr 
        JOIN wp_usermeta AS meta ON meta.user_id = usr.ID 
        LEFT JOIN wp_rdp_participants_answers as ans ON ans.user_id = usr.ID
        WHERE ans.answer IS NOT NULL
        GROUP BY usr.ID, usr.user_email ORDER BY ans.id ASC";               


$data = $wpdb->get_results($query,ARRAY_A);
$this->items = $data;
  • Yes, of course. But how is that a WP problem? – fuxia Aug 2 '16 at 16:55
  • It is not a problem getting the right data. But for me it seems very complex coding todo almost the same queries more then once. How can I get it per return in 1 query? – Hermants Aug 2 '16 at 17:39
  • This seems to be unspecific to WordPress and likely better asked at another site of the network, such as Stack Overflow. – Rarst Aug 2 '16 at 18:07
  • This is pure WP code. – Hermants Aug 2 '16 at 19:51
2

Your problem is mostly just a change in PHP logic, not really WordPress. Anyway, we are here to help, and that would be off-topic on other sites too, so …

  1. It is unfortunate enough that you have to access a global variable in order to use the current database connection. Make others (you in 6 months!) life easier, and import global variables always at the top of a function body.

    If you are searching later for all accesses to that variables, you will thank your past self for that. :)
    In my code, I use mostly class methods as callbacks, and I pass the $wpdb object as a dependency into the class constructor, so I have to touch that global variable just once, at the very top of my code hierarchy.

  2. When you are using almost the same query every time, look what the differences are, and make those either a variable, or unify them. In your case, you can just use the $column_name variable, because it is matching the database table column name and a whitelist of valid strings. Also, you can rename the resulting variable: SELECT x as foo FROM …

  3. $item[ 'ID' ] is unknown, it can be anything. Always prepare unknown values before you send them to the database. $wpdb has method prepare() that works similar to sprintf().

  4. You don't need to run count() on the query result. You get an object only on success, so the faster test against is_object() is good enough.

  5. Move that code to a separate function. It does too much.

Sample code for all of these points:

function answer_query( $column_name, $id )
{
    global $wpdb;

    $sql = "SELECT %s as result
            FROM {$wpdb->prefix}rdp_participants_answers
            WHERE user_id = %d ORDER BY id DESC LIMIT 1 ";

    $query = $wpdb->get_row( $wpdb->prepare( $sql, $column_name, $id ) );

    if ( is_object( $query ) )
        return $query->result;

    return 0;
}

And in your switch statement, shorten the code to:

case 'answer':
case 'answer_time':
case 'ip_address':
    return answer_query( $column_name, (int) $item['ID'] ) );
  • Just add it as extra function + added the shorten cases exactly as you wrote. As result I get Fatal error: Call to undefined function answer_query() – Hermants Aug 3 '16 at 11:15
  • Well then you haven't declared the function at the proper place. – fuxia Aug 3 '16 at 11:17

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.