load grid data from mysql table with a where clause

[claudegelParticipant]

I<ve setuped a grid where I can add row, update row and delete row. the data is stored in a mysql table with form number. The table will store all data from many form.
I want to be able to load the data (row) for only one form number. something like
select * from data_table where form_num = 1234

to select the form number I’ve added a dropdownlist like this:

var source_actif =
        {
            datatype: "json",
            type:	"POST",
            datafields: [
                { name: 'form_no' },
                { name: 'date_actif' }
            ],
            url: url,
            async: false,
            data: {
        		select: true,
        		dbase: "formadmin",
        		numrow: "*",
        		filtre: "ficheno=169 AND complet=0 AND conforme=-1",
        		ordre: "form_no asc",
            	field: "form_no,concat(form_no,'-',date_actif) as liste"
        	}
        };
        var dataAdapter4 = new $.jqx.dataAdapter(source_actif);

        // Creer une jqxDropDownList des clients
        $("#open_list").jqxDropDownList({
            source: dataAdapter4,
            displayMember: "form_no",
            valueMember: "form_no",
            width: 260,
            height: 25,
            placeHolder: "Choisir le formulaire a terminer"
        });

        // recharger la grille avec les valeurs du formulaire
        $("#open_list").on('select', function (event)
        		{
    				//rouver le numero du formulaire
					var formno = event.args.item.value;
					// desactiver les autre liste
					$("#jqxWidget").jqxDropDownList({ disabled: true });
					$("#open_list").jqxDropDownList({ disabled: true });
					//var commit = $("#jqxgrid").jqxGrid('load', formno); <-- need something like this
        		}); 

my data grid is like this:

 var source2 =
       				 {
          				  datatype: "json",
           				  type:	"POST",
            			  datafields: [
               				 { name: 'nocli' },
               				 { name: 'date' },
               				 { name: 'row' },
              				 { name: 'no_facture' },
               				 { name: 'code_prod' },
                			 { name: 'qtt' },
               				 { name: 'descrip' },
               				 { name: 'no_lot' },
               				 { name: 'no_retour'}
            			  ],
            			  url: url,
           				  async: false,
           				  //ajouter un rang vide
           				  addrow: function (rowid, rowdata, position, commit) {
							// ajouter une ligne a la base de donnees
    						var data = "insert=true&dbase=" + table_form + "&nocli=" + nocli + "&date=" + ladate + "&formno=" + form_no + "&row=" + rowid + "&cause=0&" + $.param(rowdata);
								$.ajax({
	                                  dataType: 'json',
	                                  url: '../moteur/index.php',
	                                  type:	"POST",
	                                  data: data,
             				          cache: false,
	                                  success: function (data, status, xhr) {
	                                      // insert command is executed.
	                                	  $("#Termine").jqxButton({disabled: false});
	                                	  $("#Suivant").jqxButton({disabled: false});
	                                	  $("#Pause").jqxButton({disabled: false});
	                                	  $("#Annule").jqxButton({disabled: false});
	                                	  $('#jqxdate').jqxDateTimeInput({disabled: true});
	                                      commit(true);
	                                  },
	                                  error: function (jqXHR, textStatus, errorThrown) {
	                                      // cancel changes.
	                                      commit(false);
	                                  }
	                              });
           				  },
           				  updaterow: function (rowid, rowdata, commit) {
              				// changer les donnees d'une ligne dans la base de donnees
             				  var selectedrow = $('#jqxgrid').jqxGrid('selectedrowindex');
                              var filtre = "formno=" + form_no + " AND row=" + $('#jqxgrid').jqxGrid('getrowid',selectedrow);
							  var data = "update=true&dbase=" + table_form + "&filtre=" + filtre + "&" + $.param(rowdata);
							  							  
                              $.ajax({
                                  dataType: 'json',
                                  url: '../moteur/index.php',
                                  type:	"POST",
                                  data: data,
                                  cache: false,
                                  success: function (data, status, xhr) {
                                      // insert command is executed.
                                      commit(true);
                                  },
                                  error: function (jqXHR, textStatus, errorThrown) {
                                      // cancel changes.
                                      commit(false);
                                  }
                              });
                  		  },
          				  deleterow: function (rowid, commit) {
              				//effacer une ligne de la base de donnees
            				var filtre = "formno=" + form_no + " AND row=" + rowid;
              				var data = "delete=true&dbase=" + table_form + "&filtre=" + filtre;
              				
          					$.ajax({
                                dataType: 'json',
                                url: '../moteur/index.php',
                                type:	"POST",
                                data: data,
                                success: function (data, status, xhr) {
                                    // insert command is executed.
                                    commit(true);
                                },
                                error: function (jqXHR, textStatus, errorThrown) {
                                    // cancel changes.
                                    commit(true);
                                }
                            });
          				  }
        			};
					var dataAdapter2 = new $.jqx.dataAdapter(source2);
	
        			// initializer les champs du questionnaire.
                  $("#nofacture").jqxInput({ theme: 'classic', width: 150, height: 23 });
                  $("#nolot").jqxInput({ theme: 'classic', width: 150, height: 23 });
                  $("#codeprod").jqxInput({ theme: 'classic', width: 150, height: 23 });
                  $("#quantite").jqxInput({ theme: 'classic', width: 150, height: 23 });
                  $("#cause").jqxInput({ theme: 'classic', width: 250, height: 23 });

         			
         			var editrow = 0;
         			
   					// initialiser la grille des items retournes
   					$("#jqxgrid").jqxGrid({
   					width: 800,
   					columns: [
   	                  { text: 'No Facture', dataField: 'no_facture', width: 80 },
   	                  { text: 'No Lot', dataField: 'no_lot', width: 60 },
   	                  { text: 'Code Produit', dataField: 'code_prod', width: 90 },
   	                  { text: 'Quantite', dataField: 'qtt', width: 70 },
   	                  { text: 'Cause', dataField: 'descrip', width: 450 },
      	              { text: 'Editer', datafield: 'Edit', width: 50, columntype: 'button', cellsrenderer: function () {
                           return "Editer";
                           },
                           buttonclick: function (row) {
                           // open the popup window when the user clicks a button.
                           editrow = row;
                           var offset = $("#jqxgrid").offset();
                           $("#lepopup").jqxWindow({ position: { x: parseInt(offset.left) + 80, y: parseInt(offset.top) + 80 } });
                           // get the clicked row's data and initialize the input fields.
                           var dataRecord = $("#jqxgrid").jqxGrid('getrowdata', editrow);
                           $("#nofacture").val(dataRecord.no_facture);
                           $("#nolot").val(dataRecord.no_lot);
                           $("#codeprod").val(dataRecord.code_prod);
                           //$("#quantite").jqxNumberInput({ decimal: dataRecord.qtt});
                           $("#quantite").val(dataRecord.qtt);
                           $("#cause").val(dataRecord.descrip);
                           // show the popup window.
                           $("#lepopup").jqxWindow('open');
                       	   }
                       }
   	              	 ],
   					source: dataAdapter2
   	   				});

can I add a ‘load’ method so I can have an ajax query sent to my php engine. If so how

Thanks,

[DimitarParticipant]

Hello claudegel,

I think the best approach would be to apply a filter to the grid on the open_list select event and implement server-side filtering.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[claudegelParticipant]

I finally get it to work by using dataAdapter.dataBind().
The grid data is initialized via:

Var source2 ={
...
data: {
       select: true,
       dbase: table_form,
       numrow: "*",
       filtre: "formno=" + form_no,
       ordre: "row asc",
       field: "*"
   },
...
}
var dataAdapter2 = new $.jqx.dataAdapter(source2);

and in my 2 dropdownlist I use

 $("#jqxWidget").on('select', function (event)
        		{
        		// get client's ID.
        		  var nocli = event.args.item.value;
			
        		  $("#jqxWidget").jqxDropDownList({ disabled: true });
        		  $("#open_list").jqxDropDownList({ disabled: true });
        		  dataAdapter2.dataBind();
        		});

to initialise an empty datagrid

and

$("#open_list").on('select', function (event)
        		{
    				//rouver le numero du formulaire
					form_no = event.args.item.value;
					// desactiver les autre liste
					$("#jqxWidget").jqxDropDownList({ disabled: true });
					$("#open_list").jqxDropDownList({ disabled: true });
					$('#jqxdate').jqxDateTimeInput({disabled: true});
					// changer le numero de formulaire
					$('#formulaire').html("Formulaire no: " + form_no);
					//mettre a jour la grille
					source2.data.filtre = "formno=" + form_no; //change the formno value before reloading data
					dataAdapter2.dataBind();
        		});

to load a specific datagird for update

now my problem is that my two add and delete button that were working perfectly to add empty row in my grid and to delete selected row are not working anymore. Is it because the dataAdapter2.dataBind(); is issued inside a function so it is not visible outside that function ???

[DimitarParticipant]

Hi claudegel,

dataAdapter2 should be visible in the event handler function, as it is globally defined (at least it seems so from your code). We suggest you implement server-side filtering as shown in the help topic Server Side Filtering with jqxGrid using PHP and MySQL to avoid unexpected behaviour. Client-side, apply filters through the filter menu/filter row or programmatically with the method addfilter.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[Author]

Posts