I had a very similar question and I looked for a suitable plugin that would allow me to have a mysql database in the backend instead of embedding data in a post as Tablepress does. I didn’t succeed in finding anything suitable, but I like Tablepress a lot, so I decided to add an extension to “fool” Tablepress into thinking the data came from a Tablepress table. My code is really only prototype standard, but basically it works as follows:
- 1Create a dummy table where the first column contains an sql statement – SELECT col1, col2… FROM table1
- Header row has column ‘sql’ and Footer row also (if you want to use Footer and Columns Filter capability)
- New shortcode is like
[table id=1 sql=2 sql-filter='col1=value OR col2=value']
- The ‘sql’ setting is the row in which the sql statement exists in the table (it always expects it in column 1). I was able to use the same dummy table for many different sql statements.
- New extension (based on row-filters extension), calls the sql statement using wpdb and places the result in the $table[‘data’] array. It also sets the $table[‘visibility’] values accordingly.
</eol>
This solution worked for me because I have some very large tables – up to 50K rows – but I only need to display a subset of the data on any given page. The alternative was to divide my data into a few dozen smaller tables, but this would be very difficult to maintain in the long term.
@tobiasbg , I can share with you what I have done if you want to private message me.
Cheers,
Catherine
