PHP Tutorials: How to filter data from a MySQL Database Table with PHP

hello everyone this is Andrea and today I'm gonna show you how to filter data from a MySQL database table so - for the purpose of this tutorial I've created this database called test DB inside this database I've created this table called members and I've populated this table with some data or better some names random names with some random administration level so as you can see here this table is composed by three fields once call ID name the other name and level let's quickly see the structure of this table so that it's tractor here as you can see these are the fields the first fill this coat ID is integer Auto increment so this means that every time my record is inserted this value here without a increment by one these are the two our marker as you can see here let me browse and these are the data now what we will do in PHP it we will display all of this these records here in PHP and then we will create a form to filter the data here like for example to see how many people are called James or if this James is admin or a user stuff like this so let's let's open our editor I have already created a project called filter data now the first thing we are gonna do is we're gonna create a new file with the PHP extension and inside this file then I'm going to save with the name connection [Applause] we're gonna store our code to connect to the database m2 select sorry to connect the server indiscernible database we will need this file later in this tutorial every time we will need to access the database and to access our server so [Applause] this week live Hakeem back you see my super my server is local host cities local my user is good my password is blank just don't be sure find errors select it be so the database you remember it's called test DB here I'm gonna bro this file to see if I typed anything wrong if I've made errors tutorials fill the data connection I don't see any error so I think that everything's fine I'm gonna close this file connection I'm gonna create another file I'm going to call this file so first of all I'm gonna assign a PHP extension to this file I'm gonna save and I'm gonna call this file display data in this file we're gonna create a table and some rows [Applause] I'm gonna send this table moves itself 70% self adding 5 5 in our self space so if I'm gonna use this table always title so I'm gonna sign titles ID top it face name is Lam now let's quickly browse this file data as you can see here these are the labels that match this fields here you'll see the fields and the levels are the same next we're going to need to connect to our database in order to retrieve this information that is East or the distort in this table here so to do that we need to include this file connection that PHP if you remember connection that PHP displayed then we're gonna create a variable which I will call SQL in this variable we store the sequel single string that will will select everything from the stars this star here means everything from members close here next we need to query this sequel swing single single [Applause] let's check it's very good practice to always check your code for errors it's time you write a new PHP file error code so I don't have any error like query seems to be running fine well then down here I need to loop the records that I have stored here in this variable query to do that I'm gonna write a while loop write row secret the secret catch me or a socket they work with the same period then we need to close the while loop now since the while loop all what we will display is the same with these levels so I'm gonna copy this here paste down there down here so and then I'm gonna echo out everything something like PHP echo well I key [Applause] so now this always honoré this is why we're using the square brackets here level let us check if everything's going fine okay nicer than signal here everything is going as it should that it is displaying below this level I deal with the name also and the level also now we need to make this levels stand out from all of this these other information then I'm gonna make them strong [Applause] [Applause] it's not a visa bulb if you preview now in browser refresh you see that everything these labels now are bold or are blacker than this other information that is founded below this land so now we need to but we will do next we need to achieve this result where for example field name is equal to let's say our let me save this right load and as you can see we are now here seeing only this record here this row where the name is Aaron but we would like to do this dynamically like for example here instead of this static name we need to like for example and mainly it was able to name assigned a variable and then query so this variable this time that this variable is changed we will make this query select another row matching this name here this variable name here so let's quickly do that to do that we need to create a form and we go [Applause] search [Applause] search put type text sucks there's nothing and then next I'm gonna create an English type submit' suddenly name signal search [Applause] tell you the truth let's quickly preview this code through the browser yes for the moment this search table doesn't do anything it therefore it's default action is get action which means that we will see with a search string here in the address bar as you can see now what we gonna do is well let me change this name of the form here to from search to search form and action [Applause] now by default if we press this button here search the table you will know that the default action is the same file but just to be sure of a scientist it even in the action so let's refresh here sorry get rid of this search okay sorry i mistyped this let me refresh such table now we have a post but no results now next we're going to check if a search was submitted so if somebody wanted to search this page let's say let's create this statement it is sent both since now this map is it false this input field search so if someone press this button search search then you're going to start this we're going to create another variable called name or search term is equal to close with name search box based what we've done we've checked if somebody press this button submit and then from all of the posts that we have gotten from this forum we selected only this search box because we are only interested in what's inside this search box the value of this search box okay now it is very good practice to present any SQL injection to always escape this values posted so I'm gonna do my secret real escape string copy this post a rather cut and fast paste it inside here [Applause] the next thing to do is we're gonna extend this sequel variable so this ring here we're gonna extend this string to look like we're main equals to right because we need to query this data now if we press the search to display only where the name is equal to the field name is equal to this search term now what I'm gonna do is I'm gonna cut this part here cut and I'm gonna do sequel then a dot and equal this is how we concatenate the string with the same name we've concatenate the string in PHP is with the same name and then followed by a dot and then by a equal sign here [Applause] now hopefully we have done everything right now I'm gonna test this page for errors let me refresh reload sorry display data now let me make a search like James as you can see now when I have this James Harold now we're displaying on is this very good but how about we wanted to search not only for the name but you know further level now if you want to do that to search for example not just for the name bar but even for the level with the same field be able to do something like this simply concatenate again is equal to or and equals two search term which means this means to search where name is equal to this where field name is equal to the search term or world level where the level is equal to system so it doesn't matter which of these two fields is equal to this search the query will be executed whenever this search term is found in either of the fields in name and level let me check this sorry we have an error an error line [Applause] huh I should not be in his face here let me save try again reload percent okay now let's say admin okay we can see that we have three admins and their names are James Aaron and Nina pallet ship guest you don't have any guests that we have using as you can see this era is user so hopefully this is a useful tutorial for you you if you want to Matic extend it further you might use wildcards like for example this percentage sign here to search only for one particular character you know the name field you can experiment that by yourself this is all I had to do for this tutorial so let's retry again let's say Era and we have records here admin we have three admins here you can do you can further do if you like number of row founds or if we found anything or we didn't find everything you can extend this further so thank you for watching and

Channel: TheEndritv

Views: 115,140

Keywords: mysql, php, tutorial, Software

Id: 9J3S60VRYlE

Length: 19min 37sec (1177 seconds)

Published: Wed Dec 19 2012