Requering mySQL database for new dataset, based on multi table SQL script.

Larry27

http://www.baldwinproject.ca/app/on/expenses/byFunctionLine.php

Hi, I am working on a project that represents municipal statistical data. I've made a pretty good go so far, and I am very pleased and impressed with the functionality of datatables so far. I am having a bit of a challenge at the moment that I need some help with though.

My project is sourced from a mySQL database of more than 10 million records, spread over nearly 100 tables. It is easier for me to work at my data modelling through mysql view script which is imbedded in my webpage. My scripts contains multiple tables, which are strategically joined to represent data from multiple tables. I am currently trying to figure out the best way to 'toggle' my datasource, based on two Select2 boxes at the top of my form. One represents the 'function' area of the budget, i.e. police, fire, ambulance, and the other represents the year. It's pretty straight forward, and as you can see from my page, I have the filtering and formatting working pretty well. Because my page uses my source database for other purposes I am not really using any of the easily searchable datasource examples, so I am having some challenges.

The table I am working with is Schedule_40 and the debugger shows it as 'client side', which is not the case. It works, but I am assuming this little tidbit may be relevant to may issue.

Information about 4 tables available

Schedule_40

Data source: DOM
Processing mode: Client-side
Draws: 2
Columns: 19
Rows - total: 364
Rows - after search: 364
Display start: 0
Display length: 10

The long and short is that I want to be able to re-run my SQL script based on the values selected in the change event of my select2 boxes, and rebuild the same table with this new data. Any thoughts you can offer would be appreciated, even if you could perhaps point me in the right direction that would be a start.

Thanks In Advance,

Larry

colin

I assume you'll be loading the data via Ajax. If so, you could pass back to the server the values of your Select2 boxes in ajax.data. You would trigger the reload with ajax.reload(). Alternatively you could amend the URL with ajax.url().load() for the same purpose,

Colin

Larry27

Hi Colin,

I looked at both those solutions, and did a little testing, but I wasn't sure I was barking up the right tree. If you say that's my preferred approach I'll look at those more closely.

I'll get back to you, and thanks

Larry