search

volosoft / jtable

A JQuery plugin to create AJAX based CRUD tables.
http://www.jtable.org
1.1k stars 506 forks source link

table doesnot display data with dynamic innerjoin query. The query is created on keyup of multiple dropdown list #712

Open newprogrammer opened 11 years ago

newprogrammer commented 11 years ago

The table is created but the data is not shown. for a query like: SELECT c.c_name,a.a_name,Access.item_Code FROM Access INNER JOIN c ON Access.c_id=1 AND c.c_id=1 INNER JOIN a ON Access.a_id=1 AND a.a_id=1;

check the image: imine

debianlinux commented 11 years ago

There is a huge insulation between the SQL query and what is viewed on the jtable. First verify outside of jtable that your query is returning the requisite information under the appropriately named columns. Then verify that your server side script is correctly parsing the query data into a JSON formatted associative array and that that array also includes an index named "Result" and that its value is set to "OK" and that your column data indexes are found under a an index called "Records".

The picture you have posted can be generated simply by configuring a javascript code section to display those columns and have zero SQL trying to feed it or it can be done by having the same configuration and SQL feeding it perfectly good data but not formatted correctly for jtable to parse it.

newprogrammer commented 11 years ago

help First of all thanks for the kind reply, All you said id true and correct moreover my javascript is correct, my json result is also according to jtable requirements. the scenario is: I have multiple select menus, for a simple example if a user change menu1, a query is created like: SELECT c.c_name,a.a_name,Access.item_Code FROM Access INNER JOIN c ON Access.c_id=1 AND c.c_id=1; when the user change menu2 the query is updated by adding another innerjoin and is like: SELECT c.c_name,a.a_name,Access.item_Code FROM Access INNER JOIN c ON Access.c_id=1 AND c.c_id=1 INNER JOIN a ON Access.a_id=1 AND a.a_id=1; and for changing menu3 its like: SELECT c.c_name,a.a_name,Access.item_Code FROM Access INNER JOIN c ON Access.c_id=1 AND c.c_id=1 INNER JOIN a ON Access.a_id=1 AND a.a_id=1 INNER JOIN b ON Access.b_id=1 AND b.b_id=1; ..... and so on by changing more menus the query is longer and longer. Important thing to be notice is the jtable fields options (array or object) is also contineously changing with menus ... for menu1 change like: { {model_id:{key:true,create:false,edit:false,list:false},Item_code:{title: 'Item_code',width:'16%'}}........... for 2 its like:{ {model_id:{key:true,create:false,edit:false,list:false},Item_code:{title: 'Item_code',width:'16%'},Item_desc:{title: 'Item_desc',width:'16%'}} and so on.... Thanks again for your time and sorry if i'd not explained it well. have a nice time. for more info consider the screenshot. keeping in mind that the table in screenshot is not jtable. WHAT I'M THINKING IS JTABLE DOEN'T WORK WITH JOINS IN QUERIES??????

debianlinux commented 11 years ago

I won't be able to really dig into this one today as I do have a deadline to meet but I can say that I routinely use JOIN in my queries to no ill effect on jtable. jtable really should have no idea a JOIN was used as it is just reading an array of values after the fact. That is, it is not sensitive to how, exactly those values got there to begin with. Without having really studied your issue I am sensing that maybe jtable is not fetching updated information as your queries overwrite values within the array. You may need to be inserting some reload methods along the way.

An unrelated but still good example of this is a table that contains fields that are populated by way of dropdown options which are separate queries whose results are cached by jtable. On this same table is an edit column that contains an icon that when clicked opens a child table that presents the data that comprises those same dropdowns. In this way one can add, remove, edit the items that appear in the dropdown. However, since jtable has cached those dropdown options the user will find the exact same list of dropdown options despite his childtable edits unless the user manually refreshes the page after making changes to the childtable or the developer inserts some reload methods that takes care of that behind the scenes.

newprogrammer commented 11 years ago

Thanks Alot, I'd love to learn from demo of jtable working on multiple innerjoin query, to compare myself with that and find out where my code leaves the track.. if you have one kindly share a fiddle link with me. best regards