Need help with wp-list-table custom queries per column

How can i have multiple queries for each column in a custom list table when using wp-list-table function?

I am building a custom page by using wp-list table class which will display a list with lets say 5 columns.

According to the wordpress codex, I will need to handle data by using Functions Prepared(Items) with an sql query to retrieve data from the database. I would like to know if i can use a different query for each of this columns.

$query = "SELECT ID, post_title, branch, week, donation, attendance, payments,post_date FROM $wpdb->posts, $wpdb->postmeta WHERE $wpdb->posts.ID=$wpdb->postmeta.post_id AND post_type = 'manufacturing' AND post_status = 'publish' ORDER BY post_date"; $data = $wpdb->get_results($query, ARRAY_A);

The above will get data for all columns at once but i want to use special queries for each column.

Secondly, if I have a custom filter for a post list table for a custom post type, after applying the filter , I want each column to display a result ( this result ..each column will be based on specific queries).

I mean -- each column after the filter query will be result from a specific query for each column.

  • Ash

    Hello Bez

    In the prepare_item() method, you can get all columns and then you can use query for each columns, something like the following:

    public function prepare_items( $iten )
    {
    	$all_columns = $this->get_columns();
    	foreach( $all_columns as $column )
    	{
    		switch( $column )
    		{
    			case 'col1':
    				$query = '';
    				break;
    
    			case 'col2':
    				$query = '';
    				break;
    
    			case 'col3':
    				$query = '';
    				break;
    		}
    	}
    }

    Hope it helps! Please feel free to ask more questions if you have any.

    Have a nice day!

    Cheers,
    Ash

  • Bez

    Hello Ash,
    I applied your solution but it did not work.
    It gave me bunch of errors :See my code below :

    if(!class_exists('WP_List_Table')){
        require_once( ABSPATH . 'wp-admin/includes/class-wp-list-table.php' );
    }
    
    class TT_Example_List_Tablesss extends WP_List_Table {
    
         function __construct(){
            global $status, $page;
    
            //Set parent defaults
            parent::__construct( array(
                'singular'  => 'region',     //singular name of the listed records
                'plural'    => 'regions',    //plural name of the listed records
                'ajax'      => false        //does this table support ajax?
            ) );
    
        }
    
          function column_cb($item){
            return sprintf(
                '<input type="checkbox" name="%1$s[]" value="%2$s" />',
                /*$1%s*/ $this->_args['singular'],  //Let's simply repurpose the table's singular label ("movie")
                /*$2%s*/ $item['ID']                //The value of the checkbox should be the record's id
            );
        }
    
        function get_columns(){
            $columns = array(
                'cb'        => '<input type="checkbox" />', //Render a checkbox instead of text
                'branch'     => 'Branch',
                'donation'    => 'Donation',
                'attendance'  => 'Attendance'
            );
            return $columns;
        }
    
        function prepare_items($item) {    
    
    		$all_columns = $this->get_columns();
            switch($column){
    			case 'branch':
    			$meta_key = '_branch';
    			$all_branch = $wpdb->get_col($wpdb->prepare("
                                      SELECT meta_value
                                      FROM $wpdb->postmeta
                                      WHERE meta_key = %s ORDER BY post_id", $meta_key));
    foreach( $all_branch as $all_branch ) {
    echo $all_branch;
    }
    			break;
    			case 'donation':
    			$meta_key = '_donation';
    			$all_donations = $wpdb->get_col($wpdb->prepare("
                                      SELECT meta_value
                                      FROM $wpdb->postmeta
                                      WHERE meta_key = %s ORDER BY post_id", $meta_key));
    foreach( $all_donations as $all_donations ) {
    echo $all_donations;
    }
    			break;
    			case 'attendance':
    			$meta_key = '_attendance';
    			$all_attendance = $wpdb->get_col($wpdb->prepare("
                                      SELECT SUM(meta_value)
                                      FROM $wpdb->postmeta
                                      WHERE meta_key = %s ORDER BY post_id", $meta_key));
    foreach( $all_attendance as $all_attendance ) {
    echo $all_attendance;
    }
    			break;
                    //return $item[$column_name];
                default:
    			break;
                    //return print_r($item,true); //Show the whole array for troubleshooting purposes
            }
    
            $hidden = array();
            $sortable = $this->get_sortable_columns();       
    
            $this->_column_headers = array($columns, $hidden);
    		$this->items = $all_columns;
        }
    
    }
    
    /** ************************ REGISTER THE TEST PAGE ****************************
     *******************************************************************************
     * Now we just need to define an admin page. For this example, we'll add a top-level
     * menu item to the bottom of the admin menus.
     */
    function tt_add_menu_itemszzz(){
        add_menu_page('Manufacturing', 'List Table Examples', 'activate_plugins', 'tt_list_testsss', 'tt_render_list_pagesss');
    } add_action('admin_menu', 'tt_add_menu_itemszzz');
    
    function tt_render_list_pagesss(){
    
        //Create an instance of our package class...
        $testListTable = new TT_Example_List_Tablesss();
        //Fetch, prepare, sort, and filter our data...
        $testListTable->prepare_items();
    
        ?>
        <div class="wrap">
    
            <div id="icon-users" class="icon32"><br/></div>
            <h2>List Table Test</h2>
    
            </div>
    
            <!-- Forms are NOT created automatically, so you need to wrap the table in one to use features like bulk actions -->
            <form id="movies-filter" method="get">
                <!-- For plugins, we also need to ensure that the form posts back to our current page -->
                <input type="hidden" name="page" value="<?php echo $_REQUEST['page'] ?>" />
                <!-- Now we can render the completed list table -->
                <?php $testListTable->display() ?>
            </form>
    
        </div>
        <?php
    }
    ?>
    }
  • Bez

    In addition, i picked up the recommended the custom list table plugin which has been my use case ..see attached and i made a query just for the colume name post_content :

    function column_post_content($item){
    		global $wpdb;
    	$meta_key = '_branch';
    
    $all_downloads = $wpdb->get_col($wpdb->prepare("
                                      SELECT meta_value
                                      FROM $wpdb->postmeta
                                      WHERE meta_key = %s ORDER BY post_id", $meta_key));
    foreach( $all_downloads as $all_downloads ) {
    echo $all_downloads;
    	}
    	}

    It gave me this result :

    As you can see , all content for this specific case is displayed on one row and duplicated for the other rows too. Kindly help profer solution solution to this..

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.