Retrive and display MySql query results on form submission

(Pawel Masloch) #1

Hello,

I am looking for a bit of a guidance on how to retrieve and display results returned upon form submission.

So, I have been testing this form (a simple one: an input and a submit button) which uses a query to SELECT

  • FROM myDB WHERE id = %field_q1% (it is actually MySQL DB and %field_q1% is a keyword example for input field: ID: #field:q1).

For the purpose of this exercise, my db connection as well as the query has been set up in ‘Submission Actions=>Run Database Query’. Unfortunately, every time I submit the form I only can see following message: ‘Unattached Questions; My Label Name, My Input Value’ as a response but not the result(s) I’d expect.

In form’s Body Copy=>Page Contents, once again I use very simple set up:
%form_errors_message%
%question_label_field_q1%
%question_field_field_q1%
%submit_button%

I didn’t need a confirmation or thank you page so couldn’t bother and left it out.

Now, my submission log shows every submitted entry correctly. Yet again, not being able to show the query result(s) on the page where the form is (Form Submission URL: current URL).

Just to confirm, my DB connection is fully operational and tested. I have set up a separate DB Connector, DB Data Source showing shadow assets correctly and Asset Listing nested in a Standard Page in order to display any results using exactly same credentials as per connection above.

My current Matrix version is:5.1.3.0

So, in a (rather large) nut shell, I wonder where did I go wrong ?..

Thanks for any help.

(Bart Banda) #2

So you basically want to store the response of the DB query against the form submission somehow? Via like a metadata field or attribute for example?

If so I don’t think that’s supported, i think it’s mainly just meant for submitting a query and that’s it, not caring what the response or result it, unlike the REST submission action where you can print the response data into a session variable which is then accessible via a keyword.

Might need to go in as a feature request, but not sure how easy/hard it would be to implement.

(Pawel Masloch) #3

Hello,

Thanks Bart. Sorry I took longer than usual. Well, at least I can stop pulling my hair over this method now.
Having siad that, I had a little time to revist this yesterday and I felt there must be a way around this using other tool(s) within Matrix somehow…

So, I set up a ‘Set Session Var From Form Action’, set the response value and test it. As expected, my global session var picks up the form field input correctly and I am able to use it e.g. in metadata or print it on page upon submission. That’s easy… Next, I went on to set up DB Source asset. It connects to MySQL - OK , query returns and populates shadow assets as specified in the select query. And my asset listing prints each and/or all of them. Also, I got a working record filter that maps one of the record’s set field keyword (a DB column) with my Dynamic Input Var keyword. So when I use e.g. ‘Set value’ in Data Mappings and fill it in with valid value I get the result no problem.

However, now when I tried to use my Form’s Session Var in Dynamic Var Data Mapping in DB Source nothing has been passed through.

Is it actually possible to get it working this way? Would you be able to suggest or point me to right direction from here?

Thanks.

(Bart Banda) #4

Sounds like it should be possible, but hard to say without actually seeing the implementation up closer.

Maybe the session var value is not set at the time you request the DB Source?

How is it all put together? Are you requesting the DB Source on the thank you page of the custom form?

Can you try using a different dynamic value instead of session var? Such as %globals_get_var% and passing var=value in the URL when viewing the Asset Listing / DB Source?

(Michael Williams) #5

I also want to know how to display the results of a MySQL query. Once the form is submitted, I just get the data values printed. Ideally I’d like it to display the results of the query, but I get the feeling I need to just query shadow assets instead.
Hard to believe that nobody except Pawel and I have wanted to do this?

(Pawel Masloch) #6

Hello Michael,

Having managed to get it working eventually, I think it may be about time to reveal the step by step to you all!
Needless to say, I do need to give some credit to Squiz UK who did contributed in the process and made me realise chain of events running in the background. That was a key to solve this little riddle. :wink:

So, here it is how I got MySQL DB queries up and running and providing data listing in SQ Matrix as well as on the web site.

In our case I have been working on implementing LLPG - post codes and other street name based lookups on our site.
Initially, this connection had been configured in our test SQ Matrix 5.1.3 (as mentioned above), but since then it has been successfully migrated to the live server.
Also, I currently use MySQL 5.6.XX. And, each instance is installed on separate environment and all are available under DMZ.

  1. Set up your DB connectors. Outside of the web root asset I have a dir called ‘resource’ which hosts my DB connector and DB source.
    Although you can use straight connection in DB source I prefer keeping it separate as this way I can reuse the connector with different source assets at the same time - straight forward.
    Once you have your connection to MySQL DB successfully established we can leave it as it is for now and move on to the next step.
  2. Another easy task is to create a simple page under your root that will eventually host e.g. a simple form.
  3. Set up your form that will provide means to enter data used in DB query.
    I’d normally use the ‘Bodycopies’ section to set up form’s label, input and submit button and in addtion a separate ‘results’ page (SQ native ‘Thank You’ container in forms).
    Please note the ‘results’ page will play important part in the process so it’s got to be enabled under ‘Use Bodycopy?’ in ‘Form Contents’.
  4. Create your asset listing that will display data set upon a DB query.
    On ‘Details’ screen ‘Asset Types to list’ => ’ Data Source Record Set’, ‘Asset Locations’ => ‘Root Nodes’ => pointing to your ‘Data Source’ asset.
    Everything else as default on this page.
    a) Page Contents: whatever html formatting you may need e.g. I do tabular data display so here it is where I start my table and absolute must: %asset_listing%
    b) Type Format: this is where my data will go so I’d need to use the data set keywords. However, since we are not querying anything on the source yet we won’t be able to pull them from DB Source’s Details ‘Available Keywords’ section yet. Good thing is SQ matrix will have two predefined formats and in most cases it will look like this: %data_source_record_set_MYSQL-TABLE-COLUMN-NAME% (is the one I prefer using)

Now, since we have all the assets ready. And our connector(s) can talk to MySQL DB we are ready to wire it all up!

  1. Nest the form in a standard page previously created.
  2. Nest asset listing in form’s result page. Under nested content container => ‘Send the following additional GET variables to the asset:’ => Variable Name:e.g. myVarName_ (any meaningful word), Variable Value: %response_ASSETID_q1%. The idea is to grab data value from input field and convey that in to the DB query.
  3. Data Source => right click => Dynamic Inputs:
    Dynamic Variables => create a new name, leave default empty
    Data Mappings => Parameter is your Dynamic Variable Name, GET Variable Name => myVarName_ (as per step 2)
  4. Data Source => right click =>Record Builder
    Filter Options => Filter Status: On, Logical Grouping: Match on ALL conditions, Filter Mode: Return the records
    matching the given conditions
    Record Filters => Record Set Field Name: create a new name (I’d normally use the same as Dyn Var - keep it simple),
    Value to match => Data Mappings Parameter in format: %%%%MY-PARAMETER-NAME%%%%
    Match Type => Regex match
    Match Case => Yes
    Allow empty => No
  5. Data Source => right click => Query Builder
    SQL Query => SELECT * FROM mytable WHERE mycolumn REGEXP %%MY-VALUE-TO-MATCH%%;

…and that is it, folks!..

It is worth noting that this DB query method looks for all data matching my dynamic parameter. So, e.g. for streets lookup it will bring back all that has got ‘Oxford Street’ in it.
In order to pin point one entry you can use ‘String Match - Exact’ in Match Type drop down and amend query to ‘=’ instead of REGEXP, etc. You can expand on this example and experiment a little.

Also, because nothing has been fed back in to the query at this stage you won’t see any record(s) set under DB source. However, if you wish to use a ‘like for like’ approach in your DB query and replace dynamic variable with static value you should be able to start getting results that will be passed to your asset listing which effectively will display record set in the forms result page which in the end shows it to a user in the front end’s simple page created in the first place. :slight_smile: :smiley:

Good luck and have fun!