Custom Search in jQuery Datatables using PHP Ajax

hello friends in this video tutorial we are going to learn how can we add custom search in jQuery data tables plug-in by using PHP script with Ajax we all know jQuery data tables is a powerful jQuery plugin for display dynamic data in tabular format on web page and it has provides features like searching sorting and pagination without write any configuration or any line of code we have seen it has provide default searching while we have load data in this plugin and by using the search we can search any column data by using Ajax server-side processing but now we do not want this default searching in jQuery data table and add our custom search in this jQuery data tables plugin so in this tutorial we will seen step by step how can we add customer search filter in jQuery data tables plug-in using PHP with Ajax now let's start learning this topic so this is our testing database and in this we have one customer table with seven table column in this table we have already inserted some data so we will fetch this data and load under jQuery data table plug-in this is our index page and in this page we have already included jQuery JavaScript library jQuery data tables library bootstrap library by using this all library we can use jQuery data tables plug-in with jQuery and bootstrap library now we have moved to write HTML code so here first we have make one table with ID is equal to customer data here ID attribute we will use as selector in jQuery code in this table we have make six table column like customer name gender address city postal code country on this table we will initialize jQuery data tables plugin now we have moved to write jQuery code so here first we have make function with name fill data table with two argument like filter gender is equal to blank and filter country is equal to blank under this function we will initialize jQuery data table plugin and here both argument value is optional so without passing both argument value it will fetch all data otherwise it will fetch filter data and display on webpage under this function we have right data table variable is equal to dollar with table ID customer data with data table method this method will activate jQuery data table plug-in on this customer data table under this method 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 set to true this option will switch client-side processing to server side processing in third option we have right orders set to blank array this option will enable table column ordering now we want to remove default search box from jQuery data table plug-in so here we have right searching option which has been set to false this option will remove default search box from jQuery data table plugin lastly we have right Ajax option by using this option we can send Ajax requests to server and in this first we have right first option URL set to fetch dot PHP here we have send request to this page types in second option we have right type set to post method here we have used post method for send data to server lastly we have write data option in this option we can define which data we want send to server here we want to send data like his filter gender and filter country get from this function argument now we have moved to write PHP code and first we want to make database connection in database connection dot PHP file here we have write dollar connect variable is equal to new class object with three argument in first argument my SQL host said to localhost and database name set to testing in second argument we have set username set to root and password set to blank after making database connection now in fetch dot PHP file we have write include statement with database connection dot PHP file name it will make database connection on this page below this we have right dollar column variable is equal to array with table column name like customer name gender address city postal code and country we will use this data in column sorting now we have right dollar query variable is equal to select star from customer table so here we have start right select query for fetch data from customer table here we have not used default data tables search box and we will custom search filter in jQuery data tables plugin so here we will not write search query for all table column after this we have right if statement and under condition we have right is set function and under this we have right dollar post-filter gender variable and dollar post-filter country variable this condition will both variable value is set then after we have write an operator and write dollar post-filter gender variable not equal to blank and dollar post-filter country variable value is not equal to blank this condition will check both variable has some value then it will execute if block of code suppose above condition true then it will execute if block of code and under this block we have right dollar query variable and in this variable we will append query like we're gender table column is equal to dollar post-filter gender variable and country table column is equal to dollar post-filter country variable this condition will search data in gender and country table column you EPIK after write search condition 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 is set then it will execute if block otherwise it will execute else block here when we have click on any table force or table column then this dollar post-order variable has been passed by data tables jQuery 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 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 dollar post-order zero 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 will return sorting direction like ascending and descending but supposed 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 order customer ID table column in descending order [Music] now we have defined $1 query one variable is equal to blank in this variable we will store query with fetch limit data then after we have write 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 posts start variable comma dollar post length variable here both variable 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 customer table first we want to execute only dollar query variable query forget number of filter record so we have write dollars statement variable is equal to dollar Connect variable with preparer statement with dollar query variable it will make query for execution for execute dollar query variable query we have rightt dollars statement variable with execute method this method will execute query birth below this we have right dollar number filter row variable is equal to dollar statement variable with row count method this method will return number of row affected after query execution after getting number of filter row now we have right dollars statement variable is equal to dollar Connect variable with preparer statement and under this we have right dollar query variable dollar query one variable here we make bot query for execution and for execute we have rightt dollars statement variable with execute method this method will execute query after query execution we have right dollar result variable is equal to dollar statement variable with fetch all method this method will fetch query execution result and store under dollar result variable now we have right dollar data variable as 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 variables 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 get from dollar row variable first we want to store customer name data in dollar sub-array variable so we have right sub-array variable is equal to dollar rho customer name variable after this we have again right dollar sub-array variable is equal to dollar row order gender variable here we have store gender data in dollar sub array variable same way we have again right dollar sub-array variable is equal to dollar row address variable here we have store address data in dollar sub array variable after this we have right dollar sub-array variable is equal to dollar rose city variable here we have store city data in dollar sub-array variable now we have rightt dollars sub-array variable is equal to dollar roh postal code variable here we have store postal code data under this dollar sub-array variable and lastly we have again write dollar sub-array variable is equal to dollar roh country variable and here also we have store country 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 subarray data in dollar data variable now we want to get total number rows in customer table so here we have make function count all data with dollar Connect variable argument this function will return total number of rows in table under this function we have right dollar query variable is equal to select star from customer table below this we have right dollars statement variable is equal to dollar Connect variable with preparer statement with dollar query variable this statement will make query for execution for execute query we have write dollars statement variable with execute method this method will execute query lastly we have rightt return statement with dollars statement variable with row count method this method will return number of rows in customer table now we have right dollar output variable as equal to array and in this array we have right first key draw with value dollar post draw variable you in second key we have right records total and in value we have right count all data function with dollar connect variable you in third key we have right 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 order 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 fetch data function Ajax requests in JSON format so we have right echo statement with JSON encode function and in this function we have write dollar output variable this function convert data into JSON string now we have go to index page and here we have called fill data table function without any argument so this function will display data without filter on webpage in jQuery data table plugin friends here we can see when page has been load in to browser then we can see customer data in jQuery data table plug-in here we can see default search text box has been removed because here we have make custom search in jQuery data table for this we have go to index page and here we have right select tag with name and ID is equal to filter gender with two option like make and female this filter is for filter male and female customer data after this we have again right select tag with name and ID is equal to filter country by using this select box we can filter customer data by country now we want to fill this select box with data so here we have right include statement with database connection dot PHP file this file will make database connection after making database connection we have rightt dollar query variable is equal to select distinct country column from customer table order by country in ascending order this query returned unique country name from customer table below this we have right dollars 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 for query execution we have rightt dollars statement variable with execute method this method will execute query after query execution we have right dollar result variable is equal to dollar statement variable with fetch all method this method returned query execution result below this we have right for each loop and under condition we have right dollar result variable as dollar row variable by using this we can fetch data from dollar result variable under this loop we have right dollar country variable and in this variable we have a pend a CH tml code like option tag with value is equal to dollar row country variable and in option text we have write dollar row country variable this code will add all unique country name with option tag under this dollar country variable so for fill country select box we have right echo statement with dollar country variable it will fill country select box with data after Phil country select box now we have right button type is equal to button name and ID is equal to filter by click on this button we can filter customer data by gender and country name for this we have go to jQuery code and here we have write dollar with button ID filter with click event so when we have click on button then it will execute this block of code under this we have right filter gender variable is equal to dollar with select box ID filter gender with value method here we have store filter gender select box value under this filter gender variable same way we have again right filter country variable is equal to dollar with select box ID filter country with value method here we have store filter country select box value under this filter country variable now we have right if statement and under condition we have right filter gender variable value is not equal to blank and filter country variable value is not equal to blank if this condition true then it will execute if block of code under this block first we want to destroy jQuery data table for this here we have write dollar with table ID customer data with data table method with destroy method this code will destroy jQuery data table after this we want to load filter data in jQuery data table so here we have called fill data table function with two arguments like filter gender and filter country it will load filter data on data table but suppose above condition false then it will execute else block of code and under this we have right alert statement with message like select both filter option this message will be pop-up if we have not select any or both filter so suppose we have not select any or both filter then we want to load all data in jQuery data table so here we have simply called fill data table function this function will load all data in jQuery data table now check output in browser now friends first we have refresh webpage and after refresh of webpage above table we can see two filter like select gender and select country filter with button so by using this filter we can filter jQuery data table data now we want to get male customer data in United States so here we have select male from gender select box and United States from country filter and click on filter button after click on filter button we can see United States mail customer details on web page without refresh of web page and here we can see jQuery data table functionality like pagination sorting is working we have only removed default search text box for customer search same way we want to get female customer details of Germany country so we have select female from filter gender select box and Germany from filter country select box and click on filter button after click on filter button we can see female Germany customer details on web page in dateable without refresh of web page now we have not select any one option and click on filter button so here we can see pop-up message and after this it has load all data in jQuery data table so this way we can implement custom search filter in jQuery data table plug-in by using PHP script with 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 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 you

Channel: Webslesson

Views: 59,894

Keywords: Datatables, jquery, datatable, php, ajax, custom search, custom filter, php ajax datatable custom filter, datatables custom filter, datatables custom search, ajax php custom search, php ajax custom filter, datatables dropdown filter server side, datatables custom search server side, data table with filter, create filter on single column using Ajax, server side filtering, datatables custom search using php ajax, add custom search in datatable jquery

Id: M0cEiFAzwf0

Length: 27min 25sec (1645 seconds)

Published: Wed Sep 19 2018