CRUD Application with PHP and MySQL

# CHAPTER 27

CRUD Application with PHP and MySQL #

1. Introduction #

2. Learning Objectives #

• Architect the folder structure for a PHP CRUD app.

• Implement the "Create" logic to insert new records.

• Implement the "Read" logic to fetch and display records in an HTML table.

• Implement the "Update" logic to modify existing data.

• Implement the "Delete" logic to remove records securely.

3. The Setup (database.php) #

<?php
$host = &#039;localhost';
$dbname = &#039;crud_app';
$username = &#039;root';
$password = &#039;';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

4. Create (Inserting Data) #

<?php
require &#039;database.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = trim($_POST[&#039;name']);
    $email = trim($_POST[&#039;email']);

    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([&#039;name' => $name, 'email' => $email]);
    
    echo "User created successfully!";
}
?>

<!-- HTML Form -->
<form method="POST" action="create.php">
    Name: <input type="text" name="name" required>
    Email: <input type="email" name="email" required>
    <button type="submit">Create User</button>
</form>

5. Read (Displaying Data) #

<?php
require &#039;database.php';

$sql = "SELECT id, name, email FROM users ORDER BY id DESC";
$stmt = $pdo->query($sql);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<table border="1">
    <tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>
    <?php foreach ($users as $user): ?>
    <tr>
        <td><?= htmlspecialchars($user[&#039;id']) ?></td>
        <td><?= htmlspecialchars($user[&#039;name']) ?></td>
        <td><?= htmlspecialchars($user[&#039;email']) ?></td>
        <td>
            <!-- Links to the Update and Delete pages! -->
            <a href="update.php?id=<?= $user[&#039;id'] ?>">Edit</a>
            <a href="delete.php?id=<?= $user[&#039;id'] ?>">Delete</a>
        </td>
    </tr>
    <?php endforeach; ?>
</table>

6. Update (Modifying Data) #

<?php
require &#039;database.php';
$id = $_GET[&#039;id']; // Get the ID from the URL

// 1. Fetch current data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([&#039;id' => $id]);
$user = $stmt->fetch();

// 2. Process form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $sql = "UPDATE users SET name = :name, email = :email WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        &#039;name' => $_POST['name'],
        &#039;email' => $_POST['email'],
        &#039;id' => $id
    ]);
    echo "User updated!";
}
?>

<!-- Pre-fill the form with existing data -->
<form method="POST">
    Name: <input type="text" name="name" value="<?= htmlspecialchars($user[&#039;name']) ?>">
    Email: <input type="email" name="email" value="<?= htmlspecialchars($user[&#039;email']) ?>">
    <button type="submit">Update</button>
</form>

7. Delete (Removing Data) #

<?php
require &#039;database.php';

// NEVER do this: $pdo->query("DELETE FROM users WHERE id = " . $_GET['id']);
// A hacker could pass ?id=1 OR 1=1 to delete everyone!

// ALWAYS do this:
if (isset($_GET[&#039;id'])) {
    $sql = "DELETE FROM users WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([&#039;id' => $_GET['id']]);
    
    echo "User securely deleted!";
    // Redirect back to the Read page
    header("Location: read.php");
}
?>

8. Common Mistakes #

• Trusting $_GET Variables: In the delete.php script, the ID comes from the URL ($_GET['id']). Beginners often concatenate this directly into the SQL string because "it's just a number." Hackers can easily type text or malicious SQL into the URL bar. ALWAYS use Prepared Statements for $_GET variables!

• Missing htmlspecialchars(): If a malicious user types <script>alert('Hacked!');</script> into the Name field on create.php, and you echo it out directly on read.php, the script will run in everyone's browser. htmlspecialchars() converts the HTML brackets into safe text entities, neutralizing the attack.

9. Best Practices #

• Post/Redirect/Get (PRG) Pattern: In create.php, if a user clicks "Refresh" after submitting the form, their browser will submit the data again, creating a duplicate row! To prevent this, after the $stmt->execute(), you should immediately redirect them away using header("Location: read.php");.

10. Exercises #

1. 1. What does the acronym CRUD stand for, and which fundamental SQL commands (INSERT, SELECT, UPDATE, DELETE) correspond to each letter?

1. 2. Why must you wrap database outputs in htmlspecialchars() before displaying them in an HTML table?

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Explain the Post/Redirect/Get (PRG) pattern and how it prevents duplicate row insertions in a standard PHP/MySQL CRUD application.

• Q: Walk me through the security vulnerabilities of taking an ID from the URL (delete.php?id=5) and concatenating it directly into a DELETE query string.

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #