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.

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.

share|improve this question

1 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';
share|improve this answer

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.