Show in table data from a query

xnrsisbi

Hi,

i need to be able to use my own mysql query, export results as json and then show it in a DataTable.
This table doesn't need any type of edition is just a "read-only" table but i need to be able to use my own mysql query.

Can anyone please point out some code examples?? i've searched the examples available but none uses this kind of approach.

Thanks in advance

allan

You can source the data from anywhere you want. All you need to do is get the data from the database and make it available to DataTables in a JSON array. DataTables really doesn't care where the data comes from :-)

Allan

xnrsisbi

Hi Allan,

thank you fo r your answer, i know that DataTables and Editor allow us to use multiple data sources.. great job on that... my question was on another direction.

I'm using DataTables and Editor (using server-side php) without a problem, but again those only allow me "normal" queries and joins, what if i need to use a query more or less like this:

SELECT
product.name ProdName,
(SUM(if(transaction.type = 'in', transaction.quantity, 0)) - SUM(if(transaction.type= 'out', transaction.quantity, 0))) Inventory
FROM transaction inner join product on transaction.product = product.id
where transaction.uid = '$uid' GROUP BY ProdName HAVING Inventory > 0 ORDER BY Inventory ASC;

i can do pretty much all the query the only one that i don't know exactly how to do it is the calculation on field "inventory".

Hope i was more clear about my needs now.
Many thanks

allan

There is no option in the Editor PHP libraries to use SQL functions at the moment - you would need to execute the query directly (possibly using the sql() method or any other way you want to access the database). Then dump the data out in a JSON format.

Allan