Dynamic Inputs for DB Data Source

[noel]

Hi…


I'm trying to make an asset listing page working in conjunction with a DB Data source, to list items from a database. I am trying to pass a parameter to the asset listing in order for this to be passed to the related DB DataSource as a query dynamic input so as to be able to search only for specific items. Can someone help please? as the Data manual does not cover use of dynamic inputs.



thanks in advance.

[nnhubbard]

I have not finished setting this up myself, but Greg did give some help in this thread: http://forums.matrix.squiz.net/index.php?showtopic=5436

[noel]

says link broken…

[nnhubbard]

says link broken…

Oh, it is in the client only forum. Are you a Squiz client? I will post what Greg put:

[quote]To perform searches with the DB Data Source, you create a query with a dynamic variable in it (sourced from a GET var normally). Then on the frontend, you put the GET var onto the query string of the asset listing page. So when you request your listing, the DB Data Source fills the placeholders in the query with the value of the GET var and performs the query. The results then get listed as normal.[/quote]

[quote]Yes, you need to map that dynamic variable to a GET var. Let's call it "degree".

Then in your query, make sure you add a WHERE condition: SELECT ... FROM table WHERE table.degree = "%degree_keyword%"

Point the asset listing at the DB Data Source as normal. You will need to create your own form for users to select the degree they want to search for. When you submit the form, make sure it is a GET request and the URL will end up like www.example.com/asset_listing?degree=superHotDegree

The asset listing will ask the DB Data Source to run its query. It will use the GET var "degree" to insert a user-defined value into the query (it also does the anti sql-injection for you) and return a list of degrees "WHERE table.degree = <degree GET var>".

This is effectively a search as the query the DB Data Source executes is no longer static; it is based on user input.[/quote]

[noel]

Thanks
Managed to get this to work, however, with some changes to what you posted.



For what version of Matrix is yours valid?

[nnhubbard]

[quote]Thanks
Managed to get this to work, however, with some changes to what you posted.



For what version of Matrix is yours valid?[/quote]



We are running 3.18.8, but I never found the time to finish getting this to work.



Could you post what steps you took to get it working?

[PeterSheppard]

In 3.18 it appears that you mustn't quote inputs, as the DAL does it for you.


So whereas in 3.16 you'd have had WHERE value = '%%MYVARIABLE%%'

In 3.18 you just have WHERE value = %%MYVARIABLE%%

[cokefour]

Does this method work in 4.6? I have options to input variables on a separate screen so I was wondering if I need to rejig my query directly or if filing in these fields does the job.

 

If the image isn't working try https://www.dropbox.com/s/rgm7gy899kciva4/datamapping.JPG?dl=0

[joel]

Does this method work in 4.6? I have options to input variables on a separate screen so I was wondering if I need to rejig my query directly or if filing in these fields does the job.

 

If the image isn't working try https://www.dropbox.com/s/rgm7gy899kciva4/datamapping.JPG?dl=0

 

you will want to add a data mapping in the area below (ie. a GET var) so you can actually use a dynamic input for those variables

 

to actually use them you will have to insert them in your query on the query builder screen like 

select * from yourtable
where somevalue = %%yourdynamicparam%%

[cokefour]

I've now got the orange error message so not sure if this will work yet. Here's the set up 

 

https://www.dropbox.com/s/5g5tjtfduc0rlz0/datamapping2.JPG?dl=0

 and the SQL has 

WHERE c.caseSummaryRunningNumber = %%runnum_keyword%%

in the where clause.

[cokefour]

I've changed the WHERE to HAVING which got rid of the orange errors in the SQL syntax but not getting any listings from the front end when I add queries to the URL.

I've tried www.domain.org/page?caseSummaryRunningNumber=310 and www.domain.org/page?runnum_keyword=310 to test but nada.

[cokefour]

Has anyone got a screenshot of a working dynamic input variable that works? I'm pulling out what little hair I have left now.

[joel]

take the %%s off your get var names - you only use that syntax in the query builder screen dynamic-inputs.png (3.49 KB)

[cokefour]

Thanks J.P. I figured it out minutes after pulling out the last strand. :) All working fine now, just have to work out the intricacies of using wildcard LIKE queries with the %%keywords%%.