When sorting WP_List_Table, table sorts, but I also get SQL errors

Question

Here's my lovely prepare_items() function:

function prepare_items()
{
    global $wpdb;

    $table_name = $wpdb->prefix . 'blah';
    $per_page = 100;
    $columns = $this->get_columns();
    $hidden = array();
    $sortable = $this->get_sortable_columns();

    $this->_column_headers = array($columns, $hidden, $sortable);

    $this->process_bulk_action();

    $total_items = $wpdb->get_var("SELECT COUNT(id) FROM $table_name");

    $paged = isset($_REQUEST['paged']) ? max(0, intval($_REQUEST['paged']) - 1) : 0;
    $orderby = (isset($_REQUEST['orderby']) && in_array($_REQUEST['orderby'], array_keys($this->get_sortable_columns()))) ? $_REQUEST['orderby'] : 'title';
    $order = (isset($_REQUEST['order']) && in_array($_REQUEST['order'], array('asc', 'desc'))) ? $_REQUEST['order'] : 'asc';

    $this->items = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name ORDER BY $orderby $order LIMIT %d OFFSET %d", $per_page, $paged), ARRAY_A);

    $this->set_pagination_args(array(
        'total_items'   => $total_items,
        'per_page'      => $per_page,
        'total_pages'   => ceil($total_items / $per_page),
    ));
}

Now when I sort the table, the URL becomes:

http://blah.dev/wp-admin/admin.php?page=mvc_events&orderby=start_date&order=asc

[13-Aug-2013 20:55:21 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asc LIMIT 0, 10' at line 1 for query SELECT `Event`.* FROM `wp_atb_events` `Event`    ORDER BY asc LIMIT 0, 10 made by do_action('toplevel_page_mvc_events'), call_user_func_array, MvcDispatcher->admin_events_index, MvcDispatcher->__call, __lambda_func, MvcDispatcher::dispatch, AdminEventsController->index, MvcAdminController->set_objects, MvcModel->paginate, MvcDatabaseAdapter->get_results, MvcDatabase->get_results
[13-Aug-2013 20:55:21 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asc' at line 1 for query SELECT COUNT(*) AS count FROM `wp_atb_events` `Event`    ORDER BY asc made by do_action('toplevel_page_mvc_events'), call_user_func_array, MvcDispatcher->admin_events_index, MvcDispatcher->__call, __lambda_func, MvcDispatcher::dispatch, AdminEventsController->index, MvcAdminController->set_objects, MvcModel->paginate, MvcModel->get_total_count, MvcDatabaseAdapter->get_var, MvcDatabase->get_var

Any idea why this error is showing up? Everything works, it's just these two error messages keep coming up.

Answer 1

the part ORDER BY $orderby $order LIMIT get translated to ORDER BY asc LIMIT, the columnname that supose to be in $orderby is missing, and there for the sql fail.

looks like you want your default value to be 'title'

$orderby = (isset($_REQUEST['orderby']) && in_array($_REQUEST['orderby'], array_keys($this->get_sortable_columns()))) ? $_REQUEST['orderby'] : 'title';

may geuss is that in_array() return true even when $_REQUEST['orderby'] is empty, and therefor accepting a empty $_REQUEST['orderby'] insted of 'title'.

if you add this row, dose the error messages diseper? (under $orderby, above $this->items)

$orderby = $orderby :? 'title';