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

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

Danny Hutson

45 thoughts on “PHP Search Box Tutorial – Filter Data in HTML Table Using PHP & MySQL Database

  1. Good job coding passive in…. can you provide video for multiple keyword search box to display data like youtube, dailymotion search box I am waiting for your next tutorial have a nice day bro…

  2. How to do search with function type function search () instead of via /search.php, in this case using model and controller

  3. Is there a way to incorporate this into a wordpress theme? I have been learning alot of stuff lately and have some questions haha

  4. i want to make classified website but i dont know how to make searchbox to get data from category and display please help

  5. Hey, I've done all what you've said but I keep getting the 'Your search query doesn't match' at the top of the screen permanently without changing. Any ideas what part of my code may not be workin. I think it may be line 10/11. I had to remove the word query: before the select statement. Does this matter? Cheers man!

  6. why it does not work for me. no error but no output. can you help me I have been stuck a week ago for this coding search.

  7. Hi Senaid
    Thank you for the video..
    I want to know how to execute the search query for showing multiple infos in output as table. Also I want the user to see the output table in another page,not in same search page.
    Waiting for your reply.

  8. hye there, mind if i ask. do you think this can be applied in cakePHP. do you have any video that help me create websites using cakePHP. i really, really really need that. thank you.

  9. Sve i jedan ti je video uživancija za gledati. Svaka čast.
    Dal ćeš se držati samo PHP-a ili ćeš krenuti i na druge programske jezike?

  10. <?php

    if (isset($_POST['submit'])) {

    $connection = new mysqli(host: "localhost", username: "root", passwd: "", dbname: "phpSearch");

    }

    ?>
    Hi, the ':' next to to the word 'host' is giving me an error. I wonder why since yours is working @ 11:30 in your video. im using sublime text 3 to follow this tutorial. Can you help? Thanks man.

    this is the error im getting.

    Parse error: syntax error, unexpected ':', expecting ',' or ')' in C:xampphtdocsprojectstutorialsPHP Search Enginesearch.php on line 3

  11. Hi Senaid!
    in the Dutch language – as in French – we use many special characters such as: é, è, à, ç, ë. When reading and displaying my data, the pagination stops every time a record has to be shown that contains such a special character! How can I bypass this? I could replace the special characters with characters without accent, but then the spelling and pronunciation is wrong!
    Greetings, and thanks you already for all the examples I eagerly devour.

  12. I would advise you to join the Patreon community as a developer so peoples can cherish your hard work.

  13. Parse error: syntax error, unexpected ':' in C:xampphtdocsSeartest.php on line 5 can u please solve this..thanks anyways

  14. Hi @coding passive income I wanted to add a search bar to your comment section and I was only able to get where you can search the person's userID the comment show's up but i can not get the person's name to show up mainly because it is in the users table . Do you have any information on how I can go about searching the name and the comment displays?
    P.S your reply feature after you make a reply then reply to that reply keeps repeating the previous reply!

Leave a Reply

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