In-Place Editing in DataTable with X-Editable using PHP Ajax

hello friends in this video tutorial we will learn how to make live editable data table by using x editable plugin with php script and ajax we all know jquery data table plugin is widely used curry plugin for load dynamic data in tabular format with feature like searching pagination feature so now we want to convert curry data table cells into editable cell for update particular cell data so for this here we have use x editable jury plugin by using this x editable plugin we can create editable elements on our page so here we will convert data table cell into editable elements [Music] now let's start discussing this topic so this is our testing database and in this we have one sample table with four table column like id first name last name and gender in this table we have already store some data so we will use this data to make inline editable table by using jquery x editable plugin and jury data table plugin with php script and ajax this is our working folder first we have open index dot php file and in this file we have already included jakari library link bootstrap library link jury data table library link and jury x editable plugin library link in this file first we have create one table with id is equal to sample data we will use this id attribute value for initialized curry data table plugin and jquery x editable plugin under this table we have create one table head tag and between this we have create one table row tag and under this tag we have create four table data tag like id first name last name and gender first we want to initialize jquery data table plugin for this we have go to jquery code part and here we have write data table variable is equal to dollar with table id sample data with data table method this method will initialize jquery data table plugin under this method first we have write first option processing which has been set to true this option will enable the display of a processing indicator when the table is being processed in second option we have right server side which has been set to true this option will switch client side processing to server side processing in third option we have right order option which has been set to blank array this option will enable table column ordering lastly we have write ajax option by using this option we can send ajax request to server and under this first we have write first option url set to fetch.php here we have send request to this page in second option we have right type which has been set to post method so here we have use post method for send data to server at the time of initializing jquery data table plugin with data we want to add some attribute in each row table cells so for this we have write created row callback function with three arguments like row data and row index [Music] under this function we have write dollar dot each method with argument like table data and row variable with callback function with call index argument under this function we have write if statement and under condition we have write call index variable is equal to one this condition will check if this variable value will be 1 then it will execute if block of code and here in first number column we will stored first name table column data so under this column we want to add data name attribute so for this we have write dollar this with attribute method with two argument like data name attribute with value like first name same way we want to add class attribute under this first column so here we have right dollar this with attribute method with two argument in first argument we have write class and in second argument we have write first name after this we want to add data type attribute also so for this here we have write dollar this with attribute method with two argument in first argument we have write data type and in second argument we have write text so here x editable plugin will use text data type for this first name column data and lastly we have to add data primary key attribute so for this we have write dollar this with attribute method with two argument in first argument we have write data primary key and in second argument we have write data with zero index so here in data zero index we have store primary key data which will be used for update of data by using x editable plugin after this we have write if statement and under condition we have write call index variable is equal to 2 so when call index variable value will be 2 then it will execute if block of code and under this third table column we will load last name table column data under this block also we have to add different attribute in last name table column so we have simply copy this code and paste here and here in data name attribute we have right last name in place of first name and same way in class attribute also we have right last name in place of first name for this last name table column also we have used text data type and in data primary key we have no do any changes after this again we have write if statement and under condition we have write call index variable value is equal to 3 if this condition true then it will execute if block of code and under this table column we will load gender table column data [Music] under this block also we have to add different attribute in gender table column so here we have simply copy this code and paste here and here in data name attribute we have write gender in place of first name and same way in class attribute also we have write gender in place of first name for this gender table column we have used select data type by using this data type this x editable plugin will make select box for select gender details and in data primary key we have no do any changes so by using this code it will add attribute in table data tag at the time of initializing jquery data table plugin now we have moved to write php code so first we want to make database connection so for this we have open database connection dot php file and here we have write dollar connect variable is equal to new do class with three argument in first argument we have write mysql host is equal to localhost database name set to testing user name set to root and password set to blank this code will make database connection next we have go to fetch.php file and here we have write include statement with database connection.php file this code will make database connection [Music] after this we have write dollar columns variable is equal to array with table column like id first name last name and gender we will use this array for sorting table column now we have write dollar query variable and in this variable we will store query for fetch data from sample table so here we have right select star from sample table after this we have write if statement and under condition we have write is set function with dollar post search value variable here this post search value variable will be passed by data table plugin so when we have write something in search box and then it will execute this if block so under this block we have again write dollar query variable and in this variable we have append query like where first name like percentage dollar post search value variable with percentage and this same query we have write for all table column this query will search data in this table column after defining search condition now we have again write if statement and in condition we have write is set function with dollar post order variable this condition will check if this variable value has been set then it will execute if block of code otherwise it will execute else block suppose dollar post order variable value is set then it will execute if block of code and under this we have write dollar query variable and in this variable we have append query like order by dollar columns variable with dollar post order zero column variable and dollar post order zero direction variable here first variable will return column number for sort and based on that number we have get table column name from dollar column variable and second variable will return sorting direction like ascending or descending but suppose dollar post order variable value is not set then it will execute else block of code and under this block we have write dollar query variable and in this variable we have opened query like order by id table column in descending order now we have defined dollar query one variable is equal to blank value in this variable we will store query with fetch limit data then after we have write if statement and under condition we have write dollar post length variable value is not equal to minus 1 this condition will check if dollar post length variable value is not equal to minus 1 then it will execute if block of code so under this block we have write dollar query one variable is equal to limit dollar post start variable comma dollar post length variable here both variable value has been posed by data tables plugin wire ajax request [Music] here we have store query in two different variable from dollar query variable we will get number of filter data and from combination of both query we will make complete query for fetch data from sample table first we want to execute only dollar query variable query so forget number of filter records for this here we have write dollar statement variable is equal to dollar connect variable with prepare statement and under this we have write dollar query variable it will make query for execution then after we want to execute query so here we have write dollar statement variable with execute method it will execute dollar query variable query now we want to get number of filter data for this here we have write dollar number filter row variable is equal to dollar statement variable with row count method it will return number of row affected after query execution [Music] now we want to execute complete query so here we have write dollar result variable is equal to dollar connect variable with query method and under this we have write dollar query variable dot dollar query one variable here we have merged both query and this query method will execute combine query and return query execution result which has been stored under this dollar result variable now we have write dollar data variable is equal to blank array in this variable we will store dollar result data one by one below this we have right for each loop and under condition we have write dollar result variable as dollar row variable by using this loop we can fetch data from dollar result variable under this for each loop first we have create one dollar sub array variable is equal to blank array in this variable we will store data which will be get from dollar row variable first we want to store id table column data so here we have write dollar sub array variable is equal to dollar row id table column name after this we have again write dollar sub array variable is equal to dollar row first name table column name here we have store first name table column data in dollar sub array variable same way we have again write dollar sub array variable is equal to dollar row last name variable so here we have store last name table column data in dollar sub array variable [Music] after this we have write dollar sub array variable is equal to dollar row gender variable here we have store gender table column data in dollar sub array variable so here we have store data in dollar sub array variable now we have write dollar data variable is equal to dollar sub array variable so here we have store all sub array data in dollar data variable now we want to get total number rows from sample table data so here we have make count all data function with dollar connect variable as argument this function will return total number of rows from sample table under this function we have write dollar query variable is equal to select star from sample table below this we have write dollar statement variable is equal to dollar connect variable with prepare statement with dollar query variable this statement will make query for execution for execute query we have write dollar statement variable with execute method this method will execute query lastly we have write return statement with dollar statement variable with row count method this method will return number of rows from sample table now we have write dollar output variable is equal to array and in this array we have write first key draw with value dollar post draw variable in second key we have write records total and in value we have write count all data function with dollar connect variable [Music] in third key we have write records filtered and in this key value we have write dollar number filter row variable in which we have stored number of filter rows and in last key we have write data and in value we have write dollar data variable in which we have store sample table data in array format so this way we have store all required data for make data table in dollar output variable now we want to send this data to ajax request in json format so we have write echo statement with json encode function and in this function we have write dollar output variable this function will convert data into json string so here our code is ready for load data in jquery data table plugin with server side processing so now we have check output in browser friends here we can see when page has been load in browser then we can see sample table data has been load in jury data table plugin [Music] and here we can do searching of my sql sample table data here we can see pagination is automatic made without write any line of code and here we can also see table column sorting of data this is all feature of jquery data table plugin here also we can see different attribute has been added in table cell this attribute we will use for convert this data table into edit table by using x editable plugin for this we have go to index dot php file and here we have write curry code for inline table column edit first name column so we have write dollar with table id sample data as selector with editable method this method will initialize editable plugin [Music] under this first we have write container property which has been set to body this is for pop window smoothly on web page [Music] in second property we have write selector and in this option we can define html selector so here we want to activate on first name table column so we have write table data tag dot first name class which is value of class attribute in third property we have write url and here we have writeupdate.php here it will send request to this page in fourth property we have write title and here we have right first name this will display as title under pop heading in fifth property we have right type which has been set to post here we have use post method for send data to server by using post method this will send name value and primary key value as data sent to server in last option we want to use required field validation for this first name table column data so when someone enter blank value then it will show error message like this field is required so here we have write validate property with callback function with as value argument under this function we have write if dollar trim function with value is equal blank [Music] this condition will check if someone enter blank records then it will execute if block of code and under this block we have write return statement with message like this field is required [Music] now want to initialize x editable plugin on last name table column so here we have copy this whole code and paste here from this code we have go to selector property and here we have right last name in place of first name [Music] and then after we have go to title option and here we have right last name in place of first name so now it will initialize x editable plugin on last name table column also same way we want to initialize x editable plugin for gender table column so here first we have copy above code and paste here and here first we have change selector property and here we have write table data tag with gender class in this table column we want to display male female select box so here we have write source property and here we have defined male female data in json array format this property will make select box with male and female option this will code will initialize editable plugin on gender table column with male female select box so here are code ready for initialize x editable plugin on table column like first name last name and gender so now we have go to update dot php page and on this page we have first make database connection after making database connection we have write dollar query variable is equal to update sample table set table column get from dollar post name variable is equal to value get from dollar post value variable where id is equal to dollar post primary key variable [Music] this is update query and here we have get value of post name value and primary key variable value which has been get from ajax request in editable plugin now we want to execute this query so we have write dollar connect variable with query method and under this we have write dollar query variable this method will execute above update query and it will update data in my sql table so here our code is ready now we have check output in browser friends here we can see that when page has been load in browser then sample table data has been load in jquery data table plugin and here we have also initialize x editable plugin on first name last name and gender table column so when we have click on first name table column then pop over pop-up on web page and here we can edit this table column value same way we have click on other table column like gender then here in pop-up dialog box we can select male female gender from select box and we can change gender table column data now first we want to check validation so we have click on first name table column and remove this text box value and click on ok button then here we can see error message like this field value is required so here our validation work perfectly after this we want to update last name table column data so we have click on last name table column and here we have change table value and click on ok button so after click on ok button here we can see this table cell background color has been changed now we have refresh page and after refresh of web page here we can see changed data under this data table that means inline update of data table column has been working properly now we want to update gender table column data so we have click on gender table column data then you can see select box with male and female option so we have changed this column gender from male to female and click on ok button then here we can see that this column data has been updated so this code also working so x editable plugin is very useful and by using this plugin you can edit inline table column data without refreshing of page and here we have convert jquery data table cell data into inline editable by using this x editable plugin with server side processing using php with ajax lastly if you have any query or inputs regarding this tutorial you can ask your query in comment box if you have liked this video please share with your friends or even you can also share on social media also and lastly don't forget to subscribe our channel for future video tutorials

Channel: Webslesson

Views: 10,441

Keywords: inline, editing, jquery, datatable, x-editable, php, ajax, editable datatable, editable datatable php mysql, editable datatable with ajax, editable data table using jquery, x-editable datatables, x-editable datatables ajax, x-editable database example, inline editable, inline edit datatable, inline edit php, inline editable datatable, php ajax inline edit mysql, php ajax inline editing datatables, php ajax inline editable datatables, editable datatable in php mysql

Id: lkDO2xqbifE

Length: 28min 7sec (1687 seconds)

Published: Fri Oct 02 2020