PHP 4 - MYSQL and phpmyadmin

By Sheldon L

Published at 2020-06-23

Updated at 2020-06-23

Last Artical: PHP 3 - PHP Language Basics

Introduction to Databases

Create database and table:
- http://domain_address/phpmyadmin
- Create a new database phptutorial in Database tag;
- Create a new table users with 3 columns (id[int, primary, A_I], username[varchar, 255], password[varchar, 255]) in the database, and save;
- In Query:
```
DROP table users;

CREATE TABLE users (
  id int(11) not null PRIMARY KEY AUTO_INCREMENT,
  username varchar(255) not null,
  password varchar(255) not null
);
```
Insert data in table:
- Click Insert button in users table, fill username and password, id is AUTO_INCREMENT, so no need to fill.
- In Query:
```
INSERT INTO users (username, password) VALUES
('Dary03', 'mypassword'),
('Dary04', 'pass123456'),
```

Select data:

SELECT username, password FROM `users` WHERE username = 'Dary01' OR id = 2

Updating data:

UPDATE users
SET username = 'Dary99', password = 'newpass123'
WHERE id = 2

Delete data:

DELETE FROM users WHERE id = 2 OR username = 'Dary02'

Order by:

INSERT INTO users (username, password) VALUES
('Mike', 'mypassword'),
('John', 'pass123456'),
('Sheldon', 'mypassword'),
('Atom', 'pass123456')

SELECT * FROM `users`
ORDER BY id DESC

SELECT * FROM `users` WHERE id >= 5
ORDER BY username ASC

Conect to database

Create a newfolder project in PHP_Course

# project/includes/database.php

# Params to connect to a database
$dbHost = "localhost";
$dbUser = "root";
$dbPass = "";
$dbName = "phptutorial";

# connection - MySQLi Procedural
$conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);

if (!$conn) {
  die("Database connection failed!");
}

# project/index.php

<?php
require_once 'includes/database.php';
?>
  
<?php

$sql = "SELECT * FROM users WHERE id = 3";
$result = mysqli_query($conn, $sql);
$rowCount = mysqli_num_rows($result);

if ($rowCount > 0) {
  while ($row = mysqli_fetch_assoc($result)) {
    echo $row['username'] . " ". $row['password']. "<br>";
  }
} else {
  echo "No results found. <br>";
}

?>

Using a Database to Create a Login/Register Script

# includes/header.php

<?php
session_start();
require_once 'database.php';
require_once 'register-inc.php';
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Document</title>
  <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>

<header>
  <nav>
    <ul>
      <li><a href="index.php">Home</a></li>
      <li><a href="login.php">Login</a></li>
      <li><a href="register.php">Register</a></li>
    </ul>
  </nav>
</header>

# includes/footer.php

</body>
<footer>
  <p>This is my footer of login and register form</p>
</footer>
</html>

# includes/database.php

# Params to connect to a database
$dbHost = "localhost";
$dbUser = "root";
$dbPass = "";
$dbName = "phptutorial";

# connection - MySQLi Procedural
$conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);

if (! $conn) {
  die("Database connection failed!");
}

# index.php

<?php
require_once 'includes/header.php';
?>
<?php
if (isset($_SESSION['sessionId'])) {
  echo "Your are logged in";
} else {
  echo "HOME";
}
?>
<?php
require_once 'includes/footer.php';
?>

# register.php

<?php
require_once 'includes/header.php';
?>

<div>
  <h1>Register</h1>
  <p>Already have an account? <a href="login.php">Login!</a></p>

  <form action="includes/register-inc.php" method="post">
    <input type="text" name="username" placeholder="Username">
    <input type="password" name="password" placeholder="Password">
    <input type="password" name="confirmPass" placeholder="Conform password">
    <button type="submit" name="register">REGISTER</button>
  </form>
</div>

<?php
require_once 'includes/footer.php';
?>

# login.php

<?php
require_once 'includes/header.php';
?>

<div>
  <h1>Login</h1>
  <p>No account? <a href="register.php">Register here!</a></p>

  <form action="includes/login-inc.php" method="post">
    <input type="text" name="username" placeholder="Username">
    <input type="password" name="password" placeholder="Password">
    <button type="submit" name="login">LOGIN</button>
  </form>
</div>

<?php
require_once 'includes/footer.php';
?>

# includes/register-inc.php

if (isset($_POST['register'])) {
  # add database connection
  require 'database.php';

  $username = $_POST['username'];
  $password = $_POST['password'];
  $confirmPass = $_POST['confirmPass'];

  # error handle
  if (empty($username) || empty($password) || empty($confirmPass)) {
    header("Location: ../register.php?error=emptyfields&username=".$username);
    exit();
  } elseif (!preg_match("/^[a-zA-z0-9]*/", $username)) {
    header("Location: ../register.php?error=invalidusername&username=".$username);
    exit();
  } elseif($password !== $confirmPass) {
    header("Location: ../register.php?error=passworsdonotmatch&username=".$username);
    exit();
  }
  
  # username tooken?
  else {
    # PREPARED STATEMENTS
    $sql = "SELECT username FROM users WHERE username = ?";  # be careful, using `?` to prevent sql injection
                                                             # `?` will be bind to the placeholder value
    $stmt = mysqli_stmt_init($conn);

    # check the query statement error
    if (!mysqli_stmt_prepare($stmt, $sql)) {
      header("Location: ../register.php?error=sqlerror1");
      exit();  
    } else {
      mysqli_stmt_bind_param($stmt, "s", $username); # s-string, b-boolean, i-int; if multi fields, use such as "ss"
      mysqli_stmt_execute($stmt);
      mysqli_stmt_store_result($stmt);
      $rowCount = mysqli_stmt_num_rows($stmt);
      if ($rowCount > 0) {
        header("Location: ../register.php?error=usernametaken");
        exit();  
      }

      # store user to database
      else {
        $sql = "INSERT INTO users (username, password) VALUES (?, ?)";
        $stmt = mysqli_stmt_init($conn);
        if (!mysqli_stmt_prepare($stmt, $sql)) {
          header("Location: ../register.php?error=sqlerror2");
          exit();  
        } else {

          # hash the password
          $hashedPass = password_hash($password, PASSWORD_DEFAULT);

          mysqli_stmt_bind_param($stmt, "ss", $username, $hashedPass); # should be the same order as the query
          mysqli_stmt_execute($stmt);

          header("Location: ../register.php?success=registered");
          exit();
        }
      }
    }
  }
  mysqli_stmt_close($stmt);
  mysqli_close($conn);
}

# includes/login-inc.php

if (isset($_POST['login'])) {
  require 'database.php';

  $username = $_POST['username'];
  $password = $_POST['password'];

  if (empty($username) || empty($password)) {
    header("Location: ../login.php?error=emptyfields");
    exit();
  } else {
    $sql = "SELECT * FROM users WHERE username = ?";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
      header("Location: ../login.php?error=sqlerror1");
      exit();  
    } else {
      mysqli_stmt_bind_param($stmt, "s", $username);
      mysqli_stmt_execute($stmt);
      $result = mysqli_stmt_get_result($stmt);

      if ($row = mysqli_fetch_assoc($result)) {

        # compare password
        $passCheck = password_verify($password, $row['password']); # the password will be digested
        if ($passCheck == FALSE) {
          header("Location: ../login.php?error=wrongpass");
          exit();
        } elseif ($passCheck == TRUE) {

          # Create sessions if login success
          session_start();
          $_SESSION['sessionId'] = $row['id'];
          $_SESSION['sessionUser'] = $row['username'];

          # Redirect to homepage
          header("Location: ../index.php?success=loggedin");
          exit();

        } else {
          header("Location: ../login.php?error=wrongpass");
          exit();
        }
      } else {
        header("Location: ../login.php?error=nouser");
        exit();  
      }
    }
  }
} else {
  header("Location: ../login.php?error=accessforbiden");
  exit();
}