|
function show_data_with_search_and_sorting_shortcode($atts) { |
|
global $wpdb; |
|
|
|
// Get the shortcode attributes |
|
$atts = shortcode_atts(array( |
|
'query' => '', // SQL query provided via the 'query' parameter in the shortcode |
|
'per_page' => 10, // Number of rows per page |
|
'auth' => 'administrator', // Roles or capabilities required to view the data |
|
'link_columns' => '', // Columns to be displayed as links |
|
'link_templates' => '', // URL templates for the links, one per column |
|
), $atts, 'show_data'); |
|
|
|
// Parse the 'auth' attribute into an array of roles/capabilities |
|
$required_roles = array_map('trim', explode(',', $atts['auth'])); |
|
|
|
// Check if the current user has any of the required roles or capabilities |
|
$user_has_access = false; |
|
$current_user = wp_get_current_user(); |
|
foreach ($required_roles as $role) { |
|
if (in_array(strtolower($role), array_map('strtolower', $current_user->roles)) || current_user_can($role)) { |
|
$user_has_access = true; |
|
break; |
|
} |
|
} |
|
|
|
// If the user doesn't have access, display a message |
|
if (!$user_has_access) { |
|
return '<p>You do not have permission to view this content.</p>'; |
|
} |
|
|
|
// If no query is provided, display a message |
|
if (empty($atts['query'])) { |
|
return '<p>No query provided.</p>'; |
|
} |
|
|
|
// Ensure only SELECT queries are allowed for safety |
|
$query = stripslashes($atts['query']); |
|
if (stripos(trim($query), 'SELECT') !== 0) { |
|
return '<p>Invalid query. Only SELECT queries are allowed.</p>'; |
|
} |
|
|
|
// Use unique parameter names to avoid conflicts |
|
$pagination_param = 'custom_paged'; |
|
$sort_column_param = 'sort_column'; |
|
$sort_order_param = 'sort_order'; |
|
$search_param = 'search'; |
|
$column_search_param = 'col_search'; |
|
|
|
// Get the current page number from the URL, default to 1 if not set |
|
$current_page = isset($_GET[$pagination_param]) ? max(1, intval($_GET[$pagination_param])) : 1; |
|
$per_page = max(1, intval($atts['per_page'])); |
|
$offset = ($current_page - 1) * $per_page; |
|
|
|
// Get sort parameters from URL |
|
$sort_column = isset($_GET[$sort_column_param]) ? sanitize_text_field($_GET[$sort_column_param]) : ''; |
|
$sort_order = isset($_GET[$sort_order_param]) && strtoupper($_GET[$sort_order_param]) === 'DESC' ? 'DESC' : 'ASC'; |
|
|
|
// Get search parameters from URL |
|
$global_search = isset($_GET[$search_param]) ? sanitize_text_field($_GET[$search_param]) : ''; |
|
$column_search = isset($_GET[$column_search_param]) && is_array($_GET[$column_search_param]) ? array_map('sanitize_text_field', $_GET[$column_search_param]) : array(); |
|
|
|
// Execute the initial query to get column names |
|
$initial_results = $wpdb->get_results($query . " LIMIT 1"); |
|
if (empty($initial_results)) { |
|
return '<p>No records found.</p>'; |
|
} |
|
|
|
// Get column names from the first row |
|
$columns = array_keys(get_object_vars($initial_results[0])); |
|
|
|
// Validate sort column |
|
if (!in_array($sort_column, $columns)) { |
|
$sort_column = ''; // Reset to default if invalid |
|
} |
|
|
|
// Build WHERE clauses for search |
|
$where_clauses = array(); |
|
$query_params = array(); |
|
|
|
// Global search across all columns |
|
if ($global_search !== '') { |
|
$global_search_like = '%' . $wpdb->esc_like($global_search) . '%'; |
|
$global_search_clauses = array(); |
|
foreach ($columns as $column_name) { |
|
$global_search_clauses[] = "`$column_name` LIKE %s"; |
|
$query_params[] = $global_search_like; |
|
} |
|
$where_clauses[] = '(' . implode(' OR ', $global_search_clauses) . ')'; |
|
} |
|
|
|
// Column-specific searches |
|
foreach ($columns as $column_name) { |
|
if (isset($column_search[$column_name]) && $column_search[$column_name] !== '') { |
|
$search_value = '%' . $wpdb->esc_like($column_search[$column_name]) . '%'; |
|
$where_clauses[] = "`$column_name` LIKE %s"; |
|
$query_params[] = $search_value; |
|
} |
|
} |
|
|
|
// Build the final WHERE clause |
|
$where_clause = ''; |
|
if (!empty($where_clauses)) { |
|
$where_clause = ' WHERE ' . implode(' AND ', $where_clauses); |
|
} |
|
|
|
// Build ORDER BY clause if sorting is applied |
|
$order_by_clause = ''; |
|
if ($sort_column) { |
|
// Ensure the column name is safe (whitelisted) |
|
$order_by_clause = " ORDER BY `" . esc_sql($sort_column) . "` $sort_order"; |
|
} |
|
|
|
// Modify the query to add WHERE, ORDER BY, and LIMIT for pagination |
|
$paged_query = "SELECT * FROM (" . $query . ") AS subquery" . $where_clause . $order_by_clause . " LIMIT %d OFFSET %d"; |
|
|
|
// Prepare the paged query |
|
$prepared_query = $wpdb->prepare($paged_query, array_merge($query_params, array($per_page, $offset))); |
|
|
|
// Execute the paged query |
|
$results = $wpdb->get_results($prepared_query); |
|
|
|
// Count total records for pagination |
|
$count_query = "SELECT COUNT(*) FROM (" . $query . ") AS subquery" . $where_clause; |
|
$count_prepared_query = $wpdb->prepare($count_query, $query_params); |
|
$total_records = $wpdb->get_var($count_prepared_query); |
|
$total_pages = ceil($total_records / $per_page); |
|
|
|
// Parse link columns and templates |
|
$link_columns = array_map('trim', explode(',', $atts['link_columns'])); |
|
$link_templates = array_map('trim', explode(',', $atts['link_templates'])); |
|
|
|
// Create an associative array mapping columns to their templates |
|
$link_columns_templates = array(); |
|
foreach ($link_columns as $index => $column_name) { |
|
$template = isset($link_templates[$index]) ? $link_templates[$index] : ''; |
|
$link_columns_templates[$column_name] = $template; |
|
} |
|
|
|
// Start output buffering to capture the output |
|
ob_start(); |
|
|
|
// Include CSS and JavaScript for enhanced styling and interactivity |
|
?> |
|
<!-- Custom Styles --> |
|
<style> |
|
.custom-data-table-container { |
|
margin: 20px; |
|
padding: 20px; |
|
border: 1px solid #ddd; |
|
font-family: Arial, sans-serif; |
|
} |
|
.custom-search-form { |
|
margin-bottom: 20px; |
|
} |
|
.custom-search-form label { |
|
display: block; |
|
margin-bottom: 5px; |
|
font-weight: bold; |
|
} |
|
.custom-search-form input[type="text"] { |
|
padding: 8px; |
|
margin-bottom: 10px; |
|
width: 100%; |
|
box-sizing: border-box; |
|
} |
|
.custom-search-form button { |
|
padding: 8px 16px; |
|
margin-top: 10px; |
|
margin-right: 10px; |
|
} |
|
.custom-search-row { |
|
display: flex; |
|
flex-wrap: wrap; |
|
gap: 20px; |
|
} |
|
.custom-search-column { |
|
flex: 1; |
|
min-width: 200px; |
|
} |
|
.custom-data-table { |
|
border-collapse: collapse; |
|
width: 100%; |
|
margin: 10px 0; |
|
} |
|
.custom-data-table th, |
|
.custom-data-table td { |
|
border: 1px solid #ddd; |
|
padding: 8px; |
|
text-align: left; |
|
} |
|
.custom-data-table th { |
|
background-color: #f2f2f2; |
|
position: relative; |
|
cursor: pointer; |
|
} |
|
.custom-data-table th .sort-indicator { |
|
position: absolute; |
|
right: 8px; |
|
} |
|
.custom-data-table tr:nth-child(even) { |
|
background-color: #f9f9f9; |
|
} |
|
.custom-pagination { |
|
text-align: center; |
|
margin-top: 20px; |
|
} |
|
.custom-pagination a, |
|
.custom-pagination span { |
|
margin: 0 5px; |
|
text-decoration: none; |
|
} |
|
.custom-pagination a { |
|
color: #0073aa; |
|
} |
|
.custom-pagination a:hover { |
|
text-decoration: underline; |
|
} |
|
/* Padding and margin adjustments for search boxes */ |
|
.custom-global-search { |
|
margin-top: 20px; |
|
margin-left: 20px; |
|
margin-right: 20px; |
|
margin-bottom: 20px; |
|
} |
|
.custom-global-search input[type="text"] { |
|
padding: 8px; |
|
width: 300px; |
|
max-width: 100%; |
|
box-sizing: border-box; |
|
margin-right: 10px; |
|
} |
|
.custom-global-search button { |
|
padding: 8px 16px; |
|
margin-top: 0; |
|
margin-right: 10px; |
|
} |
|
.custom-column-search input[type="text"] { |
|
padding: 6px; |
|
width: 100%; |
|
box-sizing: border-box; |
|
margin-top: 5px; |
|
} |
|
</style> |
|
|
|
<!-- Custom JavaScript --> |
|
<script> |
|
document.addEventListener('DOMContentLoaded', function () { |
|
// Add event listeners to table headers for sorting |
|
var headers = document.querySelectorAll('.custom-data-table th.sortable'); |
|
headers.forEach(function (header) { |
|
header.addEventListener('click', function () { |
|
window.location.href = this.dataset.sortUrl; |
|
}); |
|
}); |
|
|
|
// Clear search inputs when 'Clear' button is clicked |
|
var clearButton = document.getElementById('clear_search'); |
|
if (clearButton) { |
|
clearButton.addEventListener('click', function () { |
|
// Clear global search input |
|
var globalSearchInput = document.getElementById('global_search'); |
|
if (globalSearchInput) { |
|
globalSearchInput.value = ''; |
|
} |
|
// Clear column-specific search inputs |
|
var columnSearchInputs = document.querySelectorAll('.custom-column-search input[type="text"]'); |
|
columnSearchInputs.forEach(function (input) { |
|
input.value = ''; |
|
}); |
|
// Submit the form |
|
this.form.submit(); |
|
}); |
|
} |
|
}); |
|
</script> |
|
|
|
<?php |
|
// Build the search form |
|
echo '<form method="get" class="custom-search-form">'; |
|
|
|
// Preserve other query parameters |
|
foreach ($_GET as $key => $value) { |
|
if (!in_array($key, array($pagination_param, $search_param, $column_search_param))) { |
|
if (is_array($value)) { |
|
foreach ($value as $array_value) { |
|
echo '<input type="hidden" name="' . esc_attr($key) . '[]" value="' . esc_attr($array_value) . '">'; |
|
} |
|
} else { |
|
echo '<input type="hidden" name="' . esc_attr($key) . '" value="' . esc_attr($value) . '">'; |
|
} |
|
} |
|
} |
|
|
|
// Global search field |
|
echo '<div class="custom-global-search">'; |
|
echo '<label for="global_search">Search All Columns:</label>'; |
|
echo '<input type="text" id="global_search" name="' . esc_attr($search_param) . '" value="' . esc_attr($global_search) . '">'; |
|
echo '<button type="submit">Search</button>'; |
|
echo '<button type="button" id="clear_search">Clear</button>'; |
|
echo '</div>'; |
|
|
|
// Start the table |
|
echo '<div class="custom-data-table-container">'; |
|
echo '<table class="custom-data-table">'; |
|
|
|
// Column-specific search inputs (placed above the headers) |
|
echo '<tr>'; |
|
foreach ($columns as $column_name) { |
|
echo '<td class="custom-column-search">'; |
|
$search_value = isset($column_search[$column_name]) ? $column_search[$column_name] : ''; |
|
echo '<input type="text" name="' . esc_attr($column_search_param) . '[' . esc_attr($column_name) . ']" value="' . esc_attr($search_value) . '">'; |
|
echo '</td>'; |
|
} |
|
echo '</tr>'; |
|
|
|
// Table headers with sorting links |
|
echo '<tr>'; |
|
// Build base URL for sorting links |
|
$base_url = get_permalink(); |
|
|
|
// Preserve existing query parameters except sort and pagination parameters |
|
$query_args = $_GET; |
|
unset($query_args[$sort_column_param]); |
|
unset($query_args[$sort_order_param]); |
|
unset($query_args[$pagination_param]); |
|
|
|
foreach ($columns as $column_name) { |
|
// Determine the sort order for this column |
|
$new_sort_order = 'ASC'; |
|
if ($sort_column === $column_name && $sort_order === 'ASC') { |
|
$new_sort_order = 'DESC'; |
|
} |
|
|
|
// Build the sorting URL |
|
$query_args[$sort_column_param] = $column_name; |
|
$query_args[$sort_order_param] = $new_sort_order; |
|
$sorting_url = add_query_arg($query_args, $base_url); |
|
|
|
// Add sort indicators |
|
$sort_indicator = ''; |
|
if ($sort_column === $column_name) { |
|
$sort_indicator = $sort_order === 'ASC' ? '▲' : '▼'; |
|
} |
|
|
|
echo '<th class="sortable" data-sort-url="' . esc_url($sorting_url) . '">'; |
|
echo '<span>' . esc_html($column_name) . '</span>'; |
|
if ($sort_indicator) { |
|
echo '<span class="sort-indicator">' . $sort_indicator . '</span>'; |
|
} |
|
echo '</th>'; |
|
} |
|
echo '</tr>'; |
|
|
|
// Close the form tag here so the table is submitted when search inputs are used |
|
echo '</form>'; |
|
|
|
// Add table rows |
|
if (!empty($results)) { |
|
foreach ($results as $row) { |
|
echo '<tr>'; |
|
foreach ($columns as $column_name) { |
|
$cell_value = esc_html($row->$column_name); |
|
|
|
// Check if the column should be displayed as a link |
|
if (isset($link_columns_templates[$column_name]) && !empty($link_columns_templates[$column_name])) { |
|
$template = $link_columns_templates[$column_name]; |
|
$url = str_replace('{{column}}', urlencode($row->$column_name), $template); |
|
$cell_value = '<a href="' . esc_url($url) . '" target="_blank">' . $cell_value . '</a>'; |
|
} |
|
|
|
echo '<td>' . $cell_value . '</td>'; |
|
} |
|
echo '</tr>'; |
|
} |
|
} else { |
|
echo '<tr><td colspan="' . count($columns) . '">No records found.</td></tr>'; |
|
} |
|
|
|
echo '</table>'; |
|
|
|
// Pagination controls |
|
if ($total_pages > 1) { |
|
echo '<div class="custom-pagination">'; |
|
|
|
// Build base URL for pagination links |
|
$query_args = $_GET; |
|
unset($query_args[$pagination_param]); |
|
|
|
// Determine the range of pages to display |
|
$max_display_pages = 7; |
|
$half = floor($max_display_pages / 2); |
|
|
|
$start_page = max(1, $current_page - $half); |
|
$end_page = min($total_pages, $current_page + $half); |
|
|
|
if ($current_page <= $half) { |
|
$end_page = min($total_pages, $max_display_pages); |
|
} |
|
if ($current_page + $half >= $total_pages) { |
|
$start_page = max(1, $total_pages - $max_display_pages + 1); |
|
} |
|
|
|
// "First" and "Previous" links |
|
if ($current_page > 1) { |
|
$query_args[$pagination_param] = 1; |
|
$first_page_url = add_query_arg($query_args, $base_url); |
|
echo '<a href="' . esc_url($first_page_url) . '">« First</a>'; |
|
|
|
$query_args[$pagination_param] = $current_page - 1; |
|
$prev_page_url = add_query_arg($query_args, $base_url); |
|
echo '<a href="' . esc_url($prev_page_url) . '">‹ Prev</a>'; |
|
} |
|
|
|
// Ellipsis before |
|
if ($start_page > 1) { |
|
echo '<span>...</span>'; |
|
} |
|
|
|
// Page number links |
|
for ($i = $start_page; $i <= $end_page; $i++) { |
|
$query_args[$pagination_param] = $i; |
|
$page_url = add_query_arg($query_args, $base_url); |
|
|
|
if ($i == $current_page) { |
|
echo "<span>$i</span>"; |
|
} else { |
|
echo '<a href="' . esc_url($page_url) . '">' . $i . '</a>'; |
|
} |
|
} |
|
|
|
// Ellipsis after |
|
if ($end_page < $total_pages) { |
|
echo '<span>...</span>'; |
|
} |
|
|
|
// "Next" and "Last" links |
|
if ($current_page < $total_pages) { |
|
$query_args[$pagination_param] = $current_page + 1; |
|
$next_page_url = add_query_arg($query_args, $base_url); |
|
echo '<a href="' . esc_url($next_page_url) . '">Next ›</a>'; |
|
|
|
$query_args[$pagination_param] = $total_pages; |
|
$last_page_url = add_query_arg($query_args, $base_url); |
|
echo '<a href="' . esc_url($last_page_url) . '">Last »</a>'; |
|
} |
|
|
|
echo '</div>'; |
|
} |
|
|
|
echo '</div>'; // Close the data table container |
|
|
|
// Return the buffered content |
|
return ob_get_clean(); |
|
} |
|
add_shortcode('show_data', 'show_data_with_search_and_sorting_shortcode'); |
