Inline Datatable Editing using jQuery Tabledit with PHP Ajax

hello friends in this video tutorial we are going to learn how to integrate chicory table edit plug-in with chicory data table plug-in in PHP using Ajax in previous tutorial we have published tutorial on how to integrate chicory table edit plug-in with laravel framework then some viewers has requested us to publish video tutorial on how can we use chicory table edit plug-in width chicory data table plugin so for this here we have published this video tutorial in which we will describe you to how to use chicory table edit plug-in with chicory data table plug-in and make live in line editable data table and do the inline edit and delete my SQL data operation using PHP script 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 in this table so we will use this data to make in line editable table by using Jerry table edit plug-in and jakhary data table plug-in 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 Shikari library link bootstrap library link chicory data table library link and curie table edit plug-in library link you 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 curie data table plug-in and Curie table edit plugin on this table 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 jakhary data table plugin for this we have go to Jakara 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 jakhary data table plugin under this method first we have right first option processing 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 right Ajax option by using this option we can send Ajax requests to server and under this first we have right first option URL set to fetch dot PHP here we have send request to this page in second option we have right type which has been set to post method here we have used post method for send data to server now we have moved to write PHP code so first we want to make database connection 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 my SQL host is equal to localhost database name set to testing user name set to root and password set to blank it will make database connection next we have go to fetch dot PHP file and here we have write include statement with database connection dot PHP file it will make database connection under this file after this we have right 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 right $1 query variable 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 right if statement and under condition we have right is set function with dollar post search value variable here this post search value variable has been passed by the table plugin when we have write something in search box and then it will execute if block so under this block we have again right dollar query variable and in this variable we have append query like where first name like percentage dollar post search value variable with percentage same query we have right for all table column this query search data in this table column you after defining search condition now we have again right if statement and in condition we have right 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 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 right 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 jolla posts order 0 Direction variable here first variable will return column number for sought 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 and under this block we have right dollar query variable and in this variable we have append 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 right if statement and under condition we have right dollar post length variable value is not equal to minus one this condition will check if dollar post length variable value is not equal to minus one then it will execute if block of code so under this block we have right dollar query one variable is equal to limit dollar post start variable comma dollar post length variable here both variable value has been post by data tables plug-in via Ajax request 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 forget number of filter records for this here we have write dollars statement variable is equal to dollar connect variable with preparer 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 right 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 now we want to execute complete query so here we have write dollar statement variable is equal to dollar connect variable with preparer statement and under this we have write dollar query variable dot dollar query one variable here we have merged both query next we want to execute complete query so here we have write dollar statement variable with execute method it will execute complete merge query now we want to get query execution result so here we have write dollar result variable is equal to dollar statement were able with fetch old method it will return query execution result now we have right 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 right 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 $1 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 sub array variable is equal to dollar row ID table column name after this we have again right 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 right dollar sub-array variable is equal to dollar row last name variable so here we have stole ass name table column data in dollar sub array variable after this we have right dollar sub-array variable is equal to dollar Rho general variable here we have stole gender table column data in dollar sub-array variable so here we have store data in dollar sub-array variable now we have right 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 rose from sample table data so here we have make function 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 right dollar query variable is equal to select star from sample table below this we have right 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 right dollar statement variable with execute method this method will execute query lastly we have rights return statement with dollar statement variable with row count method this method will return number of rows from sample table now we have right dollar output variable is equal to array and in this array we have right first key drop with value dollar post draw variable in second key we have right records total and in value we have right count all data function with dollar connect variable in third key we have rights records filtered and in this key value we have right dollar number filter row variable in which we have store number of filter rows and in last key we have right data and in value we have right 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 Shikari data table plug-in with server-side processing so now we have checked 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 curry data table plug-in 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 seek table column sorting of data this is all feature of Jakari data table plugin now we want to convert this jakhary data table plugin with inline table edit of data and deleting of data by using jakhary table edit plugin so now we have seen how to integrate curry table edit plug-in with curry data table plug-in with PHP script using Ajax for this we have go to index dot PHP file and here we have write dollar with table ID sample data with on method and under this we have write draw with callback function so when curry data table plug-in fulfilled data in HTML table then it will execute this block of code under this we have write dollar with table ID sample data with table edit method by using this method it will initialize table edit plug-in and convert data table into inline editable data table with edit and delete button under this method in first option we have right which has been set to action dot PHP it will send request to action dot PHP file in second option we have right data type and here we have right Sun so it will receive data in JSON format in third option we have right columns and in this option we can define table column details under this option we have right identifier option and here we have right zero index with table column name ID so this plug-in will use this ID value for edit or delete my SQL data operation after this we have right editable option and in this option we can define which column we want to convert into editable data table column so here in array format we have right index one with first-name table column index two with table column name last name so it will convert this to column into editable text box same way we want to convert third index table column into select box for select gender detail so here we have right three index with table column name gender after this we want to define select option so here in first index we have write male and in second index we have write female so it will convert gender table column into select box so this way we can define which column we want to convert into editable by using this table edit plug-in then after we have write restore button option which has been set to false here we do not want to required restore data button so we have set to false table edit method we have right on success callback function with data argument this function has been called if request completed successfully and it will receive data from server under this function we have right if statement and under condition we have right data adult action is equal to delete this condition will check if data dot action value is equal to delete then it will execute if block of code this plug-in will received data in JSON format under this block of code we have right dollar and in selector we have right hashtag with data dot ID with remove method this will remove whole data table row when data dot action value is equal to delete here action is a hidden field value which is created by using plug-in with the creation of edit save delete and restore button so when we have click on delete button then at that time hidden action field value will be delete and same way when we have click on Save button then action hidden field value will be save after this we want to reload curry data table data so here we have right dollar with table ID sample data with data table method dot Ajax dot reload method it will refresh curry data table data without refresh of webpage now we have go to action dot PHP file and here we have write include statement with database connection dot PHP file it will make database connection under this file below this we have right if statement and under condition we have right dollar post action variable value is equal to edit if this condition true then it will execute if block of code under this block we have right dollar data variable is equal to array in first key we have right double : first name and in value we have right dollar post first name variable value in second key we have right double : last name and in value we have right dollar post last name variable value in third key we have right double : gender and in value we have right dollar post gender variable value and in last key we have right double : ID and in value we have right dollar post ID variable value so this way we have store Ajax request data in this dollar data variable in array format next we have right dollar query variable is equal to update sample table set first name is equal to double colon first name last name is equal to double colon last name gender table column is equal to double colon gender where ID is equal to double colon so this is update data query below this we have right dollar statement variable is equal to dollar connect variable with preparer statement and under this we have right dollar query variable it will make query for execution after this we want to execute above update query so here we have write dollar statement variable with execute method and under this we have write dollar data variable it will execute above update query and it will update my SQL table data now we want to send response to Ajax request so here we have write echo statement with Sun encode method and under this we have write dollar post variable it will convert data into JSON format and sent to Ajax request after this for delete data here we have write if statement and under condition we have write dollar post action variable value is equal to delete if this condition is true then it will execute if block of code under this block we have right dollar query variable is equal to delete from sample table where ID is equal to dollar post ID variable this is delete my SQL data query below this query we have right dollar statement variable is equal to dollar Connect variable with preparer statement and under this we have right dollar query variable after this for execute above delete query we have right dollar statement variable with execute method it will execute above delete query and it will remove data from my SQL table lastly we want to send data to Ajax request so here we have write echo statement with an encode method and under this we have write dollar post variable so it will convert data into JSON format and sent to Ajax request so here our code is ready now we have checked output in browser friends here we can see sample table data has been loaded in jakhary data table plug-in and here we cannot see edit or delete button so first we have refreshed web page after a refresh of webpage here we can see one column has been a tinge curry data table and in that column we can see edit or delete button this button has been created by using chicory table edit plugin first we want to check in line curry data table edit so we have click on this row of data then this whole row has been converted into editable row here to table column has been converted in textbox and one table column has been converted into select box and here Save button has been appear in this data table row now we have changed this role gender table column data and then after we have click on Save button after click on Save button table row background color has been changed that means data has been updated in my SQL database same way we want to check live data table delete data operation so for this we have click on this row delete button after click on delete button here we can see confirmed button has been appear in this table row so we have click on confirmed button after click on confirm button that row has been removed from data table also that means that row of data has been also removed from my SQL database also and we can see remaining data on web page in scurry data table without refresh of web page so friends in this video tutorial we have first load data into Jakari data table plug-in with feature like searching pagination table sorting and more after this injury data table we want to convert into live editable data table for this here we have integrated curie table edit plug-in with injury data table and convert data table into inline editing and deleting of data so here we have make inline edit or delete data table by using chicory data table with chicory table edit plug-in using PHP script and Ajax if you have any query or input regarding this video tutorial please comment your query or input in comment box or if you like this video tutorial please share with your friends or even you can also share on social media also

Channel: Webslesson

Views: 29,745

Keywords: php, ajax, jquery, plugin, datatable, tabledit, mysql, edit, update, delete, remove, data, live, inline, editable, server-side processing, live datatables, datatable with tabledit, editable datatable, editable datatable in jquery, editable datatable php mysql, editing inline cells, php live data update, tabledit php, datatables live crud using php, inline editing in php with ajax, bootstrap datatables, datatables tutorial, live datatable edit with ajax and php, crud using datatables, php grid

Id: nd9nOQemVAc

Length: 32min 27sec (1947 seconds)

Published: Sun May 17 2020