jQuery UI Widgets Forums Grid load grid data from mysql table with a where clause

This topic contains 3 replies, has 2 voices, and was last updated by  Dimitar 9 years, 2 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author

  • claudegel
    Participant

    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,


    Dimitar
    Participant

    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/


    claudegel
    Participant

    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 ???


    Dimitar
    Participant

    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/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.