PHP MySQL Login System

PHP MySQL Login System

Building the register system:
Step1: creating the database table
CREATE TABLE users ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );

INT: Stores numeric values in the range of -2147483648 to 2147483647
NOT NULL: constraint the field cannot accept a NULL value.
PRIMARY KEY: constraint; marks the corresponding field as the table's primary key.
AUTO_INCREMENT: a MySQL extension to standard SQL, which tells MySQL to automatically assign a value to this field if it is left unspecified, by incrementing the previous value by 1. Only available for numeric field.
VARCHAR():Stores variable-length strings with a maximum size of 65,535 characters.
N:CHAR():Stores fixed-length strings with a maximum size of 255 characters.
UNIQUE: ensures that each row for a column must have a unique value.
DATETIME: Stores combined date/time values in the YYYY-MM-DD HH:MM:SS format.
CURRENT_TIMESTAMP: Stores timestamp values. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:01' UTC).


Create the Config File to connect the MySQL database server.

<?php /* Database credentials. Assuming you are running MySQL server with default setting (user 'root' with no password) */ define('DB_SERVER', 'localhost'); define('DB_USERNAME', 'root'); define('DB_PASSWORD', '123456'); define('DB_NAME', 'demodb'); /* Attempt to connect to MySQL database */ $link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME); // Check connection if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } ?>

Create the Registration Form.
<?php // Include config file require_once "config.php"; // Define variables and initialize with empty values $username = $password = $confirm_password = ""; $username_err = $password_err = $confirm_password_err = ""; // Processing form data when form is submitted if($_SERVER["REQUEST_METHOD"] == "POST"){ // Validate username if(empty(trim($_POST["username"]))){ $username_err = "Please enter a username."; } else{ // Prepare a select statement $sql = "SELECT id FROM users WHERE username = ?"; if($stmt = mysqli_prepare($link, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "s", $param_username); // Set parameters $param_username = trim($_POST["username"]); // Attempt to execute the prepared statement if(mysqli_stmt_execute($stmt)){ /* store result */ mysqli_stmt_store_result($stmt); if(mysqli_stmt_num_rows($stmt) == 1){ $username_err = "This username is already taken."; } else{ $username = trim($_POST["username"]); } } else{ echo "Oops! Something went wrong. Please try again later."; } } // Close statement mysqli_stmt_close($stmt); } // Validate password if(empty(trim($_POST["password"]))){ $password_err = "Please enter a password."; } elseif(strlen(trim($_POST["password"])) < 6){ $password_err = "Password must have atleast 6 characters."; } else{ $password = trim($_POST["password"]); } // Validate confirm password if(empty(trim($_POST["confirm_password"]))){ $confirm_password_err = "Please confirm password."; } else{ $confirm_password = trim($_POST["confirm_password"]); if(empty($password_err) && ($password != $confirm_password)){ $confirm_password_err = "Password did not match."; } } // Check input errors before inserting in database if(empty($username_err) && empty($password_err) && empty($confirm_password_err)){ // Prepare an insert statement $sql = "INSERT INTO users (username, password) VALUES (?, ?)"; if($stmt = mysqli_prepare($link, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "ss", $param_username, $param_password); // Set parameters $param_username = $username; $param_password = password_hash($password, PASSWORD_DEFAULT); // Creates a password hash // Attempt to execute the prepared statement if(mysqli_stmt_execute($stmt)){ // Redirect to login page header("location: login.php"); } else{ echo "Something went wrong. Please try again later."; } } // Close statement mysqli_stmt_close($stmt); } // Close connection mysqli_close($link); } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Sign Up</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css"> <style type="text/css"> body{ font: 14px sans-serif; } .wrapper{ width: 350px; padding: 20px; } </style> </head> <body> <div class="wrapper"> <h2>Sign Up</h2> <p>Please fill this form to create an account.</p> <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post"> <div class="form-group <?php echo (!empty($username_err)) ? 'has-error' : ''; ?>"> <label>Username</label> <input type="text" name="username" class="form-control" value="<?php echo $username; ?>"> <span class="help-block"><?php echo $username_err; ?></span> </div> <div class="form-group <?php echo (!empty($password_err)) ? 'has-error' : ''; ?>"> <label>Password</label> <input type="password" name="password" class="form-control" value="<?php echo $password; ?>"> <span class="help-block"><?php echo $password_err; ?></span> </div> <div class="form-group <?php echo (!empty($confirm_password_err)) ? 'has-error' : ''; ?>"> <label>Confirm Password</label> <input type="password" name="confirm_password" class="form-control" value="<?php echo $confirm_password; ?>"> <span class="help-block"><?php echo $confirm_password_err; ?></span> </div> <div class="form-group"> <input type="submit" class="btn btn-primary" value="Submit"> <input type="reset" class="btn btn-default" value="Reset"> </div> <p>Already have an account? <a href="login.php">Login here</a>.</p> </form> </div> </body> </html>