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 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 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

Comments 45

Leave a Reply

Your email address will not be published. Required fields are marked *