Show any data in DataTables in WordPress

There are many way to show data in jQuery DataTables. Here’s a way using shortcode.

Create a plugin with shortcode.

1. Create a function

Pseudo function:

function get_something_shortcode(){
    global $wpdb;

    $sql = "select * from table";
    $result = $wpdb->get_results($sql);

    $str = '<table id="logTable">';
    $str .= '<thead><tr><th>column1</th><th>column1</th></tr></thead>';
    $str .= '<tbody>';

    foreach($result as $row) {
        $str .= '<tr><td>'.$row->column1.'</td><td>'.$row->column2.'</td></tr>';
    }
    $str.="</tbody></table>";
    return $str;
}

Real function

function get_activity_logs_shortcode(){
    global $wpdb;
    $sql = "select DATE_FORMAT(convert_tz(l.visit_date,'+0:00','-5:00'), '%y-%m-%d %h:%i:%s')
 AS `date`,p.post_title,p.post_type,pu.display_name AS author,(case when l.display_name='unknown' then '-' else l.display_name end) AS user,l.status ,l.user_ip,l.city,l.referer
from wp_moove_activity_log l join wp_posts p on p.ID = l.post_id
join wp_users pu on pu.ID = p.post_author
left join wp_users u on u.ID = l.user_id
where p.post_type in ('page','post') and l.user_ip not LIKE '66.249.75.%' AND l.user_ip not LIKE '66.249.69.%' and l.display_name!='太极客'
order by l.visit_date desc";
    $result = $wpdb->get_results($sql);

    $str='<table id="logTable" style="font-size:12px">';
        $str .= '<thead><tr><th>Date/Time</th><th>Title</th><th>Post type</th><th>Author</th><th>User</th><th>Activity</th><th>Client IP</th><th>Client Location</th><th>Referer</th></tr></thead>';
        $str .= '<tbody>';

    foreach($result as $row) {
        $str .= '<tr><td><a href="'.$row->Date.'" nowrap style="font-size:10px;white-space:nowrap">'.$row->date.'</td><td>'.$row->post_title.'</td><td>'.$row->post_type.'</td><td>'.$row->author.'</td><td>'.$row->user.'</td><td>'.$row->status.'</td><td>'.$row->user_ip.'</td><td>'.$row->city.'</td><td>'.$row->referer.'</td></tr>';
    }
    $str.="</tbody></table>";
    return $str;
}

2. Make it a shortcode

add_shortcode('activity_logs','get_activity_logs_shortcode');

Add following code in head section in header.php file of the child theme.

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

<script type="text/javascript">
$(document).ready(function() {
    $('#logTable').DataTable( {
        "initComplete": function(settings, json) {
            var obj = $('#memberTable_filter label');
            obj.html(obj.html().replace('Search:','搜索: '));

            obj = $('.dataTables_length label');
            obj.html(obj.html().replace('Show','显示 ').replace('entries',' 行'));
        },
        "pageLength": 50
    } );
} );
</script>