Below is an example shortcode which will display a search field that searches arbitrary tables and columns using a MySQL LIKE query.
Its usage takes the form [act-search fields="wp_pods_test_act.some_field,wp_pods_test_act_2.name"], where wp_pods_test_act and wp_pods_test_act_2 are table names, and some_field and name are columns within those tables.
If taking this approach rather than going with a graphical software built for this purpose, there are several technical challenges to note:
- MySQL
LIKE queries are not particularly performant on unindexed columns of large datasets. This goes even more so if many fields are to be searched. The Stack Overflow thread mentioned in the code comments discusses this challenge as well as suggested optimizations to table structure.
- User input must be escaped and sanitized. This is the purpose of
wp_strip_all_tags(), esc_html() or wp_kses_post(), esc_attr(), and esc_sql(). Otherwise, user input can cause script injection or queries such as DROP TABLES.
- MySQL data does not always have a one-to-one relationship with the desired HTML view. This would be particular to your application.
- Sometimes
JOINs are necessary. This would be particular to your application.
Solving these problems for all potential data structures is a daunting task. It would be nice to see in Pods, but would take an enormous amount of work. However, if you have a specific data set or use case in mind, the example below may be enough to get you started.
<?php
/**
* Plugin Name: ACT Search Shortcode
* Description: Usage: <code>[act-search fields="wp_pods_test_act.some_field,wp_pods_test_act_2.name"]</code> where <code>wp_pods_test_act</code> and <code>wp_pods_test_act_2</code> are table names and <code>some_field</code> and <code>name</code> are column names.
* Version: 0.1
*/
add_shortcode(
'act-search',
function( $atts, $content, $tag ) {
global $wpdb;
ob_start();
if ( empty( $atts['fields'] ) ) {
return;
}
?>
<form id="act-search" method="GET">
<input
type="text"
name="act-search"
placeholder="<?php echo esc_attr( __( 'Search...', 'act-search' ) ); ?>"
value="<?php echo esc_attr( @$_GET['act-search'] ); ?>"
/>
<button type="submit">
<?php echo __( 'Search', 'act-search' ); ?>
</button>
</form>
<?php
// If nothing has been searched, just display the form.
if ( ! isset( $_GET['act-search'] ) ) {
return ob_get_clean();
}
// Derive table names from table1.field1,table2.field2 notation.
$fields_array = explode( ',', $atts['fields'] );
$tables = [];
foreach( $fields_array as $field ) {
$tables[] = explode( '.', trim( $field ) )[0];
}
// Build a case-insensitive OR WHERE LIKE query for each word in the search query.
$search_array = explode( ' ', wp_strip_all_tags( $_GET['act-search'] ) );
$where = [];
foreach( $fields_array as $field ) {
foreach( $search_array as $word ) {
$word = trim( $word );
if ( empty( $word ) ) {
continue;
}
/**
* @see https://stackoverflow.com/questions/2876789/how-can-i-search-case-insensitive-in-a-column-using-like-wildcard
*/
$where[] = sprintf(
'LOWER( %s ) LIKE \'%%%s%%\'',
$field,
esc_sql( strtolower( $word ) )
);
}
}
// Combine inputs into the final SQL query.
$sql = sprintf(
'SELECT
%s
FROM
%s
WHERE
%s',
$atts['fields'], // SELECT
implode( ',', $tables ), // FROM
implode( ' OR ', $where ) // WHERE
);
// Run the query.
$results = $wpdb->get_results( $sql, 'ARRAY_A' );
// If nothing found, return a message saying so.
if ( empty( $results ) ) {
printf(
'<pre>%s</pre>',
__( 'Nothing found.', 'act-search' )
);
return ob_get_clean();
}
// Display the results.
echo '<table>';
// Headers.
echo '<tr>';
foreach( array_keys( $results[0] ) as $header ) {
printf(
'<th>%s</th>',
esc_html( $header )
);
}
echo '</tr>';
// Rows.
foreach( $results as $row ) {
echo '<tr>';
foreach( $row as $header => $value ) {
printf(
'<td>%s</td>',
wp_kses_post( $value )
);
}
echo '</tr>';
}
echo '</table>';
return ob_get_clean();
}
);
