Wordpress custom admin table from database with pagination

Categories: 

Tags: 

Objective

  • Create a custom table with data from a database sql query, with sort and pagination, in WordPress admin panel.
  • Step 1: create a custom menu link to access a new page for the table
  • Step 2: create callback function for the menu link, prepare data input for pagination, sort and print form + table
  • Note: this should be improved to use full WordPress advantages of wp_list_table

Step 1: create a custom menu link to access a new page for the table

Easy one: http://guillermocerezo.com/en/blog/codigo-php-desarrollo-web-cms-wordpre...

function register_table_example_menu_page() {
    add_menu_page(
        __('Custom table example link'), // link title
        __('Custom table example link'), // link display on screen
        'manage_options', // capability / permission to see display the link
        'example_table', // menu slug / clean url
        'example_table_with_custom_data', // function name to display content when an user clicks 
        '', // icon url, example: plugins_url( 'myplugin/images/icon.png' ),
        6 // menu order, must be unique, if the number exists it will overwrite another menu link
    ); 
}
 
add_action( 'admin_menu', 'register_table_example_menu_page' );

Step 2: create callback function for the menu link, prepare data input for pagination, sort and print form + table

/**
 * Print form and table.
 * Example fields for sent mails table 'wp_example_messages': subject, body, created, user_id 
 */
function example_table_with_custom_data()
{
    global $wpdb;
 
    // prepare user input for pagination and sorting
 
    // user id
    $user_id = isset($_GET['user_id']) ? $_GET['user_id'] : null;
 
    // page number
    $paged = isset($_GET['paged']) ? $_GET['paged'] : 1;
 
    // date filter example
    $filter['date_begin'] = isset($_GET['date_begin']) ? $_GET['date_begin'] : "";
    $filter['date_end'] = isset($_GET['date_end']) ? $_GET['date_end'] : "";
    $conditions = example_table_page_filter($filter);
 
    // sort by row column or created
    $sort['name'] = isset($_GET['sort']) ? $_GET['sort'] : 'created';
    $sort['order'] = isset($_GET['order']) ? $_GET['order'] : 'DESC';
    $sort_by = ' ORDER BY ' . $sort['name'] . ' '. $sort['order'];
 
    // init html output
    $html = '';
 
    // if we have the user id...
    if ($user_id && is_numeric($user_id)) {
 
        // initial link for pagination.
        // "page" must be the  menu slug / clean url from the add_menu_page
        $link = 'admin.php?page=example_table';
 
        // user info by WordPress function
        $user_info = get_userdata($user_id);
 
        // initial form html
        $html = '<h1>'. __('Emails sent by').' '. $user_info->display_name.'</h1>';
        $html .= '
            <form action="' . $link . '" method="get">
                <input type="hidden" name="page" value="example_table">
                <input type="hidden" name="user_id" value="'.$user_id.'">
                <input type="hidden" name="sort" value="'.$sort['name'].'">
                <input type="hidden" name="order" value="'.$sort['order'].'">
                <input type="hidden" name="paged" value="'.$paged.'">
                <label for="date_begin">'.__('Date begin').'</label>
                <input type="text" name="date_begin" value="' .$filter['date_begin']. '" placeholder="YYYY-MM-DD">
                <label for="date_end">'.__('Date end').'</label>
                <input type="text" name="date_end" value="' .$filter['date_end']. '" placeholder="YYYY-MM-DD">
                <input type="submit" value="'.__('Send').'">
            </form>';
 
        // example sql query to get user emails
        $sql = '
            SELECT u.display_name as "from", u.ID, m.subject,  m.body, m.created
            FROM wp_example_messages m 
            LEFT JOIN wp_users u ON u.ID=m.user_id 
            WHERE u.ID = '.$user_id.' '.$conditions.' '.$sort_by;
        $rows = $wpdb->get_results($sql);
 
        $rows_per_page = 20;
 
        // add pagination arguments from WordPress
        $pagination_args = array(
            'base' => add_query_arg('paged','%#%'),
            'format' => '',
            'total' => ceil(sizeof($rows)/$rows_per_page),
            'current' => $paged,
            'show_all' => false,
            'type' => 'plain',
        );
 
        $start = ($paged - 1) * $rows_per_page;
        $end_initial = $start + $rows_per_page;
        $end = (sizeof($rows) < $end_initial) ? sizeof($rows) : $end_initial;
 
        // if we have results
        if (count($rows) > 0) {
            // prepare link for pagination
            $link .= '&date_begin=' . $filter['date_begin'] 
                    . '&date_end=' . $filter['date_end'] 
                    . '&user_id=' . $user_id
                    . '&paged=' . $paged;
 
            $order = $sort['order'] == "ASC" ? "DESC" : "ASC";
 
            // html table head
            $html .= '<table id="user-sent-mail" class="wp-list-table widefat fixed users">
                    <thead>
                    <tr class="manage-column">
                        <th class="col-from">
                            <a href="' . $link . '&sort=from&order=' . $order . '">
                            ' . __('From') . '
                            </a>
                        </th>
                        <th class="col-subject">
                            <a href="' . $link . '&sort=subject&order=' . $order . '">
                            ' . __('Subject') . '
                            </a>
                        </th>
                        <th class="col-body">
                            <a href="' . $link . '&sort=body&order=' . $order . '">
                                ' . __('Body') . '
                            </a>
                        </th>
                        <th class="col-created">
                            <a href="' . $link . '&sort=created&order=' . $order . '">
                            ' . __('Created') . '
                            </a>
                        </th>
                    </tr>
                    </thead>
                    <tbody>
                    ';
 
            // add rows
            for ($index = $start; $index < $end;  ++$index) {
                $row = $rows[$index];
 
                $class_row = ($index % 2 == 1 ) ? ' class="alternate"' : '';
                $html .= '
                    <tr ' . $class_row . '>
                        <td>' . $row->from . '</td>
                        <td>' . $row->subject . '</td>
                        <td>' . $row->body . '</td>
                        <td>' . $row->created . '</td>
                    </tr>';
            }
 
            $html .= '</tbody></table>';
 
            // add pagination links from WordPress
            $html .= paginate_links($pagination_args);
        } else {
            $html .= '<p>' . __('No records found, try again please') . '</p>';
        } // endif count($rows)
 
    } // endif $user_id exists
 
    // print form + table
    echo $html;
 
} // end example_table_with_custom_data
 
 
/**
 * Add where conditions for dates
 * 
 * @param array $filter date_begin | date_end
 * @return string
 */
function example_table_page_filter($filter)
{
    $conditions = '';
    if (isset($filter) && count($filter)>0) {
        if (isset($filter['date_begin']) && $filter['date_begin'] 
            && checkdate(
                substr($filter['date_begin'], 5,2),
                substr($filter['date_begin'], 8,2),
                substr($filter['date_begin'], 0,4)
                )) {
            $conditions .= ' AND DATE_FORMAT(m.created,"%Y-%m-%d") 
                >= DATE_FORMAT("'.$filter['date_begin'].'","%Y-%m-%d") ';
        }
        if (isset($filter['date_end']) && $filter['date_end'] 
                && checkdate(
                substr($filter['date_end'], 5,2),
                substr($filter['date_end'], 8,2),
                substr($filter['date_end'], 0,4)
                )) {
            $conditions .= ' AND DATE_FORMAT(m.created,"%Y-%m-%d") 
                <= DATE_FORMAT("'.$filter['date_end'].'","%Y-%m-%d") ';
        }
    }
    return $conditions;
}