PHP-and-MySQL/C4/User-Login-Part-2/English-timed
From Script | Spoken-Tutorial
| Time | Narration |
| 00:00 | Welcome to the second part. Here I’ll show you how to edit your "login dot php" page to connect to the database and also how to check our username and password against that in the database. |
| 00:14 | Now we’ve already connected to our database. |
| 00:18 | Refreshing this and resending my username and password, we can establish that there have been no errors. |
| 00:24 | I mean this error here. And we have seen that if we don’t type data we will get an error. |
| 00:28 | Now, first of all I’m going to setup a query. |
| 00:36 | If you have used "mysql" or any structured query language before, you will know that you can query a database. |
| 00:43 | I think Microsoft Access has this. |
| 00:46 | So, here we are going to say, SELECT, in fact we’re going to say "SELECT *" because we need the ID, the username and the password. |
| 00:54 | I don’t think we need the id but "SELECT *" anyway so that it will grab all the data. |
| 00:59 | So "SELECT * FROM" and we’ve called this 'users', I think. Let me confirm that. |
| 01:04 | Yes, 'users'. So "SELECT * users" and here we’ll say "WHERE username" which is the name of this...... this, here. |
| 01:20 | And we will say "WHERE username equals" the "username" that has been typed. |
| 01:30 | Now, if that "username" doesn’t exist, we need to display some kind of an error message to say that "This user doesn’t exist". |
| 01:37 | So, what we will do is we will use another function, a 'mysql' function called "mysql_num_rows()". |
| 01:46 | This counts the number of rows that have been retrieved from the query you’ve given the database. |
| 01:53 | So we’ll say $numrows equals mysql_num_rows() and in the brackets we have the name of our query, the variable that I have stored the query function in. |
| 02.08 | And if we echo out the number of rows, I’ll just prove to you and test for myself that we should get 1 because we have just 1 row. |
| 02.16 | Let me click Insert and add another row of data, for example - another "username" and another "password". |
| 02:26 | I’ll just try that now. I’ll be testing this later on. Let's see, let's say 'username'is "Kyle" and 'password' is "123" this time. |
| 02:38 | Okay, let's try that. And there we are. Hold on a second. Where am I? Ah there we go. |
| 02:53 | So we have got "Alex" and "Kyle". |
| 02:55 | We can see the "ids" have been automatically incremented. |
| 02:58 | You can see both our passwords here and the 2 'usernames'. |
| 03:02 | Now we will refresh this and see what we get. |
| 03:06 | Oh Ok. This is the whole part of the check. |
| 03:10 | The reason this has returned 1 is that if I were just selecting every user and then counting the row, the value would increment. |
| 03:18 | Go back here and refresh and we’ll get the value of 2 because there are 2 rows. |
| 03:22 | But if I’m saying "SELECT where the username equals my username", we’re obviously only selecting where my username exists and that is in 1 row. |
| 03:34 | Usually on a website, you won’t have duplicate username. |
| 03:40 | Okay. So now that we have got this, what is the purpose of finding out how many rows there are. |
| 03:47 | Now the purpose is that we can say if "num_rows is equal to zero", then that means we can.. sorry, if "my num_rows doesn’t equal zero" then we can execute the code we need to do, that we will need to login. |
| 04:01 | Otherwise, sorry else, we need to echo out, sorry else die. We will give the message "That user doesn’t exist". |
| 04:16 | So, what we’re doing is, we’re checking that a row has been returned where we have supplied that username. |
| 04:25 | And if it doesn’t equal zero, we can execute our code to login. |
| 04:29 | Otherwise we’ll say die and "That username doesn’t exist". |
| 04:33 | This will be equal 1, 2, 3, 4 and so on. |
| 04:38 | Sorry it will equal... |
| 04:40 | If it doesn’t equal zero, then it must equal something. |
| 04:44 | And if it does equal something, then the code in here will be executed. |
| 04:47 | So, if it is equal to 0, that basically means that no result can be returned. |
| 04:52 | I’ll resend this. Let’s go back. |
| 04:57 | And.... let’s get rid of our "echo num_rows". |
| 05:05 | Okay. So let’s go back to our main page and we’ll login with "Alex" and "abc"; password doesn’t matter at the moment. |
| 05:13 | Nothing’s happened because no errors have been returned. |
| 05:15 | Now let me use Billy, for example, and type the password in and click on Login. |
| 05:21 | "That user doesn’t exist!" because no rows that have the 'username' equal to "Billy" have been returned. |
| 05:26 | So, we can see that that’s working. |
| 05:28 | I’ll go back to my original thing. |
| 05:31 | So "Alex" and my password is "abc". |
| 05:37 | Now the code to login. |
| 05:39 | In order to login, we need to perform a password check. |
| 05:42 | So, to grab the password, I’ll use a function. |
| 05:46 | Sorry not a function, I’ll use... a loop and that loop will be the while loop. |
| 05:52 | I will type a variable name here. I’ll call it $row and that is equal to "mysql"..... "mysql_ fetches a row as an array". Okay? |
| 06:11 | So, I’ll say "mysql_fetch_assoc" for short. |
| 06:22 | And this is going to be my query. So I’ve got my $query there. |
| 06:28 | From this, we are fetching each column data from here and putting it into an array called "$row". |
| 06:40 | So, obviously with the while loop, we’ll have our brackets and now we’ll set some variables. |
| 06:45 | I’ll say "$db username" which is the username that I’ll extract from the database, is equal to the "$row" and this is the row name, "username". |
| 06:55 | So, as we can see here, this is the row name here. |
| 06:59 | If this is an array of the data then each of these are going to be "id", "username" and "password". |
| 07:06 | We’re not using 0,1,2. But I’m not sure whether that works. |
| 07:10 | Now we’ll keep it simple and we’ll directly reference the name of our column. |
| 07:20 | So, the database username will be "row" and since this is an array which is using this function on our query. |
| 07:26 | Next we will say "$db password" equals "$row" and then our 'password'. |
| 07:38 | So, after this we could echo out.... |
| 07:43 | No, in fact we don’t need to echo out our contents of our 'db username' and 'password' unless we want to run into errors. |
| 07:49 | We already know what they are. We’ve seen them in the database. |
| 07:51 | Now what we’ll do is we will perform a check. So "check to see if they match". |
| 08:00 | It’s very simple to do this using an if statement. |
| 08:04 | if our $username equals our $db username AND our $password is equal to our $db password then we’ll say it’s correct. |
| 08:19 | Otherwise, we’ll say it’s incorrect. |
| 08:22 | I will remove the brackets because there is only one line. So echo "Incorrect password!". Just leave it like that. |
| 08:34 | And here we’ll say echo "You’re in!". |
| 08:41 | Okay, we’ll just test this before I end this part of the video. |
| 08:46 | I’ll first say "Alex" and I’ll put a wrong password in. "Incorrect password!". |
| 08:51 | And now I’ll put "abc" as password and "You’re in!". |
| 08:55 | So, we’ve checked our "username" and it exists. |
| 08:58 | We’ve checked our fields exist so please enter in your "username" and "password". |
| 09:04 | If we enter in a "username" and a wrong "password" we get an error message – "Incorrect password". |
| 09:11 | If we enter the correct password, we get- "You’re in". |
| 09:13 | And if we enter a "username" that is not found, we get an error message saying the "user doesn’t exist". |
| 09:24 | Okay, so join me in the next part and I’ll show you how to create your sessions and your log out page. See you then! |
| 09:32 | This is Antara, dubbing for the Spoken Tutorial project. |
