Project Management: Authentication - Create the Table

(Page 2 of 4 )

So create a table with the following SQL:

CREATE TABLE `users` (

`uid` int(11) NOT NULL auto_increment,

`name` varchar(20) NOT NULL default '',

`sname` varchar(20) NOT NULL default '',

`uname` varchar(100) NOT NULL default '',

`upass` varchar(8) NOT NULL default '',

`level` enum('admin','normal') NOT NULL default 'normal',

`last_login` datetime NOT NULL default '0000-00-00 00:00:00',

`email` varchar(100) NOT NULL default '',

PRIMARY KEY (`uid`)

) TYPE=MyISAM AUTO_INCREMENT=5 ;

Below is some sample data for the table:

INSERT INTO `users` VALUES (1, 'jack', 'dee', 'jack.dee', 'pass', 'admin', '0000-00-00 00:00:00', 'jack@dee.com');

INSERT INTO `users` VALUES (2, 'maria', 'garises', 'maria.garises', 'pass', 'normal', '0000-00-00 00:00:00', 'maria@garises.com');

INSERT INTO `users` VALUES (3, 'kine', 'brand', 'kine.brand', 'pass', 'normal', '0000-00-00 00:00:00', 'kine@brand.com');

INSERT INTO `users` VALUES (4, 'john', 'doe', 'john.doe', 'pass', 'normal', '0000-00-00 00:00:00', 'john@doe.com');

Copy and paste the above SQL in your MySQL administration application and run it. You should have a table called "users" with the sample data above. Now, let's create the login script that will run the login process for us. Create a new PHP document and add the following code:

include "dbcon.php";

include "functions.php";

//initialise variables

$err=false;

$errmsg="";

//is form submitted?

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

//check that the form values are not empty, if so, set errormsg value

if(empty($_POST['uname'])){

$errmsg="The username field is empty, please enter a username
";

$err=true;

}

if(empty($_POST['upass'])){

$err=true;

$errmsg .="The password field is empty, please enter password
";

}

//check that the username is in correct format

if(!checkformat($_POST['uname'])){

$err=true;

$errmsg .="The username that you entered has a incorrect format.
";

}

//if there is no errors above, then clean the form values before using in query.

if(!$err){

$cleanuname = mysql_escape_string($_POST['uname']);

$cleanupass = mysql_escape_string($_POST['upass']);

$checkuser = "SELECT * from users WHERE uname = '".$cleanuname."' AND upass = '".$cleanupass."'";

$checkuser_res = mysql_query($checkuser);

$checkuser_num = mysql_num_rows($checkuser_res);

if($checkuser_num > 0){

//if user exists and passes authentication

//setup session variables and redirect to index page

$row = mysql_fetch_assoc($checkuser_res);

$_SESSION['name'] = $row['name']." ".$row['sname'];

$_SESSION['uid'] = $row['uid'];

$_SESSION['level'] = $row['level'];

//redirect

header("location:main.php");

}else{

//if values do not match set errmsg

$err=true;

$errmsg .="The username or password you entered does not match.
MYSQL ERROR ".mysql_error();

}//else

}//end $err check

} //end form submit check

?>

Project Management:: User Authentication

} ?> Login Username: Password: Forgot your password?

copyright © 2007 PM