I searched high and low for a WordPress plugin to display dynamic table data from my databases on my WP sites. I store a lot of data, with frequent updates, using the Postgresql relational database management system (RDMS). This post explains using WordPress shortcodes to query a database and display table data so your posts are always current. Tables can be displayed inline anywhere in posts and pages where you can insert a shortcode. This method was developed on a site running the Gutenberg block editor (WP v5.2.1 ) so don’t be shy, give it a try.

Custom shortcode functions could be developed as a simple plugin but I chose to add this PHP shortcode function in the functions.php file of my child theme. The HTML output defines a CSS class i.e. <table class=”pg_table_query”> for easy styling to compliment your theme.

The pg_table_query shortcode takes 2 arguments; (1) sql query and (2) text title displayed as a table caption.  An example Gutenberg shortcode entry is shown below.

Loading the page/post produces the table shown below. CSS styling, also in the child theme, centers the table caption and highlights header data.

The shortcode function pg_table_query_func coded in PHP below is written in the functions.php file of my child theme.

function pg_table_query_func($atts) {
$atts = shortcode_atts (
array(
‘sql’ => ‘select * from quote_day_r;’,
‘title’ => ‘table title’
), $atts, ‘pg_table_query_func’ );
$host = “host=db_host”;
$port = “port=5432”;
$dbname = “dbname=my_db”;
$credentials = “user=db_user password=my_pwd”;
$output=”;

$db=pg_connect( “$host $port $dbname $credentials”) or die (“no database connection”);
$ret = pg_query($db, $atts[‘sql’]) or die (“query failure”);
$output .= ‘<table class=”pg_table”>’.”\n”;
$output .= ‘<caption>’.$atts[‘title’].'</caption>’.”\n”.'<tr>’;
for($i=0; $i<pg_num_fields($ret); $i++) {
$fieldname=pg_fieldname($ret,$i);
$output .= ‘<th><b>’.$fieldname.'</b></th>’;
}
$output .= ‘</tr>’.”\n”.'<tr>’;
for($row=0; $row<pg_num_rows($ret); $row++) {
$output .= ‘<tr>’;
$result = pg_fetch_row($ret,$row);
for($col=0; $col<pg_num_fields($ret); $col++) {
$output .= ‘<td>’.$result[$col].'</td>’;
}
$output .= ‘</tr>’.”\n”;
}
$output .= ‘</table>’.”\n”;

return $output;
}
add_shortcode (‘pg_table_query’,’pg_table_query_func’);

The related CSS style code:

table.pg_table {
color: #070707;
border: 1px solid #070707;
vertical-align: middle;
border-spacing: 0px;
line-height: 1.2;
width: 100%;
}

.pg_table td, tr, th {
border: 1px solid black;
text-align: center;
font-size: 12px;
}

.pg_table caption {
display: table-caption;
text-align: center;
font-weight: bold;
font-size: 18px;
padding: 5px 0 5px 0;
}