Can I use WP Data Access to create a view?

Hi Howard! ??

There are no dumb questions. Only dumb answer…. ??

So I need to be careful… WP Data Access uses jQuery DataTables to built a publication table. You can add your own logic to a publication to implement grouping. Here is an example:
https://datatables.net/examples/advanced_init/row_grouping.html

I never tried this myself. If you succeed, I would be greatful for an example. If you need help, let me know. This topic might be interesting for other plugin users as well.

May I ask you a question in return? Can I add your page to my portfolio? You’ve done a good job!!

Warm regards,
Peter

Thanks Peter.

Regarding your question first: absolutely, and I’d be honoured! My site is nothing without your plug-in, to be honest. I’m pleased you think it’s a good job!

Second: if I created my own view using plain SQL in the back-end (which is where I’m more comfortable, to be honest), would it be possible to use Data Designer to reverse-engineer that back into something WPDA “thinks” and treats as a table for the purposes of creating a publication, etc?

In other words, can I get WPDA to do all the heavy lifting of turning a database object into something that can appear nicely on a web page, but do all my coding for said object in vanilla SQL via the MySQL console?

That said, if I can wrap my head around jQuery Data Tables, I will try to do something in that direction, too!

Many thanks for all your help, now and in the past ??
Regards
Howard

Hi Howard,

>>> Second: if I created my own view using plain SQL in the back-end (which is where I’m more comfortable, to be honest), would it be possible to use Data Designer to reverse-engineer that back into something WPDA “thinks” and treats as a table for the purposes of creating a publication, etc?

You don’t need to do any reverse engineering. You can just write your own create view statement, save it in a script file (make sure it has extension .sql) and then execute your script from the Data Explorer. After that you can use your view in any publication just like you use a table.

Does this help?

>>> Many thanks for all your help, now and in the past ??
Your welcome,
Peter ??

That definitely helps!

Three minutes into that jQuery link, I was mind-boggled, I’m afraid!

Importing SQL statements puts me on much firmer ground!

??
Howard

That’s what I thought as well! ??

There is another solution to consider if you are willing to write some php code. If you download the Code Manager and add a php shortcode to your page, you can connect to any database known to WP Data Access (including remote databases) like this:

$wpdadb = WPDADB::get_db_connection( ‘your-schema-name-goes-here’ );
$rows = $wpdadb->get_results( ‘select * from your-view-name‘, ‘ARRAY_A’ );

Those two lines of code will give you much more control over your output as you can do anything with $rows you like.

Good luck,
Peter

This looks like a good idea for me too. I’ll do some research.

Thanks