07/13/00 "Using a page by page password
system using mySQL" 
[next]
You can do authentication on a page by page basis
One cool thing about php is that you can fairly easily validate users before displaying content on a page by page basis. Let's say you don't want to restrict the entire directory using htaccess, this is a neat alternative. Let's create a login system using PHP and mySQL. First we need to create the table that holds our user data. We're going to have 3 fields, logged, which will keep track of the last time a person logged in to the page, user and pass which will be the username and password respectively. Using Telnet you can simply cut and paste this into your mySQL monitor. If you need help with how to log into the mySQL monitor then see the diary entry on connecting to mySQL.
CREATE TABLE login
(
L_ID INT NOT NULL AUTO_INCREMENT,
logged DATETIME,
user VARCHAR(10),
pass VARCHAR(10),
PRIMARY KEY (L_ID)
);
Next we need a way to insert the new users into this table. We can do that in telnet by utilyzing the following SQL command:
INSERT into login VALUES( 0, SYSDATE(), 'username', 'password' );
The zero increments the L_ID which is a way to refer to each row in the data without having to view to compare the other fields for a match. SYSDATE() is a built-in SQL function to insert the current server date and time in the format 2000-07-12 11:24:00. We can turn this SQL query into a PHP script by making the above code into a query and using the php function mysql_query. Here would be the code to do the above in our PHP script.
<script language="php">
$query = "INSERT into login VALUES ( ";
$query .= "0, SYSDATE(), '$username', '$password' )";
mysql_query($query, $mysql_link);
</script>
Now we need a way to actually check and see if a username/password matches a record in the database. The SQL query you can type directly into telnet is as follows:
SELECT user, pass FROM login WHERE user='$username' AND pass='$password';
Again, we can easily convert this into a query in PHP as follows:
<script language="php">
$query = "SELECT user, pass FROM login ";
$query .= "WHERE user='$username' AND pass='$password'";
$result = mysql_query($query, $mysql_link);
if(mysql_num_rows($result)) {
// we have at least one result, so let them in
} else {
print("Sorry, this login is invalid.");
exit;
}
</script>
Now let's create the code to put in the top of php enabled page before the <HTML> tag to actually authenticate the user. If the person comes to the page and there is no $username and $password, we'll show them a login box. If the $username and $password variables are assigned then we'll try to validate the user and if there isn't a match we'll pop the invalid login message and exit the script before showing the content. This may seem like a lot of code, but actually it is only a few more lines to the one above. Check it out:
<script language="php">
$mysql_link = mysql_connect("localhost",
"mysql_username", "mysql_password");
mysql_select_db($db, $mysql_link);
if (($username) AND ($password)) {
// you should inspect these variables before passing off to mySQL
$query = "SELECT user, pass FROM login ";
$query .= "WHERE user='$username' AND pass='$password'";
$result = mysql_query($query, $mysql_link);
if(mysql_num_rows($result)) {
// we have at least one result, so update the
logged in datetime
$query = "UPDATE from login SET
logged=SYSDATE()";
$query .= "WHERE user='$username'
AND pass='$password' ";
mysql_query($query,$mysql_link);
} else {
print("Sorry, this login is invalid.");
exit;
}
} else {
print ("<form action=\"$PHP_SELF\"
method=\"POST\">");
print ("User: <input type="text"
name="username">")
print ("Pass: <input type="password"
name="password">");
print (" <input type="submit"
value="Submit"></form>");
}
</script>
<HTML>
<BODY>
You may notice the use of the $PHP_SELF variable in the generated login which will call the same page. This is useful so you wouldn't have to alter every page you put the code above on. Also if we do have a successful login then we update the logged time in the mySQL table. In my next diary entry we'll take this a step further and we'll build an admin interface to add and delete users to this page by page password system for validating users.
Please vote on the usefulness of this diary entry so other people will know if it is worth their time to read :)
[back] 07/13/00 "Using a page by page password
system using mySQL" [next]
PHP Diary | scriptschool.com | PHP Scripts | TD Scripts.com
Copyright 2000 php-scripts.com Last Modified 07/30/00 06:44