Building a CRUD Application with PHP and MySQLدروستکردنی بەرنامەیەکی CRUD بە PHP و MySQL

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mydb;

CREATE TABLE users (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(50)  NOT NULL,
  lastname  VARCHAR(50)  NOT NULL,
  email     VARCHAR(255) NOT NULL UNIQUE,
  password  VARCHAR(255) NOT NULL,   -- holds a password_hash() result, never the real password
  gender    ENUM('Male', 'Female') NOT NULL
);

$db_connect = mysqli_connect('localhost','root','','db');
if(!$db_connect){
    die("Connection Failed: ".mysqli_error($db_connect));
}

$query = "SELECT * FROM Table_Name";
$result = mysqli_query($db_connect, $query);


while ($row = mysqli_fetch_assoc($result)) {
echo $row['col1'];
echo $row['col2'];
echo $row['col{n}'];
}

href="update.php?id=<?php echo $row['id']; ?>
href="delete.php?id=<?php echo $row['id']; ?>

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydb";
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

<?php
// config.php - one shared database connection for every page (mysqli, object-oriented style)
$servername = "localhost";
$username   = "root";   // XAMPP default only; a real server needs its own low-privilege user
$password   = "";
$dbname     = "mydb";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // errors become exceptions (default since PHP 8.1)
try {
    $conn = new mysqli($servername, $username, $password, $dbname);
    $conn->set_charset("utf8mb4");          // needed for Kurdish (Arabic-script) text
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());            // the details go to the server's error log
    exit("Database connection failed.");    // the visitor sees only a short message
}

// e() = escape a value before printing it into HTML (used by every page)
function e($value): string {
    return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
}

if (isset($_POST['submit'])) {
  $first_name = $_POST['firstname'];
  $last_name = $_POST['lastname'];
  $email = $_POST['email'];
  $password = $_POST['password'];
  $gender = $_POST['gender'];
  $sql = "INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`, `gender`)
  VALUES ('$first_name','$last_name','$email','$password','$gender')";
  $result = $conn->query($sql);
  if ($result == TRUE) {
    echo "New record created successfully.";
  } else {
    echo "Error:" . $sql . "<br>" . $conn->error;
  }
  $conn->close();
}
?>

<!DOCTYPE html>
<html>
<body>
  <h2>Signup Form</h2>
  <form action="" method="POST">
    <fieldset>
      <legend>Personal information:</legend>
      First name:<br />
      <input type="text" name="firstname"><br />

      Last name:<br />
      <input type="text" name="lastname"><br />

      Email:<br />
      <input type="email" name="email"><br />

      Password:<br />
      <input type="password" name="password"><br />

      Gender:<br>
      <input type="radio" name="gender" value="Male">Male
      <input type="radio" name="gender" value="Female">Female
      <br /><br />
      <input type="submit" name="submit" value="submit">
    </fieldset>

  </form>
</body>
</html>

<?php
require "config.php";

$message = "";
if (isset($_POST['submit'])) {
    $first_name = trim($_POST['firstname'] ?? '');
    $last_name  = trim($_POST['lastname'] ?? '');
    $email      = trim($_POST['email'] ?? '');
    $password   = $_POST['password'] ?? '';
    $gender     = $_POST['gender'] ?? '';

    // 1. Validate on the server
    if ($first_name === '' || $last_name === '' || mb_strlen($password) < 8
        || filter_var($email, FILTER_VALIDATE_EMAIL) === false
        || !in_array($gender, ['Male', 'Female'], true)) {
        $message = "Please fill in every field correctly (password: at least 8 characters).";
    } else {
        // 2. Is the email address already used?
        $stmt = $conn->prepare("SELECT id FROM users WHERE email = ?");
        $stmt->bind_param("s", $email);
        $stmt->execute();
        if ($stmt->get_result()->num_rows > 0) {
            $message = "This email address is already registered.";
        } else {
            // 3. Insert with a prepared statement; store a hash, never the password
            $hash = password_hash($password, PASSWORD_DEFAULT);
            $stmt = $conn->prepare(
                "INSERT INTO users (firstname, lastname, email, password, gender) VALUES (?, ?, ?, ?, ?)"
            );
            $stmt->bind_param("sssss", $first_name, $last_name, $email, $hash, $gender);
            $stmt->execute();

            header("Location: view.php");   // Post/Redirect/Get
            exit;                           // stop here: nothing below may run
        }
    }
}
?>
<!DOCTYPE html>
<html>
<body>
  <h2>Signup Form</h2>
  <?php if ($message !== ""): ?>
    <p style="color: red;"><?= e($message) ?></p>
  <?php endif; ?>
  <form action="" method="POST">
    <fieldset>
      <legend>Personal information:</legend>
      <label for="firstname">First name:</label><br>
      <input type="text" id="firstname" name="firstname" value="<?= e($_POST['firstname'] ?? '') ?>" required><br>

      <label for="lastname">Last name:</label><br>
      <input type="text" id="lastname" name="lastname" value="<?= e($_POST['lastname'] ?? '') ?>" required><br>

      <label for="email">Email:</label><br>
      <input type="email" id="email" name="email" value="<?= e($_POST['email'] ?? '') ?>" required><br>

      <label for="password">Password:</label><br>
      <input type="password" id="password" name="password" minlength="8" required><br>

      Gender:<br>
      <label><input type="radio" name="gender" value="Male" <?= ($_POST['gender'] ?? '') === 'Male' ? 'checked' : '' ?> required>Male</label>
      <label><input type="radio" name="gender" value="Female" <?= ($_POST['gender'] ?? '') === 'Female' ? 'checked' : '' ?>>Female</label>
      <br><br>
      <input type="submit" name="submit" value="submit">
    </fieldset>
  </form>
  <p><a href="view.php">See all users</a></p>
</body>
</html>

<?php
include "config.php";
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
?>

<!DOCTYPE html>
<html>
<head>
  <title>View Page</title>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
</head>
<body>
  <div class="container">
    <h2>users</h2>
    <table class="table">
      <thead>
        <tr>
          <th>ID</th>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Email</th>
          <th>Gender</th>
          <th>Action</th>
        </tr>
      </thead>
      <tbody>

<?php
          if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
              ?>
              <tr>
                <td><?php echo $row['id']; ?></td>
                <td><?php echo $row['firstname']; ?></td>
                <td> <?php echo $row['lastname']; ?></td>
                <td> <?php echo $row['email']; ?> </td>
                <td><?php echo $row['gender']; ?></td>
                <td><a class="btn btn-info" href="update.php?id=<?php echo $row['id']; ?>">Edit</a>&nbsp;<a
                    class="btn btn-danger" href="delete.php?id=<?php echo $row['id']; ?>">Delete</a></td>
              </tr>
            <?php }}?>
      </tbody>
    </table>
  </div>
</body>
</html>

<?php
require "config.php";

// No user input in this query, so a plain query() is safe here
$result = $conn->query("SELECT id, firstname, lastname, email, gender FROM users ORDER BY id");
?>
<!DOCTYPE html>
<html>
<head>
  <title>View Page</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
</head>
<body>
  <div class="container">
    <h2>users</h2>
    <p><a class="btn btn-primary" href="create.php">Add a user</a></p>
    <table class="table">
      <thead>
        <tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th><th>Gender</th><th>Action</th></tr>
      </thead>
      <tbody>
      <?php if ($result->num_rows === 0): ?>
        <tr><td colspan="6">No users yet.</td></tr>
      <?php endif; ?>
      <?php while ($row = $result->fetch_assoc()): ?>
        <tr>
          <td><?= e($row['id']) ?></td>
          <td><?= e($row['firstname']) ?></td>
          <td><?= e($row['lastname']) ?></td>
          <td><?= e($row['email']) ?></td>
          <td><?= e($row['gender']) ?></td>
          <td>
            <a class="btn btn-info" href="update.php?id=<?= e($row['id']) ?>">Edit</a>
            <form action="delete.php" method="post" style="display:inline"
                  onsubmit="return confirm('Delete this user?');">
              <input type="hidden" name="id" value="<?= e($row['id']) ?>">
              <button type="submit" class="btn btn-danger">Delete</button>
            </form>
          </td>
        </tr>
      <?php endwhile; ?>
      </tbody>
    </table>
  </div>
</body>
</html>

<?php
include "config.php";

   if (isset($_POST['update'])) {
       $firstname = $_POST['firstname'];
       $user_id = $_POST['user_id'];
       $lastname = $_POST['lastname'];
       $email = $_POST['email'];
       $password = $_POST['password'];
       $gender = $_POST['gender'];
       $sql = "UPDATE `users` SET `firstname`='$firstname',`lastname`='$lastname',
               `email`='$email',`password`='$password',`gender`='$gender' WHERE `id`='$user_id'";
       $result = $conn->query($sql);

       if ($result == TRUE) {
           echo "Record updated successfully.";
       }else{
           echo "Error:" . $sql . "<br>" . $conn->error;
       }  }

if (isset($_GET['id'])) {
  $user_id = $_GET['id'];
  $sql = "SELECT * FROM `users` WHERE `id`='$user_id'";
  $result = $conn->query($sql);


  if ($result->num_rows > 0) {
     while ($row = $result->fetch_assoc()) {
       $first_name = $row['firstname'];
       $lastname = $row['lastname'];
       $email = $row['email'];
       $password  = $row['password'];
       $gender = $row['gender'];
       $id = $row['id'];
}?>

<h2>User Update Form</h2>
    <form action="" method="post">
     <fieldset>
      <legend>Personal information:</legend>

      First name:<br>
      <input type="text" name="firstname" value="<?php echo $first_name; ?>">

      <input type="hidden" name="user_id" value="<?php echo $id; ?>"><br />

      Last name:<br />
      <input type="text" name="lastname" value="<?php echo $lastname; ?>"><br />

      Email:<br />
      <input type="email" name="email" value="<?php echo $email; ?>"><br />

      Password:<br />
      <input type="password" name="password" value="<?php echo $password; ?>"><br />

Gender:<br />
      <input type="radio" name="gender" value="Male" <?php if($gender == 'Male')
      { echo "checked";} ?> >Male
      <input type="radio" name="gender" value="Female" <?php if($gender == 'Female')
      { echo "checked";} ?>>Female
      <br /><br />
      <input type="submit" value="Update" name="update">


     </fieldset>
    </form>
    </body>
    </html>
 <?php
 } else{
    header('Location: view.php');
 }}?>

<?php
require "config.php";

$message = "";
// Part 1: the form was submitted (POST) - save the changes
if (isset($_POST['update'])) {
    $user_id   = filter_var($_POST['user_id'] ?? null, FILTER_VALIDATE_INT);
    $firstname = trim($_POST['firstname'] ?? '');
    $lastname  = trim($_POST['lastname'] ?? '');
    $email     = trim($_POST['email'] ?? '');
    $gender    = $_POST['gender'] ?? '';
    $password  = $_POST['password'] ?? '';

    if ($user_id === false || $user_id === null || $firstname === '' || $lastname === ''
        || filter_var($email, FILTER_VALIDATE_EMAIL) === false
        || !in_array($gender, ['Male', 'Female'], true)
        || ($password !== '' && mb_strlen($password) < 8)) {
        $message = "Please fill in every field correctly.";
    } else {
        $stmt = $conn->prepare("UPDATE users SET firstname = ?, lastname = ?, email = ?, gender = ? WHERE id = ?");
        $stmt->bind_param("ssssi", $firstname, $lastname, $email, $gender, $user_id);
        $stmt->execute();

        if ($password !== '') {                 // change the password only when a new one was typed
            $hash = password_hash($password, PASSWORD_DEFAULT);
            $stmt = $conn->prepare("UPDATE users SET password = ? WHERE id = ?");
            $stmt->bind_param("si", $hash, $user_id);
            $stmt->execute();
        }
        header("Location: view.php");           // Post/Redirect/Get
        exit;
    }
}

// Part 2: opened from the Edit link (update.php?id=N) - load that user
$user_id = filter_var($_GET['id'] ?? null, FILTER_VALIDATE_INT);
if ($user_id === false || $user_id === null) {
    header("Location: view.php");
    exit;
}
$stmt = $conn->prepare("SELECT id, firstname, lastname, email, gender FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$user = $stmt->get_result()->fetch_assoc();     // one row, or null if the id does not exist
if ($user === null) {
    header("Location: view.php");
    exit;
}
?>
<!DOCTYPE html>
<html>
<body>
  <h2>User Update Form</h2>
  <?php if ($message !== ""): ?>
    <p style="color: red;"><?= e($message) ?></p>
  <?php endif; ?>
  <form action="" method="post">
    <fieldset>
      <legend>Personal information:</legend>
      <input type="hidden" name="user_id" value="<?= e($user['id']) ?>">
      First name:<br>
      <input type="text" name="firstname" value="<?= e($user['firstname']) ?>" required><br>
      Last name:<br>
      <input type="text" name="lastname" value="<?= e($user['lastname']) ?>" required><br>
      Email:<br>
      <input type="email" name="email" value="<?= e($user['email']) ?>" required><br>
      New password (leave empty to keep the old one):<br>
      <input type="password" name="password" value=""><br>
      Gender:<br>
      <input type="radio" name="gender" value="Male" <?= $user['gender'] === 'Male' ? 'checked' : '' ?>>Male
      <input type="radio" name="gender" value="Female" <?= $user['gender'] === 'Female' ? 'checked' : '' ?>>Female
      <br><br>
      <input type="submit" value="Update" name="update">
    </fieldset>
  </form>
</body>
</html>

<?php
include "config.php";

if (isset($_GET['id'])) {

    $user_id = $_GET['id'];
    $sql = "DELETE FROM `users` WHERE `id`='$user_id'";
    $result = $conn->query($sql);

    if ($result == TRUE) {

        echo "Record deleted successfully.";
    } else {
        echo "Error:" . $sql . "<br>" . $conn->error;
    }
}
?>

include 'connection.php';
if (isset($_GET['id']))
{
        $user_id = $_GET['id'];
        $sql = "delete from users where id='$user_id'";
        $result = $con->query($sql);
        if ($result == TRUE)
        {
                 echo "Record deleted successfully.";
        }
        else
        {
                 echo "Error: ".$sql."<br>".$con->connect_error;
        }
}

<?php
require "config.php";

// Deleting changes data, so accept it only from a POST form - never from a plain link
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $user_id = filter_var($_POST['id'] ?? null, FILTER_VALIDATE_INT);
    if ($user_id !== false && $user_id !== null) {
        $stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
    }
}
header("Location: view.php");
exit;

<?php
// config.php - PDO version (works with MySQL; change the DSN for other databases)
$dsn = "mysql:host=localhost;dbname=mydb;charset=utf8mb4";
try {
    $pdo = new PDO($dsn, "root", "", [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // the default since PHP 8.0
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // rows come back as associative arrays
        PDO::ATTR_EMULATE_PREPARES   => false,                   // real prepared statements in MySQL
    ]);
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit("Database connection failed.");
}

<?php
// crud.php - the four CRUD operations as functions (PDO)

function create_user(PDO $pdo, string $first, string $last, string $email, string $password, string $gender): int {
    $stmt = $pdo->prepare("INSERT INTO users (firstname, lastname, email, password, gender) VALUES (?, ?, ?, ?, ?)");
    $stmt->execute([$first, $last, $email, password_hash($password, PASSWORD_DEFAULT), $gender]);
    return (int) $pdo->lastInsertId();             // the id the database gave the new row
}

function all_users(PDO $pdo): array {
    return $pdo->query("SELECT id, firstname, lastname, email, gender FROM users ORDER BY id")->fetchAll();
}

function find_user(PDO $pdo, int $id): array|false {
    $stmt = $pdo->prepare("SELECT id, firstname, lastname, email, gender FROM users WHERE id = :id");
    $stmt->execute(['id' => $id]);
    return $stmt->fetch();                         // false when no row matches
}

function update_user(PDO $pdo, int $id, string $first, string $last, string $email, string $gender): int {
    $stmt = $pdo->prepare("UPDATE users SET firstname = ?, lastname = ?, email = ?, gender = ? WHERE id = ?");
    $stmt->execute([$first, $last, $email, $gender, $id]);
    return $stmt->rowCount();                      // number of rows changed
}

function delete_user(PDO $pdo, int $id): int {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->rowCount();                      // number of rows deleted
}

<?php
// Try the same functions on SQLite: only the connection line is different
$pdo = new PDO("sqlite::memory:", null, null, [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, firstname TEXT NOT NULL, lastname TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE, password TEXT NOT NULL, gender TEXT NOT NULL)");
require "crud.php";

$a = create_user($pdo, "Sara", "O'Brien", "sara@example.com", "sara-pass-1", "Female");
$b = create_user($pdo, "دیلان", "کەریم", "dilan@example.com", "dilan-pass-1", "Male");
echo "Created ids: $a, $b\n";

foreach (all_users($pdo) as $u) {
    echo $u['id'], ": ", $u['firstname'], " ", $u['lastname'], "\n";
}
echo "Updated rows: ", update_user($pdo, $b, "Dilan", "Karim", "dilan@example.com", "Male"), "\n";
print_r(find_user($pdo, $b));
var_dump(find_user($pdo, 99));
echo "Deleted rows: ", delete_user($pdo, $a), "\n";
echo "Users left: ", count(all_users($pdo)), "\n";

<?php
session_start();                         // the very first line of the page
if (!isset($_SESSION['userId'])) {
    header('Location: login.php');
    exit;                                // without exit, the rest of the page is still sent
}

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE notes (id INTEGER PRIMARY KEY, owner_id INTEGER, text TEXT)");
$pdo->exec("INSERT INTO notes (owner_id, text) VALUES (1, 'Aram''s note'), (2, 'Hana''s note')");

function delete_note(PDO $pdo, int $note_id, int $current_user_id): int {
    // the extra condition "AND owner_id = ?" means: only your OWN notes
    $stmt = $pdo->prepare("DELETE FROM notes WHERE id = ? AND owner_id = ?");
    $stmt->execute([$note_id, $current_user_id]);
    return $stmt->rowCount();
}

echo "Hana (user 2) deletes note 1: ", delete_note($pdo, 1, 2), " row(s)\n";
echo "Aram (user 1) deletes note 1: ", delete_note($pdo, 1, 1), " row(s)\n";