jQuery Grid Plugin - jqGrid - Topic: Reloading grid when having multiple tables related between themselves

Luigino on Reloading grid when having multiple tables related between themselves

Thu, 25 Nov 2010 19:53:57 +0200

Hello OlegK!!

Indeed you were damn right!!! it was that loadonce:true the guilty of my issue eheheh

And now it works.

Thanks a lot again!

Ciao,

Luigi

OlegK on Reloading grid when having multiple tables related between themselves

Thu, 25 Nov 2010 11:10:45 +0200

Hello Luigi,

probably you have problem with reloading of grid because you use loadonce:true. See my old answers on the stackoverflow.com: this for example).

Moreover Luigi I don't give any suport of jqGrid. I am just a user of jqGrid. So if I found my time today to write an answer there are no garantee that I'll do this at morning. I hope you understand me.

Best regards
Oleg

Luigino on Reloading grid when having multiple tables related between themselves

Thu, 25 Nov 2010 09:57:22 +0200

No suggest about what to check to force reloading grid recalling the PHP in the url parameter about my little issue? (so that PHP would get back all complete data in the grid)

Thanks again

Ciao,

Luigi

Luigino on Reloading grid when having multiple tables related between themselves

Tue, 23 Nov 2010 22:06:18 +0200

HI Oleg!

First, thanks again for your answer. Actually I tried a little test, I reduced the code to minimal to check the functionality as:

    jQuery(document).ready(function(){
       var ids=0;
       jQuery("#datatableoffers").jqGrid({
           url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/load_dataoffers.php?table_name=vvi_offers&q=1",
           datatype: 'json',
           colNames:['ID','Codice','Aeroporto','Aeroporto','Paese','Paese','Localita','Categoria','Categoria','Struttura','Struttura','Titolo','Descrizione ridotta','Descrizione estesa','Descrizione quote','Appunti di viaggio','Note interne'],
           colModel:[
                   {name:'offer_id',index:'offer_id',width:1,editable:false,hidden:true},
                   {name:'offer_code',index:'offer_code',width:60,editable:true,editoptions:{readonly:false,size:12}},
                   {name:'offer_ref_vvd_airports',index:'offer_ref_vvd_airports',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:airports},editrules:{edithidden:true,required:true,number:true}},
                   {name:'airport_name',index:'airport_name'},
                   {name:'offer_ref_vvd_states',index:'offer_ref_vvd_states',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:states},editrules:{edithidden:true,required:true,number:true}},
                   {name:'state_name',index:'state_name'},
                   {name:'offer_place',index:'offer_place',width:200,editable:true,editoptions:{readonly:false,size:100}},
                   {name:'offer_ref_vvd_categories',index:'offer_ref_vvd_categories',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:categories},editrules:{edithidden:true,required:true,number:true}},
                   {name:'category_description',index:'category_description'},
                   {name:'offer_ref_vvd_furnitures',index:'offer_ref_vvd_furnitures',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:furnitures},editrules:{edithidden:true,required:true,number:true}},
                   {name:'furniture',index:'furniture'},
                   {name:'offer_title',index:'offer_title',width:300,editable:true,editoptions:{readonly:false,size:300}},
                   {name:'offer_tiny_descr',index:'offer_tiny_descr',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
                   {name:'offer_long_descr',index:'offer_long_descr',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
                   {name:'offer_quotes_descr',index:'offer_quotes_descr',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
                   {name:'offer_travel_notes',index:'offer_travel_notes',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
                   {name:'offer_internal_notes',index:'offer_internal_notes',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}}
           ],
           rowNum:10,
           rowList:[10,20,30],
           loadonce:true,
           pager: '#pageroffers',
           emptyrecords: 'Nessun pacchetto disponibile',
           sortname: 'furniture_name',
           viewrecords: true,
           sortorder: "desc",
           caption:"Pacchetti vacanze",
           editurl:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/edit_dataoffers.php?table_name=vvi_offers",
           shrinkToFit: false,
           autowidth: true
       }).navGrid('#pageroffers',
       {height:750,width:800,top:30,left:100,reloadAfterSubmit:true}, // edit options
       {height:750,width:800,top:30,left:100,reloadAfterSubmit:true}, // add options
       {reloadAfterSubmit:true}, // del options
       {}, // search options
       {alerttext:'E\' necessario selezionare dapprima un pacchetto...'}
       );

and I put in the load_offers.php, mentioned in url parameter, a little test code to write in a txt output file the JSON result and I tried to add new record which it should re-call the load_offers.php to reload the grid and have the output txt test file. But the result I see is a partially filled record and no output txt test file which would mean only a thing: the load_offer.php file wasn't called to reload the grid.

Any idea? maybe it needs a different parameter to FORCE to re-call load_offers.php emptying previously the grid... if yes, which it is?

Thanks in advance for your support

Ciao,

Luigi

OlegK on Reloading grid when having multiple tables related between themselves

Tue, 23 Nov 2010 11:01:36 +0200

Hi Luigi,

It seems to me that you have problem in your server code. Firs from the screen-shorts which you posted one can see, that you has 1 from 0 pages. So the server returns wrong value for the total value in the JSON response from the server. And it is not the only error.

I have no idea how the server code looks like, but the sipmle SQL quers

SELECT OFFERS.ID, REF_AIRPORT_ID, NAME, LOCATION FROM OFFERS INNER JOIN AIRPORTS ON OFFERS.REF_AIRPORT_ID=AIRPORTS.ID

must give alwasy correct values back. Is you have caching problem and have to flush the changed values to the database or have another error... No idea... Nevertheless I would recommend you to search for the error in the server code.

Ciao
Oleg

Luigino on Reloading grid when having multiple tables related between themselves

Tue, 23 Nov 2010 02:19:15 +0200

Hello OlegK,

first thank you for your reply.

about second datagrid, that works well... once I select a record from first datagrid (datatableoffers) and I insert a new row in second datagrid (datatableprices)

But my problem is related in same datagrid where I tables related between them like:

table AIRPORTS table OFFERS

ID ID

NAME REF_AIRPORT_ID

LOCATION

so in the first datagrid I put columns (some unnecessary hided) as: ID, REF_AIRPORT_ID, NAME, LOCATION and once I insert a new record in first datagrid I should get something like:

1,1,AMSTERDAM SCHIPHOL (AMS), New York

indeed I get:

1,1,,New York

as like when datagrid reloads, doesn't pick the name of the airport related the REF_AIRPORT_ID.

As screenshots I should get something like this:

indeed I get:

if it's more clear now, and anyone has a suggest, it would be appreciated

Thanks a lot

Ciao, Luigi

OlegK on Reloading grid when having multiple tables related between themselves

Mon, 22 Nov 2010 14:32:33 +0200

Hello,

Your code is relatively long and bad formatted. You write that you have two grids. I suppose one table has id="datatableoffers" and another one id="datatableprices", but you don't include any definition of the second table.

The code which you posted use addfunc instead of just using of

editurl:'http://www.vacanze-viaggi.eu/w.....1'

The verification of the data added (which do checkExistsData.php) can do your edit_dataoffers.php code and return an error HTTP code in case of error. As the data the error message can be returned. You can use additionally errorTextFormat event handler to decode/format the message returned by the server.

It is important what return your server after the adding a new row. It should return the id of the new row. If you will use as the add parameters

closeAfterAdd:true, reloadAfterSubmit:false

then the main grid will be not reloaded, the new row will be added to the main grid and the new row will be selected.

If you need to clear the "details" grid (the second grid) you can just use another datatype. Changing the datatype to 'local'

jQuery("#datatableprices").setGridParam({ datatype: 'local' }).trigger('reloadGrid');

will clear the table. By the way initially I would also use datatype: 'local' in the definition of the jQuery("#datatableprices") because it is no row selected in the "main" grid at the beginning.

In the onSelectRow function you can set datatype: 'json' additionally to the setting url and page:1.

Best regards
Oleg

Luigino on Reloading grid when having multiple tables related between themselves

Mon, 22 Nov 2010 12:01:23 +0200

Hello...

no one has maybe a possible suggest to check for this little issue explained in previous posts?...

By the way, in this case I'm using XAMPP on windows platform.

Thanks a lot in advance.

Ciao,

Luigi

Luigino on Reloading grid when having multiple tables related between themselves

Tue, 16 Nov 2010 13:08:24 +0200

Hello again everyone,

I lately I tried even to avoid addfunc and editfunc functions, and to use editurl to see what would be the behavior if different and it's the same: I see the row just inserted partially, in the specific in auto reloading grid after submit it doesn't fill those fields which are related to other tables with their relative description. On the other hand, in both cases with addfunc/editfunc and without, if I select that row just inserted, grid would select another row! So I have to reload the complete page with CTRL+F5 to see all correctly.

Any directions would be really appreciated.... even to understand if it's a bug of 3.8 version (ah, I'm actually using XAMPP on Windows)

Thanks in advance to everyone!

Ciao,

Luigi

Luigino on Reloading grid when having multiple tables related between themselves

Mon, 15 Nov 2010 10:29:00 +0200

Hello everyone,

I am using jqGrid 3.8 and when I add new records in a table which is related to other tables like:

TABLE A : ID, Field1, Field_Ref_ID_TABLE_B, Description

TABLE B: ID, Description

The issue I get is once I insert a new record and the grid reloads: I see new record but partially because it doesn't show data from other tables like it doesn't really empty the grid and re-call the php of the url related to loading data. I tried also to put inside addfunc the event afterComplete: function(…) { .trigger('reloadGrid');} but without success….

Here it's the code…. any directions would be really appreciated…might be a little version bug because at least reloading, even partially, works…

jQuery(document).ready(function(){
//result = false;
var ids=0;
jQuery("#datatableoffers").jqGrid({
   url:"http://www.vacanze-viaggi.eu/w.....#038;Prime;,
   datatype: 'json',
   colNames:['ID','Codice','Aeroporto','Aeroporto','Paese','Paese','Localita','Categoria','Categoria','Struttura','Struttura','Titolo','Descrizione ridotta','Descrizione estesa','Descrizione quote','Appunti di viaggio','Note interne'],
   colModel:[
           {name:'offer_id',index:'offer_id',width:1,editable:false,hidden:true},
           {name:'offer_code',index:'offer_code',width:60,editable:true,editoptions:{readonly:false,size:12}},
           {name:'offer_ref_vvd_airports',index:'offer_ref_vvd_airports',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:airports},editrules:{edithidden:true,required:true,number:true}},
           {name:'airport_name',index:'airport_name'},
           {name:'offer_ref_vvd_states',index:'offer_ref_vvd_states',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:states},editrules:{edithidden:true,required:true,number:true}},
           {name:'state_name',index:'state_name'},
           {name:'offer_place',index:'offer_place',width:200,editable:true,editoptions:{readonly:false,size:100}},
           {name:'offer_ref_vvd_categories',index:'offer_ref_vvd_categories',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:categories},editrules:{edithidden:true,required:true,number:true}},
           {name:'category_description',index:'category_description'},
           {name:'offer_ref_vvd_furnitures',index:'offer_ref_vvd_furnitures',width:1,hidden:true,editable:true,edittype:'select',editoptions:{width:100,value:furnitures},editrules:{edithidden:true,required:true,number:true}},
           {name:'furniture',index:'furniture'},
           {name:'offer_title',index:'offer_title',width:300,editable:true,editoptions:{readonly:false,size:300}},
           {name:'offer_tiny_descr',index:'offer_tiny_descr',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
           {name:'offer_long_descr',index:'offer_long_descr',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
           {name:'offer_quotes_descr',index:'offer_quotes_descr',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
           {name:'offer_travel_notes',index:'offer_travel_notes',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}},
           {name:'offer_internal_notes',index:'offer_internal_notes',width:500,editable:true,edittype:'textarea',editoptions:{readonly:false,rows:3,cols:80}}
   ],
   rowNum:10,
   rowList:[10,20,30],
   loadonce:true,
   pager: '#pageroffers',
   emptyrecords: 'Nessun pacchetto disponibile',
   sortname: 'furniture_name',
   viewrecords: true,
   sortorder: "desc",
   caption:"Pacchetti vacanze",
   editurl:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/edit_dataoffers.php?table_name=vvi_offers",
   shrinkToFit: false,
   autowidth: true,
   onSelectRow: function(ids) {
       if(ids==null) {
           ids=0;
           if(jQuery("#datatableprices").jqGrid('getGridParam','records')>0) {
               jQuery("#datatableprices").jqGrid('setGridParam',{url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/load_subdata.php?q=1&idm="+ids,page:1})
               .trigger('reloadGrid');
           }
       } else {
           jQuery("#datatableprices").jqGrid('setGridParam',{url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/load_subdata.php?q=1&idm="+ids,page:1})
           .trigger('reloadGrid');
           $("#pagergallery").photomanager("option","refid",ids);
       }
   }
})
var grid = jQuery("#datatableoffers").navGrid('#pageroffers', // afterComplete:function(response, postdata, formid){jQuery("#datatableoffers").trigger('reloadGrid');}
{addfunc:function(){var result=$.ajax({url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/checkExistsData.php",async:false}).responseText;if (result) grid.editGridRow("new",{height:750,width:800,top:30,left:100,url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/edit_dataoffers.php?q=1″,reloadAfterSubmit:true,closeOnEscape:true});},editfunc:function(){var id=jQuery("#datatableoffers").getGridParam('selrow');jQuery("#datatableoffers").editGridRow(id,{height:750,width:800,top:30,left:100,url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/edit_dataoffers.php?q=1&id="+id,reloadAfterSubmit:true,closeOnEscape:true});},delfunc:function(){var id=jQuery("#datatableoffers").getGridParam('selrow');jQuery("#datatableoffers").delGridRow(id,{url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/edit_dataoffers.php?q=1&id="+id,afterComplete:function(response, postdata){jQuery("#datatableprices").trigger('reloadGrid');$("#pagergallery").photomanager("option","refid",ids);},reloadAfterSubmit:true,closeOnEscape:true})},refresh:true,search:true}, //options
{height:495,width:670,top:100,left:50,reloadAfterSubmit:true}, // edit options
{height:495,width:670,top:100,left:50,reloadAfterSubmit:true}, // add options
{reloadAfterSubmit:true}, // del options
{}, // search options
{alerttext:'Select first an offer…'}
);
});

Or at least is there a way to force re-calling this:

url:"http://www.vacanze-viaggi.eu/wp-content/plugins/viaggivacanze/php/load_dataoffers.php?table_name=vvi_offers&q=1″

when I am reloading the grid, so it would empty totally the grid and re-loading all the data, included that new record just inserted?

Thanks in advance to everyone!!!

Ciao

Luigi