0

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.

1 Answer 1

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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