PHP Search Box Tutorial - Filter Data in HTML Table Using PHP & MySQL Database

in this video tutorial I will show you how to create search form and also how to filter data in PHP so stay with me Hey what's up guys, Senaid here from codingpassiveincome.com the place where I help others to become a web developer much easier and faster than they will do it and they own so if that is something that interests you consider subscribing so before we even start with this tutorial and write actually code I wanted to show on my whiteboard the basics of that you need to understand for this tutorial so what you are going to do is we are actually will create on input so this will represent some search query and this is where the visit will enter what he is looking for and then they are going to create one drop down here and visitor will be able to choose whether he wants to search for basic firstname or lastname and we are going to have one submit button we will name it find so this is basic HTML form and if you don't know what are the HTML forms basically have some inputs and for our HTML form we have two methods the first method is get okay and the second method is post and depending on the type of the phone that you are getting in PHP you can expect the data from those inputs by using get and by using post and that is all that you need to remember so when we go into the process of creating form you just have those two things in mind and the last think that I want you to understand is how to actually write the query in SQL that will actually serach something in the database so the query will be SELECT and then here we are going to write the column names that will actually select from the table if there is any data so for example we will say select first name and then we need to say from and then we will write the table name and let's say we will name users and after that we are going to make one condition so say WHERE and after that depending on what we have here we will search the data this so will say where for example the vistors selected column name is last name and then we will say like, ok and this search query so let's say it will be query so those signs means that anything before this query and after this quick query will be just ignore and the only important thing that we are looking in the column last name is this query so just remember we are the things that are always the same are SELECT, FROM, WHERE, LIKE. So those are the things that are always the same and you'll just SELECT column name from table name WHERE coumn name, LIKE this query and this query in SQL who does give us the result that we are looking for so let me not use my PC and actually show you how to write the code that will put all those things together so now once you understand the basics and what you have to learn before start with coding search form let me now show you how to actually write the code so now I'm here at my PHPstorm and I have just created new project and started my localhost XAMPP server. So I'm going to create a new empty PHP file I will name it search.PHP and hit OK and now first we need to go and create some database and the table will will store some data so I'll go in my PHP my admin' go to the new write the name of the database I'm always using utf-8 click and hit create and let's say we will have table users and we can say we have four columns so I will say he ID okay then have first name let's say 50, last name let's say 50 and let's say email against 50 okay and just hit save okay so now we have table php search and we have table users so let's now does quickly insert some users so I'll just open website fakenamegenerator.com just together some random name so let's say no use this one okay email is not important so let's get another one this is all some test data for this tutorial but I want to show you step by step how to do it okay and let them sit one more so we have our three users in this database and that's all what we need so like I said on the my whiteboard first thing that we need to do is to create HTML form so I'll just write some basic HTML code and here I will see four method I will use post action I'll use this file and let's say I will have one input type text' name q and place holder and now let's say I will have one select so that users are able to select what they are searching do is it first name or last name that they want to filter so I will say here name column and let's say they will have two options first options is first name and second option is last name okay and we are going to have one submit button so let's now just open and check we created correct form so yes we have one input the type that we have the filter which is first name or last name and have find we can just here say select filter so now they will choose what they want to find and they click on find nothing will happen because we didn't make any PHP code so far so now we need to know well someone press this button to detect that so let's say submit and in the PHP you'll see if isset a post submit which means if someone pressed this button the code be here will execute so first thing that we need to do is we'll accept the informations from this form but let's connect the data so I'll create a new variable connection equal new MySQLi and here I will specify the host which is localhost in this case I will specify the username which is root in my case and the password is an empty string and the last option I will give here is the database name so that the later don't need to use select datebase query so I would say phpSearch okay and now here we can say $q=$_POST['q']; which means in the variable q we will store whatever to user the visitor input here okay so let's not just enough to see what will happen so that's it but the problem here is if someone tried to break our system we don't have any protection so we need to do a real_escape_string for any data that we've entered in the form so I will say connection real_escape_string and on this way our form is much secure than if we don't do this and the same thing we are going to do for a column so I'll do this cone and now we need to check which column someone choose so if let's say column equal empty it means if the visitor didn't change this so he didn't whose first name or last name let's see the default column will be first name so what I did here is basically this is like some protection if someone is trying to manipulate the data in the drop down so I've seen here if a column is empty or column doesn't match first name or column doesn't match last night so if someone manipulated this data we are going to make a change here and we'll set a default like first name otherwise it will be whatever the use the whisper choosed so now all we need to do is to execute query and find the data depending on has selected drop-down so they will say data equal connection query and here we will say SELECT let's say first name FROM the name of out of our table is users which is WHERE and now we are going to save the coluumn LIKE and now we need to specify hit the query okey, so say I will write LIKE this and put in here query so what this basically means that anything before and after this query will be accepted and all we are looking for is those words inside the query so now wq will just check how many rows we have if data num_rows bigger than zero that means that we have some data in the table for this search query otherwise we will just save echo a crow so now we can test this so let's say does hit here something like this and select first name says your search query doesn't met any data but if we try to insert any name let's say this and we select first name we click on find them heels are empty because this part here is executed but at the moment we don't have any code so we are going to write it now so I will say so what just rename this out uh say SQL well I will say here while data equal SQL fetch array we can say echo data first name and we'll concatenate string and let's input want new line so let us now refresh and say continue you can see we have both the first name David see but if we now try to search for something for any first name that is that with this character we can say like this and put search field the first name he'd find and as you can see we have found those two visitors and if we now try to change this and say we are certain by date and we are searching by last name when we click find in a funk Louie and which means that on this way we have created search form and also we are doing some filtering of the data so the visitors are suppose first name or last name so guys pretty much that's it I hope so very basic and easy to understand tutorial all you need to remember is the things I show it on the whiteboard so if you guys have any questions or problems with the code that let me know in the comments below and also if there's anything else that I can do for you just let me know take care

Channel: Coding Passive Income

Views: 50,242

Keywords: php search box, php search mysql database, php search engine, php search form, php search, php search function, php, mysql, tutorial, php (programming language), php tutorial, beginners, tutorials, database, learn php, learn, senaid bacinovic, filter php, how to filter data in php, data filtering php, filter data in php table

Id: XVsMCtA1Jow

Length: 19min 33sec (1173 seconds)

Published: Fri Jun 02 2017