Shortcode to display single fields results

  • CrikeyWeb

    (@swiptly)


    5 years, 10 months ago

    Hi Peter,
    Firstly thankyou for the truly great plugin, I have setup an app and the backend data administration side is awesome, a real time saver!

    I have hit a stumbling block however in displaying the public data. First some background:

    I have setup 3 tables and 1 view to join the fields in a many-to-many relationship, this is working perfectly.

    I have setup a WP php template which uses two $_GET variables which are parsed in the url in order to execute SQL query using these variables. This is how I wish the page to be utlised.

    I have published the view and in the table format using the standard shortcode, functionally again this works fine.

    The stumbling block is that I wish to display the results in a vertical/column (side-by-side) (1-3 sets will be returned) display eg:

    Engineer 1 Engineer 2 Engineer 3
    Name: Engineer 1 name Name: Engineer 2 name Name: Engineer 3 name
    Phone: Engineer 1 phone Phone: Engineer 2 phone Phone: Engineer 2 phone
    Email: Engineer 1 email Email: Engineer 2 email Email: Engineer 2 email

    I could potentially do this outside of the WPDataAccess plugin using pure PHP/MySQL however is there a way to achieve this using WPDataAccess?

    I appreciate that there is something that I could be missing here and what I want to achieve may be fairly simple, but I’m going about it the wrong way?

    I have attempted also to use the WHERE clause within the table, but I guess it doesn’t accept variables for the query (based on the two $GETs)?

    The ultimate goal would be to have a shortcode in which I could call in a template eg

    [engineer no="1" field="name"] [engineer no="2" field="name"]

    With this I could sould setup the page using whichever page/template I like.

    I have looked into the wpda-cwg-extension in which I may be able to set the $_GET variables for the WHERE clause dynamically, but I would still have the issues with the display?

    Perhaps this could be achieved with CSS and/or jquery (not my strong point!)?

    If you could please point me in the right direction I would greatly appreciate it!

    Thanks

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

    (@peterschulznl)

    5 years, 10 months ago

    Hi swiptly,

    Great to hear you like the plugin! 🙂 And you seem to know how to use it very well…

    If you are on version 3.1.4 of the plugin, you can use POST and GET arguments to build a where clause. The functionality was really just introduced in version 3.1.4! 🙂 I just didn’t have the time to document this feature yet. Sorry! I’ll do that later…

    To add a condition for a specific column to your where clause, you can add an argument for that column to your request. The name of your argument must start with "wpda_search_column_" appended with the column name. This item:
    <input type="hidden" value="John" name="wpda_search_column_person_name"/>
    Will add this to you where clause:
    person_name like 'John'

    Can you please explain your question about the vertical column layout a bit more? I’m not sure if I understand the question correctly. Is this about the list table? Or the data entry form? Can you add a sketch or a wireframe?

    Thanks,
    Peter

    Thread Starter CrikeyWeb

    (@swiptly)

    5 years, 10 months ago

    Hi Peter,
    Thanks for the super quick response, much appreciated.
    What you have supplied might just do what I need.

    For the display reference (apologies for the poor output formatting from the OP!), here’s something I just knocked up. Which shows broadly the output desired.

    Thanks!
    Paul

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Paul,

    Thank you for the format. In your format you can see three engineers. Do you need paging button to move to the next/previous three engineers? Or is this hardcoded?

    Suppose you could “turn” the list table – showing rows vertical instead horizontal – is that what you are looking for?

    Thanks,
    Peter

    Thread Starter CrikeyWeb

    (@swiptly)

    5 years, 10 months ago

    The example page was just pure text, for demo purposes. I’m not sure what you mean by “turn” the tables? But, yes, vertical would be a start, however how would the columns list side by side?

    No, there’s no paging required. Essentialy it is searching for an engineer by postcode and the type of engineer, so the page is (pseudo);

    /website-url/page-with-custom-php-template?engineer_type=Electrical&area=SOMEWHERE

    The results will never be more than 3, so no paging required. Unfortunately due to the database data there is, strictly speaking, more than 3 results as there are several Towns in the database with the same postcode which by default are listed with WPDA, in my SQL (outside of using WPDA) I am using DISTINCT and LIMIT 3, which may not be perfect, but does provide the functionality desired.

    It seems that I’m not able to use DISTINCT or LIMIT in the sql_where clause within Data Publisher? Perhaps there is something I’m not aware of with WPDA that can do this?

    I would consider removing all the Town references to remove the duplication of the postcodes as it’s a “nice to have” to search via Town, but not strictly required at this stage of the application.

    I attempted to reference the input types as you suggested for the table search, but I am definitely missing something here. Where the does hidden <input> references go, in the page template or in Publisher somehow?

    I hope this is’nt too much of a pain Peter! The application is “so close* to completion!

    If the desired output simply can’t be done with WPDA, then that’s OK!

    I will continue using WPDA for the backend administration and custom code the page template/search results page with PHP variables looping through the SQL array.

    Cheers!

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Paul,

    The plugin performs server side processing by default. To add paging functionality the LIMIT keyword is used. Therefor you cannot use the LIMIT keyword yourself. But you can turn off paging by setting paging to No in the Data Publisher. The plugin will then load all data into your html table on page load. This is not recommended for large tables, but for three rows this sounds like your best strategy anyway… 🙂

    But there is an easier way. Please checkout the dom options (can be added to table options advanced). The dom options allows you to hide specific elements. You can combine the dom option with options pageLength to show your three rows and nothing else.Here are the links to the documentation:
    https://datatables.net/reference/option/dom
    https://datatables.net/reference/option/pageLength

    I think this gives you the result your’re looking for (without testing):
    {"dom":"t","pageLength":3}

    Now you just need to add some CSS to turn your table… 🙂

    Hope this helps,
    Peter

    Thread Starter CrikeyWeb

    (@swiptly)

    5 years, 10 months ago

    Thanks Peter,
    The code added doesn’t seem to make any difference, but as you say, it’s untested. Looks like I have some reading to do 😉

    I’ll dig into it and will let you know I appreciate your time.

    For now please can you advise how I get the Where clause to use $_GET parameters? I am using v3.1.4.

    Thanks

    Thread Starter CrikeyWeb

    (@swiptly)

    5 years, 10 months ago

    Hey again… I misread your response, from the first set I disabled paging, then when setting the dom, of course it was ignored. So thanks your solution with paging enabled for the 3 rows works great! <facepalm>

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Paul,

    Great that worked! Your idea to show rows vertically got my interest… 🙂

    If you set dom to t and pageLength to 3, try this CSS to flip the rows:

    .dataTables_wrapper table thead,
.dataTables_wrapper table tfoot {
	display: none;
}
.dataTables_wrapper table tbody tr {
	display: grid;
	float: left;
	width: 33%;
}
.dataTables_wrapper table tbody tr td {
	width: 100%;
}

    It works for me. Now I’m curious if it works for you… 😉

    Good luck,
    Peter

    Thread Starter CrikeyWeb

    (@swiptly)

    5 years, 10 months ago

    Hey Peter,
    *That* is awesome!! Thankyou!

    Glad it got your interest and I hope this will work for anyone else wanting the same display.

    I guess unfortunately that there’s no way to display the field names as well? so I guess that’s the display:none, right? As soon as I adjust that to display then it all goes out of whack. I guess it loops side by side easily without the <thead>?

    Thought to self: Hmm I wonder if it really needs the field names anyway?

    Also if you could provide the syntax for putting the $_GET parameters into the where clause that would be awesome.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Paul,

    You can also add the labels. You just have to add the thead row in front of the first tbody row. Or use the tfoot element. Whatever you prefer. Here is the CSS:

    .dataTables_wrapper table tfoot {
	display: none;
}
.dataTables_wrapper table thead tr,
.dataTables_wrapper table tbody tr {
	display: grid;
	float: left;
	width: 24%;
}
.dataTables_wrapper table thead {
	float: left;
	width: 24%;
}
.dataTables_wrapper table tbody {
	display: contents;
	float: left;
}
.dataTables_wrapper table thead tr th,
.dataTables_wrapper table tbody tr td {
	width: 100%;
}

    You still have to do some fine tuning…

    Regarding your question about changing the where clause with a GET argument, I’m afraid I misunderstood you. Sorry for that! The solution I gave you works for Data Projects. Not for the Data Publisher. I thought you were using Data Projects…

    For the Data Publisher you need another solution. If you want to write the necessary logic yourself, please check out the documentation here:
    https://datatables.net/examples/api/multi_filter_select.html

    You can also buy the full text search add-on which does it all for you. The add-on will be available next month.

    Good luck,
    Peter

    Thread Starter CrikeyWeb

    (@swiptly)

    5 years, 10 months ago

    That’s great, thanks, works 90% as you’ve supplied it already, so sure just a few tweaks and it will be there! Excellent work!

    Ahh OK, and no need to apologise. ‘Tis a shame though! I can probably get around $_GET params.

    I’m definitely interested in teh full text search add-on, I think it’ll do very well.

    Thanks again for your help today and teaching me some CSS 😀

    Enjoy your evening!

    PAul

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Paul,

    It would be great if you could share your final CSS solution on the forum so other plugin users can benefit from it as well. Let me knmow if you need help! 😉

    Best regards,
    Peter

    Thread Starter CrikeyWeb

    (@swiptly)

    5 years, 10 months ago

    Sure I will be playing with it tomorrow and will post it once I figure it out.

    Cheers 😀

Viewing 13 replies - 1 through 13 (of 13 total)

The topic ‘Shortcode to display single fields results’ is closed to new replies.