Ajax Crud with JQuery Datatables by using PHP PDO

hello friends in this video tutorial we are going to make simple crud system by using PHP PDO modal which is an object-oriented version for do operation with database it is more secure than other database driver and this driver there is no possibility of SQL injection hacking for making this system we have also used query data tables plugin with Ajax and bootstrap framework in the system we will display tabular data and query data tables plug in n for insert update delete operation we will use Ajax so all this operation will be done on single page without going to other page so in short we have make simple single page creds application in this application we have also used bootstrap framework we will use bootstrap model for insert and update data in this application we will also upload image with data also so we have also covered how to upload image with form data by using Ajax you now let's start learning this thing's this is our crud database and in this we have one table with name like users and in this table there is four table column like head first name last name and image in this table we have already inserted some data into this table this is our index page and in this page we have include some JavaScript and CSS file in JavaScript we have includes query library file query dot data tables files for data tables plugin data double dot bootstrap file this file is required when we have include data tables with bootstrap and M CSS we have include bootstrap CSS file and data table dot bootstrap file this file is required when we have used bootstrap with data tables plugin this is upload folder in which we will upload use your image file into this folder first we want to fetch data from crud database and display on the index page so we have to find one division tag with attribute class is equal to table responsive Tagg we have defined one HTML table with attributes it is equal to user data in this table we will load on tables plug-in with crud database data under this table we have define open and close table head tagging between this tag we have defined open and close table row tag in between this table we have defined five table heading like image first name last name edit and delete after defining table definition now we want load data tables plug-in on this table so we have go to rights query code and here we have write variable data table is equal to dollar with table and user data as selector with data table method this method will initialize that hateable plugin on this table under this data table method we have set processing options set to true this option will activate processing option of that a tables plugin then after we have write other option like server side option set to true this option activates server side operation after this we have right order options set with blank array and data table method we have right Ajax method and under this method we can define URL options set to fetch dot PHP we have send requested to this fetch dot PHP page dunder Ajax we have also right second option like type set to post here we have used post method suppose we want to remove table column order from first fourth and fifth table column because in first column we have display image in four and fifth table we have display edit and delete button and this plug-in will automatically activate table order so remove table column Ajax order so we have right column definition option and under this option we have right targets option and under this option we have rip table column number like zero three and four and array format for remove water we have right order able option set to false this option will remove table column order from above define table column you after the find are terrible definition now we have moved to write PHP code in fetch dot PHP page so first we have make database connection so we have create one database dot PHP page and under the page we have write dollar connection is equal to new PDO driver class object under this object we have write my SQL drive with double colon host is equal to locale lost database name is equal to grad username set to root and password set to blank value so we have make database connection in this database dot PHP file database connection filed now we have moved to fetch dot PHP file and we want to include that file into this fetch dot PHP file for making database connection so we have write include statement with database dot PHP file on this page first we have write dollar query variable is equal blank value in this variable we will create select query for fetch data from users table then after we have also write dollar output variable is equal to array data type in this variable we will store user's table data in HTML format and other required data for query data tables after this we have write dollar query variable and in this variable we have a pen query like select star from users table so this basic select query in this variable we will append query for search data and order table column data and limit data per page now we have write a statement and in condition we have writes a function with dollar post search value variable if we have write something in search box then this condition will true and it will execute if block of code under this condition we have write dollar query variable and in this variable we have event query like where first name like percentage dollar post search value variable percentage here dollar post search value variable value get from data tables search box and will search query and first name table column again we have right dollar query variable and we have a vent query like or less name like percentage dollar post search value variable with percentage sign this will search query and last name table column data so if we write something in data tables each box then this block of code will execute after this we have a gain write if statements and in condition we have rights of function with dollar post or other variable if we have click on any table column or two then this condition will true and it will execute this if block of code under this block we have right dollar query variable and under this variable we have a vent query like order by dollar post-order zero column variable with dollar postorder zero directory variable so when we have clicked on table column order arrow then this block of code will execute otherwise it will execute cells block of code and under this we have right dollar query variable and under this variable we have a vent query like ordered by it in descending order so this code order table column order now we have write a statement again for limit the data per page so we have write a statement and under this we have write dollar post length variable value not equal to minus 1 so 1 page low this condition will be true under this block we have right dollar query variable and under this variable we have a vent query like limit dollar post start variable comma dollar post length variable this query for display data poor page now we have right dollar statement variable is equal to database connection string like dollar connection variable with preparer statement and under the statement we have right dollar query variable after this we want to execute this prepared statement so we have right dollar statement variable with execute method this method will execute prepare statement now we want to fetch result from query execution so we have write dollar result variable is equal to dollar statement variable with special method this method will fetch query execution and store into dollar results variable then after we have write dollar data variable is equal to your a data type in this variable we will store user data now we want to get the number of rows in query results so we have right dollar filtered rows variable is equal to dollar statement variable with row count method this method return number of rows from query result after this we want to fetch data from dollar results variable so we have write for each loop with dollar result variable as dollar row variable by using for each loop we will fetch data from dollar result variable under this loop we have write dollar image variable is equal to blank value in this variable we will store image name from user table after this we have write if statement and in condition we have write dollar image variable value is not equal to blank this condition will check if this variable value is blank then it will execute if block of code under this block we have write dollar image variable is equal to image tag with attribute source is equal to upload folder name slash dollar roll image variable with attribute width is equal to 50 and height is equal to 35 but suppose dollar image variable is equal to blank then it will be equal to else block and under this we have right dollar image variable is equal to blank after this we have again to find one new variable dollar sub-array is equal to array data type in this variable first we want to store dollar image data so we have right dollar solver a variable is equal to dollar image variable after this we want to store value of first-name table columns so we have write dollar sabor a variable is equal to dollar row first name variable saying we want to store value if of last name table call them so we have right sub or a variable is equal to dollar row last name variable now we want to create update data button and store input dollar sabor a variable so we have write dollar sabor a variable is equal to button tag with attribute type is equal to button name is equal to update it was equal to dollar row variable class is equal to update and button text is equal to update here and its attribute we have stored dynamic value of in table column same way we want to create the lead button and store input dollar solve array variable so we have right dollar sabor a variable is equal to button tag with attribute type is equal to button name is equal to delete that is equal to dollar row in variable class is equal to delete and button text is equal to the lead here also we have stored dynamic value in this attribute now we store dollar sabor a variable value into dollar data array variable so we have right dollar data array variable is equal to dollars sub-array variable so this we have streched data from user table and store into dollar data variable we want to get wand to get total of rows and users table so for this first we have create one function dot PHP file and then this we have create one function get total all records function this function will return total records of user table under dysfunction first we have include database connection filed then after we have rightt dollar statement variable is equal to database connection variable dollar connection with preparer method with statement like select star from users table now we want to execute this prepared statement so we have right dollar statement variable with execute method this method will execute prepare statement and in this function lastly we have right return statement with dollar statement variable with row count function this function return number of rows from query execution now we have a game go fetch dot PHP function and first include this function dot PHP under this page by using include statement after including function dot PHP page now we have right dollar output variable is equal to a Rand in this array we have right first key draw with value dollar post draw variable and second key we have right records total key with valued dollar filtered rows variable in which we have store rows in third key we have write records filtered with value get from get total all records function which we have create under function dot PHP file and last key we have right data with taller data variable in which we have store user table data all data and dollar output variable now we want send to Ajax request so we have right echo statement with JSON encode function with dollar output variable this function will convert PHP array to Dyson string and send to Ajax request now we have save code and check output on browser you friends you can see when page has been loaded then we can see that users table data has been displayed in two data tables plugin here you can see user data and data tables plug-in with extra feature like search table data Ajax table column order and pagination automatically this all feature we have get by using data tables plugin we can also see update and delete button also now we have moved to write our data and new data so we have go to index page and here write button tag with attribute type is equal to button it is equal to add button data toggle is equal to modal data target is equal to hash user modal and button text ad here data toggle attribute is used for direct action to modal and data target attribute is used for activate user modal now we want to create modal for insert data form so first we have write division tag with attribute it is equal to user modal and class is equal to modal and fade under this division tag we have right division tag a tribute classes equal to modal dialogue this class to find modal then after under this tag we have defined form tag with attribute method is equal to post it is equal to user form and encryption is equal to multi-part form data this attribute is used when we have upload file to server after this we have right division tag with attribute class is equal to modal header this class define header of modal and under this tag we have one close modal button and header tag with class is equal to modal title with text like add user after the finding cutter now we have write the vision tag with attribute class is equal to modal body this class to find body of modal under motile body we have to find form element and first we have create text box for enter first name so we have write input type is equal to text with attribute name is equal to first name and it is equal to first name same way for enter last name we have right input type is equal to input type is equal to text with attribute name is equal to last name and it is also equal to last name for upload user image we have write input type is equal to file with attribute name is equal to user image and did this also user image below this file tag we have create span tag with attributes it is equal to user uploaded image under this tag we will display uploaded image after the finding form field now we have write division tag with attribute class is equal to modal footer this class to find footer of modal in this modal we have right input type is equal to hidden with attribute name is equal to user ID and get this equal to user ID in this tag we will store it of user after this we have also defined one more hidden tag with attribute name is equal to operation and did also operation and value is equal to ad in this tag we will store action like insert or update below this tag we have create one submit button like input type is equal to submit with attribute name is equal to action it is equal to action and value is equal to ad when we will click on this button form will be submitted now we have moved to write query code for insert data so we have write dollar with document selector with on method and under this method we have right click event with form if user form with one function event so one form will submit then this code will execute under this code first we have an event god preventdefault method this method will stop to submit form data then after we want to get the value of textbox of first name and last name and store into first name and last name variable after getting text box value now we have rightt variable extension is equal to dollar with file tag in user image with value method dot split method with pump method on to lowercase method this code will return extension of selected image in store into extension variable now we have write a statement and condition we have write extension variable value is not equal to blank if this variable value is not blank bandit will execute if block of code under this block we have write of statements and in condition we have rights query dot in a reign method with two argument in first argument we have write extension variable and in second argument we have write allowed image extension is equal to minus one you if selected file extension is not matched with a love extension then it will execute if block of code and under the suite of right alert message like invalid image file after this we want to remove selected file from file tag so we have right dollar file Takia user image dot value method with blank value then after we have again write a statement and condition we have right first name variable is not equal to blank and last name variable is also not equal to blank value if both variable has some value then it will execute if block of code otherwise it will execute else block of code and now's block we have right alert with message like both fields are required this alert message will be displayed on webpage but suppose both variable has some value then it will execute if block of code and under this we have right Ajax request and in this first we have right URL option set to insert dot PHP we have send requested to this page in second option we have right method option and set to post method we have used post method for send data to server in third option we have defined which data we want to serve or define and data option so we have right data option with new form data object with this and fourth option we have right content type set to false and in fifth option we have right process data set to false and last option we have right success callback function this function has been called if request success first we want to alert message which we have received from server so we have write alert with data then after we want to reset form fields so we have write dollar with four mid user form index 0 with reset method this will reset form field want to hide user modal so we have right dollar with modal and user modal with modal method with hide and lastly we want to reload data tables so we have right data table dot Ajax thought reload method this method will read load data table on web page without refresh of page now we have more to write insert data PHP code and insert dot PHP file and under this file first we have include database connection file and function file into this file and for making database connection and use the function from function dot PHP file after this we have right if statements and in condition we have rights at function with dollar post operation hidden variable this condition will check its value of this hidden operation variable value is set then it will execute if block of code under this block we have a gain write a statement with condition like dollar post operation variable value is equal to add this condition will check if value of this variable is a then it executes if block of code so suppose this variable value is add then we'll execute if block of code first we have write one dollar image variable is equal to blank under this variable we will still upload an image name then after we have a gain write if statement and under condition we have write dollar files user image name variable value is not equal to blank this condition will check if we have select any image from file tag that this condition will true and it will execute if block of code under this block we have right dollar image variable is equal to upload image function this function we will make in function dot PHP file this function will upload selected image from source to upload folder and return image name so first we have go-to function file and write function upload image function under dysfunction have right dollar extension variable is equal to explode function with two argument in first argument we have right string delimiter dot sign and in second argument we have right dollar files user image name this function will make array like file name and extension from selected user image name then after we have rightt doll or new name variable is equal to round function with dollar extension variable with one index which return extension of selected file here we have make new name for selected image and store into dollar new name variable now we have write dollar destination variable is equal to upload folder names flash doll or new name variable here we have define upload file destination with new name of selected file then after we have write move uploaded file function with two argument in first argument we have write temporary name of selected file so here we have write dollar files user image temporary name variable and then second destination argument we have write doll or destination variable this function will upload selected image to upload folder destination now we want to return image name of uploaded file so we have write return statement with dollar new name variable so this way this function will return image name now we have moved to insert dot PHP file and here we have write dollar statement variable is equal to database connection variable dollar connection with prepare statement and under this we have write insert query like insert into users table the table column like first name last name and image with values like double colon first name double colon last name and double colon image here colon is binding parameter after this we have right dollar resolve variable is equal to dollar statement variable with execute method and under this method we have right array with key line : first named with value dollar post first name variable second key : last named with value to all our post last name variable and call an image key with value dollar image variable this method will execute prepare statement with the value of this bonding parameter ended will insert data into table now we want to check data insert it or not so we have write a statement and under condition we have write not operator with empty function with argument like dollar resolved variable this condition will check if value of this dollar result variable is not empty then we'll execute if block of code and we have right to echo statement with message like data inserted this message will be C to user on browser when data will be inserted so our data and cert by using PHP though is complete so we have checked output in browser friends here you can see that when page has been loaded then users data has been display in table format with update and delete button and above this table we can see the add button so we have click on add button after clicking Add button modal has been displayed on web page with three table field so in first field first name we have Andrew Smith and second field last name textbox we have right Johnson and the last user image fall we have select user image after filling all field now we have clicked on Add button clicking on add button we have received data has been inserted message on webpage and inserted data has been display on their data tables with user image without refreshing of page so here we have insert data with upload image by using PHP bill with query data tables plug-in with Ajax and bootstrap now we want to update this data so here you can see update button and in this button we have one attribute class is equal to update and in its attribute we have store user ID so based on this thing's we will fetch data from user table and display under modal form and then after we will update this data so we have go index page query code and here we have write dollar document as selector with on method and under this method we have right click event with class name update with function so when we have click on update button then this code will execute under this first we want to get user ID so we have defined one variable user ID is equal to dollar this selector with attribute method under this method we have writes a attribute this code will fetch value of it attribute and store into this user variable then after we have rightt dollar dot ajax.request and under this request first we have right URL option set to fetch single dot PHP page here we have send requested to this file in seconds option we have right method set to post method we have used post method for send data to server in third option we can define which state that we want send to server we can define in this data option and here we have write user variable so we have sent value of user variable send to server and fourth option we have write data type here we can define in which data type you want receive data from server so here we have write JSON data type so it will receive data and JSON data type format lastly in Ajax request we have write success callback function this function has been called if request completed successfully and it will receive data from server in this function first we want show this user modal on webpage with data so we have right dollar with modal and user modal with modal method under this method we have right show this code will initialize modal on webpage this we want to fill first name text-box with data so we have right dollar with the text box said first name with value method and under this method we have right data dot first-name which we will receive from server then after we want to fill last name textbox with data so we have right dollar with last name textbox said last name with value method and under this method we have right data dot last name now we want to change modal title from AB user to edit user so we have write dollar with class name modal title with text method and under this method we have write edit user it will change modal title after this we want to assign user it variable value to hidden field with good user ID so we have right dollar with hidden fielded user ID with value method and under this method we have right user variable then after we want to display uploaded user image under span tag with it user upload image so we have right dollar with spenta gives you the rough load image with HTML method under this method we have write data Dodge's your image we will receive the state of from server in JSON array format now we want to also change the action button name from add to edit so we have write dollar with button it action with value method and under this method we have write tax tenets and we want also change value of operation hidden field also so we have write dollar with hidden field did the operation with value method and under this method we have write edit text so it will change hidden field value to add to edit now we have move to write PHP code for fetch single user data and fetch single dot PHP file and under this file first we have include database connection file and function file for making database connection and using function from function file have write a statement and under condition we have writes a function with dollar post user variable this condition will check if value of this variable is set then it will execute if block code under this block we have right dollar output variable is equal to array data type in this array variable we will store single user data and array format then after we have right dollar statement variable is equal to database connection variable dollar connection with prepare statement and under this statement we have write query like select star from users table where at is equal to dollar post user a variable limit one this query will fetch particular single user data now we want to execute this statement so we have write dollar statement with execute method this method will execute this select query after execute a query now we want fetch data from query results so we have write dollar result variable is equal to dollar statement with fetch all method this method will fetch query execution result and store into this stall or result variable now we want fetch data from this dollar result variable so we have write for each loop with dollar result variable value as dollar row variable under this loop we have write dal or output first name variable is equal to dollar row first name which we has been fetched from dollar result variable same way for last name data we have write dollar output last name variable is equal to dollar row last name variable for image data we have write a statement and under condition we have write dollar roll image variable value is not equal to blank this condition will check if value of this variable value is not equal to blank then it will execute if block of code suppose this variable value not blank then it will execute if block of code and under this we have write dal or output user image variable is equal to image tag with source is equal to upload folder name slash dollar Rho image variable with attribute width is equal to 50 and attribute height is equal to 35 it will display user image on web page here we have also add one hidden tag with image name as value so we have write input type is equal to hidden with attribute name is equal to hidden user image and and value attribute is equal to dollar roll image variable so this way we have store image name under this hidden field but suppose dollar roll image variable value is blank then it will execute cells block of code and under this we have write dal or output user image variable is equal to input type is equal to hidden with attribute name is equal to hidden user image and end value attribute is equal to blank value so we have store all data and dollar output array variable now we want to send this data to Ajax request in JSON format so we have write to echo statement with JSON encode function with argument dollar output variable this function will convert PHP array to Dyson string and send to Ajax request now we have checked output in browser your friends you can see that when we have click on update button of any user data then modal has been initialized on webpage with form fields has been filled with data and user image has been displayed below file tag here our button name has been changed to edit and modal title has been changed to edit user so now when we have click on this edit button then user data has updated and display on web page so when we have click on edit button and form data has been submitted by Ajax and we have already rights query code on this event and in that we have send request to insert dot PHP file so we have directly go to insert dot PHP file on this file here we have write a statement with dollar post-operation variable value is equal to edit this condition will check if this variable value is equal to edit Bendis if block of code will execute under this block we have right dollar image variable is equal to blank value under this variable we will store image data into this variable after this we have write if statements with dollar files user image name variable value is not equal to blank this condition will check we have select any image from file tag then block of code will execute under this block we have write dollar image variable is equal to upload image function which we have defined in function dot PHP file which we have already include into this page and this function will upload selected file and return uploaded image name and store into dollar image variable but suppose we have not select any image with file tag then at will execute else block of code and under this block we have write dollar image variable is equal to dollar post hidden user image variable so it will fetch the image name from this variable and store into dollar image variable now we have write dollar statement variable is equal to database connection string variable dollar connection with prepare statement and under this statement we have write query like update users' table set first name is equal to colon first name last name is equal to colon last name image is equal to colon image where it is equal to colon it after this we want to execute this update query so we have write dollar result variable is equal to dollar statement variable with execute method and under this method we have write array with key : first named with value to all our posts first name variable : last name key with value dollar post last name variable : image key with value dollar image variable and : it key with value dollar post user variable this method will execute update query with this array value and data will be updated now we want send to Ajax requests so we have right to echo statement with text message like data updated which will display to user on webpage so our code is ready so we have check output in browser friends here you can see user data in table format now we want to update some data so here you can see first data which we have inserted last time so here we have click on update button after clicking this button modal has been pop up with data now we have changed last name from Johnson to Peterson and without select user image and click on edit button after clicking edit button data updated message has been pop-up on web page and on table data has been changed to Johnson to Peterson that means our code is working so now we have moved to write code for delete button so in the lead button we have also defined class name is equal to the Lita's query selector and attribute that we have also store user it value into this attribute so we have go to index page query code and here we have write dollar document as selector with on method with click event class name delete with function so when we have click on delete button then this code will execute we have rightt variable user ID variable is equal to dollar this without tributed this will fetch data from media attribute and store into user it variable after this we have right if with confirm message like are you sure you want to delete this if we have click on delete button then it will pop up this message with two button okay I'm canceled if we have click on capsule bot and Dan will execute tells block of code and down to this block we have write return false so when we have click on cancel button Dan no action will be performed but suppose we have click on OK button Dan will execute if block of code and under this we have write Ajax requests with first option URLs set to delete dot PHP page in second option we have right method and we have set post method for send form data to server in third option is data and then the send user aid variable value send to server in Ajax laughs we have right success callback function this function has been called if Ajax request successfully completed under this function we have right alert with data so it will display alert message which received from delete dot PHP page and after this we want to reload data tables data so we have write that a double dot Ajax dot reload method it will reload that a table and updated data on web page without refreshing web page now we have moved to write PHP code on the lead dot PHP page and on this page we have write include statement we have we have include database connection file and function dot PHP file under this page here first we have write a statement with said function with dollar post user a variable this condition will check if value of dollar post user a variable is set then it will execute if block of code under this block we have right dollar statement variable is equal to database connection string variable dollar connection with preparer statement and under this we have right delete query like delete from users table where read is equal to : now we want to execute preparedstatement so we have right dollar results variable is equal to dollar statement with execute method under this method we have right array with key : it with value to all or post user variable this method will execute prepare statement with this array binding data and data will be deleted we want sound data to ajax.request - so we have right echo statement with text message like data deleted this message will be displayed on webpage so our code is ready for delete data so we have saved this code uncheck output on browser friends here you can see the user data on table without a delete button so when we have click on delete button then pop message box appear on web page with OK and cancel button so when we have click on cancel button and no action perform so we have a gain click on delete button and now click on OK button so that user data has been removed from table and on webpage that user data has been removed so our code is working for delete or delete of data by using PHP Pro with Ajax and query data tables plugin and our simple single page crud system is also ready by using PHP PDO modal with Ajax query data tables plug-in with bootstrap framework and this simple application can fetch data from MySQL table you can insert or add data with upload image functionality without refreshing a web page you can also edit or update data with upload image functionality and display updated data on web page without refreshing a page and lastly you can delete data from table also here we have used query data tables plugins so you can additional functionality of live searching of data Ajax table call them pagination this all feature you can get without writing line of code so we have made complete crud system by using PHP PDO database driver with Ajax query data tables plugin with bootstrap so you can perform all crud operation with upload of image without refreshing of web page and can do all operation on single page without going to other page friends if you like this video tutorial please share this video tutorial with your friends or even you can also share on social media also if you have any query regarding this video tutorial please comment your query in this video comment box you can find complete source code of this video tutorial from this video description and lastly if you want to get more update regarding our video tutorial please subscribe our YouTube channel for getting future upload a video tutorial thank you for watching this video tutorial

Channel: Webslesson

Views: 156,281

Keywords: crud bootstrap php, bootstrap crud modal, php ajax crud example, php pdo crud with Jquery and ajax, php pdo insert update delete, php ajax image upload, image uploade without page refresh, ajax jquery add edit delete in php, insert update delete without page refresh, jquery datatables insert update delete, php mysql crud tutorial, ajax crud with bootstrap modals and DataTables, jquery datatables server-side processing, ajax crud using bootstrap modals and datatables

Id: cRMY8fIj8cs

Length: 49min 56sec (2996 seconds)

Published: Tue Mar 21 2017