
Categories:
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; }