Support » Plugin: WP Data Access » Can I use WP Data Access to create a view?

  • Hi Peter: It’s been a while since I had to bother you, and I think this one might just be me being stupid!

    But I have a long list of music ‘plays’ on that page I’ve linked to, and I’d like to summarise/aggregate them (so: Average Plays Per Day X, Top Genre: Y …and so on).

    I’d have no trouble creating a view with plain SQL on the backend with the requisite grouping, sorting and aggregating… but can you do it in WP Data Access?

    It occurs to me that though I’ve used WPDA for more than a long time, I’ve only ever used it to create and populate and display tables.

    Sorry if it’s a dumb question.
    Best wishes
    Howard

    The page I need help with: [log in to see the link]

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    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

    Thread Starter dizwell

    (@dizwell)

    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

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    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 πŸ™‚

    Thread Starter dizwell

    (@dizwell)

    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

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    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

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Can I use WP Data Access to create a view?’ is closed to new replies.