Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a couple functions that are creating an admin table to display on my custom dashboard page. I'm having a little trouble with the function that determines what pages to display because I'm not sure how to restrict my query to not count items that had a post status of 'auto-draft' or 'trash'.

Anyone know how to do that? I'll need $totalitems to only count the ones without those post statuses. Here's my function:

/**
                     * Prepare the table with different parameters, pagination, columns and table elements
                     */
                    function prepare_items() {
                        global $wpdb, $_wp_column_headers;
                        $screen = get_current_screen();

                        /* -- Preparing your query -- */
                        $query = "SELECT * FROM $wpdb->posts";

                        /* -- Ordering parameters -- */
                        //Parameters that are going to be used to order the result
                        $orderby = !empty($_GET["orderby"]) ? mysql_real_escape_string($_GET["orderby"]) : 'ASC';
                        $order = !empty($_GET["order"]) ? mysql_real_escape_string($_GET["order"]) : '';
                        if(!empty($orderby) & !empty($order)){ $query.=' ORDER BY '.$orderby.' '.$order; }

                        /* -- Pagination parameters -- */
                        //Number of elements in your table?
                        $totalitems = $wpdb->query($query); //return the total number of affected rows

                        //How many to display per page?
                        $perpage = 15;
                        //Which page is this?
                        $paged = !empty($_GET["paged"]) ? mysql_real_escape_string($_GET["paged"]) : '';
                        //Page Number
                        if(empty($paged) || !is_numeric($paged) || $paged<=0 ){ $paged=1; }
                        //How many pages do we have in total?
                        $totalpages = ceil($totalitems/$perpage);
                        //adjust the query to take pagination into account
                        if(!empty($paged) && !empty($perpage)){
                            $offset=($paged-1)*$perpage;
                            $query.=' LIMIT '.(int)$offset.','.(int)$perpage;
                        }

                        /* -- Register the pagination -- */
                        $this->set_pagination_args( array(
                            "total_items" => $totalitems,
                            "total_pages" => $totalpages,
                            "per_page" => $perpage,
                        ) );
                        //The pagination links are automatically built according to those parameters

                        /* — Register the Columns — */
                        $columns = $this->get_columns();
                        $hidden = array();
                        $sortable = $this->get_sortable_columns();
                        $this->_column_headers = array($columns, $hidden, $sortable);

                        /* -- Fetch the items -- */
                        $this->items = $wpdb->get_results($query);
                    }
share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

WHERE post_status NOT IN ('auto-draft','trash')

Looks like you can add that to your base query.

/* -- Preparing your query -- */
$query = "SELECT * FROM $wpdb->posts WHERE post_status NOT IN ('auto-draft','trash') ";

This is only barely a WordPress question. Why are you not using core functions for this?

Also, NOT IN is not a very efficient query. This could be slower than you expect.

share|improve this answer
    
I guess I don't quite follow about using Core Functions. I was following the tutorial listed here: wp.smashingmagazine.com/2011/11/03/…. The answer you gave makes sure the elements don't display, but for some reason it still outputs the rows in the table and pages, so they are all blank. Any ideas on how to restrict that? –  Jake Lisby Jan 2 '13 at 15:52
    
WordPress has a lot of functions/classes built in to access its own tables. If those functions will do what you need you should nearly always use them in preference to your own SQL. That way, if WordPress changes something in the database your code will still work. –  s_ha_dum Jan 2 '13 at 16:39
    
Ok, so 2 things... #1 is there a tutorial on how you could use Core Functions to create the custom table like this? Or would you want to put one together for the community? AND #2, for some reason the query you included above still returns 263 items which is the total including auto-draft and trash items. Any thoughts on why that isn't working? –  Jake Lisby Jan 2 '13 at 17:11
    
That code (SQL only) does eliminate "auto-draft" and "trash" when I try it. It does not eliminate "draft" or "inherit", or any others, but those were not part of your request. –  s_ha_dum Jan 2 '13 at 17:32
    
You are awesome, thanks for your help with all this. I finally got it figured out and now have everything displaying properly. –  Jake Lisby Jan 2 '13 at 18:05
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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