Take a WPDB query result and display it as a table, with headers from data keys. Creates demo menu page to show example table. #wordpress Basic example for previewing results, handy for debugging etc, but doesn't provide much validation, sorting, pagination etc.

database_table_example.php

<?php

/**
 * Take a WPDB query result and display it as a table, with headers from data keys.
 * This example only works with ARRAY_A type result from $wpdb query.
 * @param  array                $db_data Result from $wpdb query
 * @return bool                          Success, outputs table HTML
 * @author Tim Kinnane <[email protected]>
 * @link   http://nestedcode.com
 */
function data_table( $db_data ) {
	if ( !is_array( $db_data) || empty( $db_data ) ) return false;

	// Get the table header cells by formatting first row's keys
	$header_vals = array();
	$keys = array_keys( $db_data[0] );
	foreach ($keys as $row_key) {
		$header_vals[] = ucwords( str_replace( '_', ' ', $row_key ) ); // capitalise and convert underscores to spaces
	}
	$header = "<thead><tr><th>" . join( '</th><th>', $header_vals ) . "</th></tr></thead>";

	// Make the data rows
	$rows = array();
	foreach ( $db_data as $row ) {
		$row_vals = array();
		foreach ($row as $key => $value) {

			// format any date values properly with WP date format
			if ( strpos( $key, 'date' ) !== false || strpos( $key, 'modified' ) !== false ) {
				$date_format = get_option( 'date_format' );
				$value = mysql2date( $date_format, $value );
			}
			$row_vals[] = $value;
		}
		$rows[] = "<tr><td>" . join( '</td><td>', $row_vals ) . "</td></tr>";
	}

	// Put the table together and output
	echo '<table class="wp-list-table widefat fixed posts">' . $header . '<tbody>' . join( $rows ) . '</tbody></table>';

	return true;
}

/* Example usage with a menu page */
function database_table_example_page() {

	// example query: Posts published in last month
	global $wpdb;
	$posts_one_month = $wpdb->get_results("
		SELECT `ID`, `post_title`, `post_modified`
		FROM $wpdb->posts
		WHERE $wpdb->posts.post_type = 'post'
		AND $wpdb->posts.post_date > '" . date('Y-m-d H:i:s', strtotime('-1 month')) . "'
		ORDER BY $wpdb->posts.post_date DESC
	", ARRAY_A );

	?>
		<div class="wrap">
			<h1>Last Month's Posts</h1>
			<?php data_table( $posts_one_month ); ?>
		</div>
	<?php
}

/* Register menu page to use for example */
function register_example_page() {
  add_menu_page( 'Database Table Example', 'DB Table (demo)', 'manage_options', 'example', 'database_table_example_page', 'dashicons-list-view', 90 );
}
add_action( 'admin_menu', 'register_example_page' );

?>

Thanks for this :)

Thanks for saying thanks ❤️ had no idea this would still be useful to anyone.