Datatables Live Records Add Edit Delete using PHP Ajax JQuery

hello friends in this video tutorial we are going to learn live data insert update delete and data tables data by using PHP with Ajax query in one of my previous video tutorial we have already discussed live data crud operation by using content editable attribute in normal HTML table but in this video tutorial we have used query data tables plug-in for discuss live data add edit remove from data tables by using query Ajax with PHP here also we have used content editable attribute and data tables for insert and update data tables data without refresh of web page there are many viewer has request us to make video tutorial on live data add edit remove data tables plug-in with PHP query Ajax so we have make this tutorial in which we have used data tables plug-in for display data and tabular format by using PHP Ajax this is our testing database in which we have one simple user table with three table tall and like head first name and last name we will use this table for insert update and delete data this is our index page and on this page first URL is for loads query library second URL is for load bootstrap stylesheet library third URL is for loads query data table JavaScript library fourth URL is for data tables bootstrap JavaScript library fifth is for load data tables bootstrap stylesheet 6qr l is for load bootstrap data Pickers stylesheet and last URL for load bootstrap data Bakker JavaScript library this old URL is required for this video tutorial first we want to load user data on web page in table format so we have defined one table with attribute it is equal to user data with three tables heading like first name last name and blank for delete and Save button we will load data under this table by using data table plugin now we have moved to write query code for activate data tables plugin here first we have made fetch data function this function will activate data tables plug-in on user data table and it will load data under this user data table under this function we have write data table variable is equal to table add user data with data table method this method will activate data table plug-in on table with it user data under this method we have write 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 set to true this option will switch client side processing to server side processing in third option we have right order 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 request to server and in this first we have right first option URL set to fetch dot PHP here we have sent requested to this page in seconds option we have right typeset the post method here we have used post method for send data to server this way we have set all option for load data under user data table by using data tables plugin now we have moved to write PHP code on fetch dot PHP page and on this page first we have make database connection so here we have write doll or connect variable is equal to mystically connect function with for argument like host name set the locale lost username set to root password set to blank and database name set to testing after making database connection now we have defined dollar columns variable in this variable we will store table column name and array format so here we have right column name first name and last name in array format now we have right dollar query variable is equal to select star from users table this is our simple select query which we have stored into dollar query variable in this variable we will append query according to data tables plugin below this we have write if statement and in condition we have write is set function with Dollar post search value variable here this post search value variable has been passed by data table plugin when we have write something in search box and then it will execute if block so under this block we have write dollar query variable and in this variable we have a vent query like table column order like percentage dollar post search value variable percentage same query we have write for both table column this query will search data in all table column after defining search condition we have again write a statement and in condition we have write as set function with dollar post-order variable this condition will check if this variable value is set then it will execute if block otherwise it will execute else plot here when we have clicked on table for soar table column then this dollar post-order variable has been passed by data tables query plugin for sort data according to particular column in ascending or descending order suppose dollar post order variable value is set then it will execute if block and under this we have write dollar query variable and in this variable we have a bend query like order by dollar columns variable with dollar post-order 0 column variable and dollar post order 0 direction variable your 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 the return sorting direction like ascending and descending but suppose dollar post order variable value is not set then it will execute else block and under this block we have write dollar query variable and in this variable we have event query like order by order in table column in descending order now we have defined $1 query one variable is equal to blank in this variable we will store a query with fetch limit data then after we have write of 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 lengths variable value is not equal to minus 1 then we'll execute if block of code so under this block we have write dollar query 1 variable is equal to limit dollar post start variable comma dollar post length variable here both variable has been post by data tables plugin by ajax request here we have store query and 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 order table first we want to execute only dollar query variable query forget number of filter record so we have right dollar number of filter row is equal to miss clean on rows function and under this we have write miss clay query function with dollar connect variable and dollar query variable here miss CLE query function execute dollar query variable and miss clean on Rose function return number of those from query execution and store into dollar number filter rows variable now we want to execute complete queries so we have right dollar results variable is equal to miss Lee query function with two arguments like dollar connect variable and dollar query variable concatenate dollar query one variable here we have combined query of both variable and dismissal a query function will execute combined query and store result in dollar result variable now we have right dollar data variable is equal to blank array in this variable we will store dollar result data one by one from dollar result variable we have used while loop with condition like dollar row variable is equal to misty fetch array function with dollar resolved variable this function will convert dollar resolved data into a ray in store into dollar row variable under this while loop first we have create $1 subarray variable is equal to blank array and this variable we will store data get from dollar row variable want to store first name column data so we have right dollar sabor a variable is equal to division tag with attribute content editable which is used to convert HTML tag to editable tag here we have also write a tribute class Z of update data it is equal to dollar row the variable data column is equal to first-name and under text we have write dollar row first name first name data under the stag and this tag will be editable that means we can edit text of this tag by using content editable attribute and here we have used update class in query code for update request and we will get it from this data in attribute and from data column we can get table column name on which we want to update data this salt is required for update data after this we have right dollar sabor a variable is equal to division tag with attribute content editable but class is equal to update data it is equal to dollar row ID data column is equal to last and for display we have right dollar row last name variable it will print last name data after the find data from two column now and last column we want to display the lead button so we have right dollar solver a variable is equal to button tag with attribute type is equal to button name is equal to the lead class is equal to delete and in attribute we have store user it get from dollar row it variable in this delete button we will use this to leave class as selector and query code and for delete records a particular user we will get it from attributed and query code so here we have store data and dollar solver a variable now we have write dollar data variable is equal to dollar sub or a variable so here we have store all solver a data and dollar data variable now we want to get total number of rows in order table so we have create function get all data with dal or connect variable argument this function will return number of rows in table on to dysfunction we have right dollar query variable is equal to select star from user table after this we want to execute above queries so we have right dollar result variable is equal to minced Lee query function with two arguments like dollar connect variable and dollar query variable this function will execute query and store result into dollar result variable now we want to get number of rows so we have write return statement with miss clean am rows function with dollar resolve variable this function will return number of rows from dollar resolved variable now we have right dollar output variable is equal to a random this array we have right first key draw with value dollar post draw variable and second key we have rights records total and in value we have right get all data function with dollar Connect variable in third key we have right records filtered in this key value we have right dollar number filter rule variable in which we have store a number of filter rows and in last key we have right data and then value we have right dollar data variable in which we have store order data and array format so this way we have store all required data from a data table and dollar output variable now we want to send this data to fetch data function Ajax requests in JSON format so we have write echo statement with JSON encode function and in this function we have write doll or output variable now we have go to index page and here we have called fetch data function this function will load user data on webpage now we have check output in browser friends you can see when page has been loaded browser then it has load user data on webpage in table format with data tables functionality like searching pagination paging display number of records per page and many more now we want to write code for add records so we have go-to index page and here we have write button tag with attribute type is equal to button name is equal to ad it is equal to ad when we have click on this button then new row of editable table data tag will be appended under table with it user data now we have rights query code for a pen blank table row so we have right dollar with button it add would click event so when we have click on that button then this code will execute under this we have define on HTML variable is equal to open table row tag in this variable we will store other HTML code for blank row so we have a gain write HTML variable and in this variable we have event code like open table data tag with attribute content editable it is equal to data one and close table data tag here content editable we'll use for editable table data and did data 1 is used to get text of this table data tag below this we have again write HTML variable and in this variable we have a vent code like open table data tag with attribute content editable lid is equal to data to enclose table data tag for a pen code for Save button we have again write HTML variable and in this variable we have a vent code like open table data tag with button tag with attribute type is equal to button name is equal to inserted is equal to insert this code is for Save button lastly for clothes table row tag we have write HTML variable and in this variable we have a bent code like clothes table row tag this we have create HTML code for blank editable table now when we have click on this insert button blank row must be a pen at start of the table so we have write dollar with table and user data table body would prepare method and under this method we have write HTML variable so this code will append blank row let's start at the table your friends you can see we have refresh page and now you can add button has been seen on above table so here we have click on Add button then blank table row has been appended in table row with two table data which are editable that means we can type here and one button so when we have click on this button the state must be stored into database and display under this table so for this we have go to index page query code and here we have write dollar document select one method and under this method we have right click event button it insert so when we have click on insert button this code will execute under this we have first right variable first name is equal to dollar with table data a data one with tax method this method will return table data tagged with a data one text and store into this first name variable same way we have a gain right last name variable is equal to dollar with table data target data two with text method this will return text of table data target data to and store into this last name variable after getting value now we have right of statement at under condition we have right first name variable value is not equal to blank and last name variable value is not equal to blank this condition will check if both variable has some value then it will execute if block otherwise it will execute else block suppose both or one of the variable value is blank then it will execute tells block and under this block we have write alert pop message like both fields are required this message will be pop-up into browser but suppose both variable has some value then it will execute if block of code and under this block we have right Ajax request and under this request we have write first option URL set to insert dot PHP page in second option we have right method set to post method here we have used post method for sending data to server in third option we have write data option in this option we can define which data we want send to server so in this option we have defined first name and last name variable data has been sent to server lastly in ajax request we have write success callback function this function has been called if request successfully completed and it will receive data from server under this first we want to display message on webpage so first here we have defined one division tag with attribute it is equal to alert message under this tag we will display alert message so for display alert message received from server so we have write dollar with division target alert message with HTML method and under this method we have write division tag with class is equal to alert and alert success with data argument received from server this will display alert message on webpage after this we want to display inserted data on webpage and data table so first we want to destroy table data so we have right dollar with the table in order data with data table method with destroy method here destroy method will destroy table data this is because we cannot direct change table data so we have to destroy table data than after we can filter table data according to date after destroy data tables data now we want to fill data tables data so we have called fetch data function this function will fill data tables with updated data fresh from table now we have moved to write insert data code of insert dot PHP so here we have first make database connection by passing necessary details after this we have write if statement and under condition we have writes a function with dollar post first-name variable and dollar post last name variable this condition will check if both variable value is set then it will execute if block of code suppose both variable value is set then it will execute if block of code and under this first we want to clear string which we have received from Ajax request so we have create two variable like first name and last name by using this clear real escape string function we have clear string and store into this both variable now we have right dollar query variable is equal to insert and to user table the table column like first name last name with value like dollar first name variable and dollar last name variable this is insert data query now we want to execute this query so we have right of statement and under condition we have right mystically query function with two arguments like dollar connect variable and dollar query variable this function will execute query if query successfully executed then at will executed if block of code and under this block we have right to echo statement with text message like data inserted this message will be seen to user on web page now we have checked output in browser friends first we have refreshed webpage and now we have click on that button then blank role has been a pen app start of table and now in first name table data tag we have write Peter and in last name table data tag we write Parker and click on insert button after clicking on insert button we have received data inserted message has been popup on web page and inserted data has been seen on web page with delete button now we have moved to write query code for live update data so here we can see not a table table column like first name and last name on table so we can easily change data on table without opening any modal or form on web page so for update this state that we have go to index page query code and here we have write dollar with document a selector with on method and under this we have write blur event with table data tag class update so when we have read a table data text and go to next table data text and this code will execute under this first we have defined it variable is equal to dollar this with data method and under this method we have righted this code will fetch particular it from data attribute and store into it variable we have defined data attribute at table data tag so we can get that easily after this we have defined variable is equal to dollar this with data method under this method we have right column this code will fetch data from data column attribute and store into column name variable we have store table column name and data column attribute now we have defined one more value variable is equal to dollar this with text method this code will return text of particular table data tag and store into this value variable after getting necessary data now we have right ajax.request and under this request we have right first option URLs at the update dot PHP page here we have sent requested to this page in second option we have right method set to post here we have used post method for send data to server in third option we have right data option and in this we can define which data we want sent to server so in this option we have define IDI column name and value variable data has been sent to server lastly we have write success callback function this function has been called if request successfully completed and dysfunction will receive data from server under this function first we want to display success message on webpage so we have write dollar with division target alert message with HTML method and under this method we have write division tag with class alert and alert success with data received from server after this we want to display updated data on webpage and data tables so first we want to destroy data table so we have write dollar with table LED user data with data table method with destroy method this destroy method will destroy data table after destroying of data table now we will want to fill data table so we have call fetch data function this function will fill data tables with data fetch from database now we have moved to write PHP code in update dot PHP page for Rob de data so on this page first we have made database connection by passing necessary information then after we have write of statement in under condition we have write is set function with dollar posted variable this condition will check value of this variable is set then it will execute if block of code under this block first we want to clean value of dal or post value variable so we have write dollar value variable is equal to mystically real escape string function by using this function we have escape special character from dollar post value variable and store into dollar value variable now we have right dollar query variable is equal to update use a table set table column name get from Dollar post column name variable is equal to value also get from dollar value variable where at is equal to dollar posted variable so here we have make dynamic update query for all table column update of data after making of a bigquery now we want to execute this query so we have write a statement and under condition we have write mystically query function with two arguments like database connection string dollar connect variable and dollar query variable this function will execute query if query successfully execute then that will execute if block of code and under the suite of right taco statement with text message like data update and this message can be seen to user on webpage no we have checked output in browser friends first we have refreshed the browser now we can see here we can easily edit both table column data so now we want to change the name of John to Johnson so here we have changed so here you can see when we have go to next color then we have received data updated message on web page so we have refreshed the browser for check data is edited or not so here you can see data has been changed that means our code is working so by using this code we can easily live that data and data table by using PHP with Ajax query now we have moved to write code for delete so in the lead button we have defined delete class and user ID data has been stored into ED attribute so we have go to its query code and write dollar document with on method and under this method we have right click event button class delete so when we have click on any delete button this code will execute under this first we define a variable as equal to dollar this with attribute method and under this method we have right to add attribute this code will fetch data from it attribute and store into this it variable now we have write a statement and under condition we have write confirm message box with message are you sure you want to remove this so when we have click on the lead button this message has been pop on browser with okay and cancel button when you have click on OK button then we'll execute if block under this block we have write Ajax request and under this request we have write first option URL set to delete dot PHP page here we have sent requested to this delete dot PHP page in second option we have right method in this post we have write post used for send data to server in third option we have write data and then this option we can define which data we want send to server so here we have righted variable data send to server lastly we have write success callback function this function has been called if request successfully completed and dysfunction will receive data from server under this function first we want to display alert message on webpage so we have write dollar with division target alert message with HTML method and under this we have write division tag class alert and alert success with data received from server this we want to reload user table data so first we want to destroy data tables so we have write dollar with table and user data with data table method with destroy method this message will destroy a data table after destroying of data table now we want to fill data so we have called fetch data function this function will fill table with data which are fetched from user table now we have moved to write PHP code on delete dot PHP page and on this page first we have made database connection by write dollar connect variable is equal to miss click connect function with for argument like closed set the locale lost username set to root password set to blank and database name set to testing after making database connection we have write if statement and under condition we have writes a function with dollar plot said this condition will check if value of dollar posted variable is set then it will execute if block of code under this block we have write dollar query variable is equal to the lead from user table where it is equal to dollar posted variable this is the lead query now we want to execute this query so we have write a statement and under condition we have write miss clay query function with two arguments like dollar connect variable and dollar query variable this function will execute query if query successfully executed then at will execute if block of code and under this block we have right to echo statement with text message like data deleted this message will be displayed to user on web page so here our code is ready so we have saved this code uncheck output in browser friends first we have refreshed the page now we want to remove data from this data tables so here we can see the lead button on webpage so we have click on delete button after click on delete button we have received pop message are you sure you want to remove this with okay and cancel button so we have click on OK button then on webpage we have received data deleted message on webpage and data also removed from table also that means our code is working so this our small cried the application in which we have users query data tables plug-in for display data in tabular format with app functionality like pagination searching and many more many viewer has request to make video tutorial on live table add edit remove data by using data tables plug-in with PHP so we have make this type of simple video tutorial for learn how to use content editable attribute and data tables for live edit of tables single column data with insert and delete feature also by using this code we have make simple single page cried the application in which we have used data tables for display data and table format and by using content editable attribute with data tables plug-in we can live update of data without refresh of web page here by using content editable attribute in data table we have not only added data but also have also used for inserting or adding records also with PHP script if you have any query regarding this video tutorial please comment to query in comment box and if you liked this video tutorial please share with your friends or even you can also share on social media also if you want to get more update regarding our video tutorial please subscribe our youtube channel forget more update regarding release of future video lastly keep watching our youtube channel thanks for watching this video tutorial

Channel: Webslesson

Views: 178,209

Keywords: jquery add edit delete table row, live table edit with jquery and ajax php, datatables live crud using php, insert update delete in php, inline editing in php with ajax, edit record using ajax in php, delete record using ajax in php, bootstrap datatables, insert data, add data, datatables tutorial, live table edit with ajax and php, crud using datatables, contenteditable, live table add edit delete using ajax in php mysql

Id: Xhl6UC3nsnE

Length: 34min 42sec (2082 seconds)

Published: Thu Jul 06 2017