Dynamically populate the HTML Dropdown template using pocketquery plugin in confluence Edited

Hi i am new to pocketquery and till now i just know how to write and MySQL query to read the MySql db and populate the table.I want to populate a dropdown list with the table data.

The Query to populate data is quite simple 

' select distinct AccountId from Accounts ' I want the values returned by this query to be the options of select html tag (dropdown).

After the dropdown is populated i would like to select one one value and based on the selected value it will fire a query to MySql DB with selected value as input parameter the 2nd Query is something like below 

'select AccountId,AccountName from Accounts where AccountId=the input parameter selected in dropdown'.

Please help me @Felix Grund.

3 answers

1 vote

After looking at the support desk tips I found out that I needed to add the following code as part of the form

<form method="get" class="aui pq-dynamic-parameter-form" action="">
## store GET parameters that are not PQ specific in hidden fields so they don't get lost
#foreach($entry in $req.getParameterMap().entrySet())
#if(!$entry.key.startsWith("pq_") && $PocketQuery.getValueFromMapEntry($entry))
<input type="hidden" name="$entry.key" value="$PocketQuery.getValueFromMapEntry($entry)" />
#end
#end
<button type="submit" class="pq-change-button aui-button aui-style aui-button-primary">Change</button>

<div>
<label for="pq_spacekey">spacekey</label>
<select id="pocketquery-select-example" name="pq_spacekey">
#foreach ($row in $result)
<option value="$row.SPACEKEY">$row.SPACEKEY</option>
#end
</select>
</div>
</form>

It is now remembering the pageid to submit the parameter values

thanks

Hi @Pascal Mayné 

very cool that you figured it out yourself and also provided the solution here for others to find! :)

Best regards,
Sven

Hi, 

I can suggest you a workaround: you can pull the whole table with all AccountIds and then filter it with Table Filter dropdown filter.

Hey Aaksh,

sorry that you didn't receive an answer yet. Felix is currently very busy and we have basically moved our support to our service desk for PocketQuery.

---

Regarding your question:

To achieve what you want you basically have to use 2 PocketQuery Macros. One for building the dropdown menu and then the other for using the selected value to run a new query.

So let's start by creating the template for the first Macro which will build your dropdown list.

For the first query i'm just going to use a very simple example and get all the spacekeys from my Confluence database with this query:

SELECT SPACEKEY FROM spaces;

We now want to build a dropdown menu from the results, which also passes the results to the other Macro. For this we will adjust the "change params"-template a little:

<form method="get" class="aui pq-dynamic-parameter-form" action="">
<button type="submit" class="pq-change-button aui-button aui-style aui-button-primary">Change</button>

<div>
<label for="pq_spacekey">spacekey</label>
<select id="pocketquery-select-example" name="pq_spacekey">
#foreach ($row in $result)
<option value="$row.SPACEKEY">$row.SPACEKEY</option>
#end
</select>
</div>
</form>

This is just a very simple standard HTML dropdown menu. Remember: If you want it to look more fancy you can always use the AUI styling elements. So this is what you should get:

Unbenannt.png

Now we need to build the second query. Again, i will use a very simple example just to show that it works. This is where you need to include a dynamic parameter. (make sure the NAME of the dynamic parameter matches the pq_NAME in the template of the first query, or else this won't work)

SELECT * FROM confluence.spaces
WHERE SPACEKEY = :spacekey

Now simply insert the macro below your first macro and make sure that you enable dynamic parameters in the macro parameters.

You can now choose something from the dropdown menu and the second query will use it to get its results.

result.PNG

If you need any further help, please don't hesitate to raise a ticket at our service desk.

---

Thank you for using PocketQuery!

Best regards,
Sven (Scandio)

Hi I have managed to replicate your way with our confluence page, however after initial success it is no longer working. it looks like the change button when used can not find the page anymore. do I need to add the pageid of the current page as part of the submit form?

Hi @Pascal Mayné 

could you raise a support ticket at our service desk for PocketQuery and tell me exactly what you are trying to achieve? Please also include your current template and query (feel free to censor sensitive data).

Best regards,
Sven

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Tuesday in Confluence

Confluence Admin Certification now $150 for Community Members

More and more people are building their careers with Atlassian, and we want you to be at the front of this wave! Important Dates Start the Certification Prep Course by 2 April 2019 Take your e...

183 views 2 10
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you