🔹 1. What Is a Database?

A database stores structured data that can be easily searched, updated, and managed.
MySQL is the most popular open-source relational database — and it pairs perfectly with PHP (the “M” in the LAMP stack).

You’ll use it to store:

• User profiles
• Posts or messages
• Product data
• Form submissions

🔹 2. Setting Up MySQL Locally

If you’re using LocalWP, XAMPP, or MAMP, you already have MySQL installed.

🔧 Accessing phpMyAdmin

• Visit: http://localhost/phpmyadmin
• Click New
• Name it streetgeek_db
• Click Create

Then, inside it, create a new table named tasks:

🔹 3. Connecting PHP to MySQL

You can connect using either the MySQLi extension or PDO.
We’ll start with MySQLi, which is simple and great for beginners.

<?php
$servername = "localhost";
$username   = "root";  // default for local
$password   = "";      // blank by default
$database   = "streetgeek_db";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "✅ Connected successfully!";
?>

✅ If you see “Connected successfully!”, your connection works.

🔹 4. Inserting Data into the Database

<?php
$conn = new mysqli("localhost", "root", "", "streetgeek_db");

$title  = "Learn PHP MySQL Integration";
$status = "pending";

$sql = "INSERT INTO tasks (title, status) VALUES ('$title', '$status')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

✅ Check phpMyAdmin → streetgeek_db → tasks — you’ll see your new row.

🔹 5. Reading Data from the Database

<?php
$conn = new mysqli("localhost", "root", "", "streetgeek_db");

$sql    = "SELECT * FROM tasks";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h2>Task List</h2>";
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " — " .
             $row["title"] . " (" . $row["status"] . ")<br>";
    }
} else {
    echo "No tasks found.";
}
$conn->close();
?>

✅ Displays all rows from your tasks table.

🔹 6. Updating Data

<?php
$conn = new mysqli("localhost", "root", "", "streetgeek_db");

$sql = "UPDATE tasks SET status='completed' WHERE id=1";
if ($conn->query($sql) === TRUE) {
    echo "Task updated successfully!";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

✅ Changes status of task with ID = 1 to completed.

🔹 7. Deleting Data

<?php
$conn = new mysqli("localhost", "root", "", "streetgeek_db");

$sql = "DELETE FROM tasks WHERE id=2";
if ($conn->query($sql) === TRUE) {
    echo "Task deleted successfully!";
} else {
    echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

✅ Deletes record with ID = 2.

🔹 8. Using Prepared Statements (Security Best Practice)

To protect your database from SQL Injection, always use prepared statements.

<?php
$conn = new mysqli("localhost", "root", "", "streetgeek_db");

$stmt = $conn->prepare("INSERT INTO tasks (title, status) VALUES (?, ?)");
$stmt->bind_param("ss", $title, $status);

$title  = "Complete PHP Module 9";
$status = "pending";
$stmt->execute();

echo "Data inserted safely!";

$stmt->close();
$conn->close();
?>

🧠 bind_param("ss", ...) uses:

• s = string
• i = integer
• d = double

🔹 9. Building a CRUD Interface

Now let’s connect everything with a form-driven mini-app.

Create task-manager.php:

<?php
$conn = new mysqli("localhost", "root", "", "streetgeek_db");

// Handle form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $title = htmlspecialchars($_POST["title"]);
    $stmt  = $conn->prepare("INSERT INTO tasks (title, status) VALUES (?, 'pending')");
    $stmt->bind_param("s", $title);
    $stmt->execute();
}

// Display tasks
$result = $conn->query("SELECT * FROM tasks");
?>
<h2>Task Manager</h2>

<form method="POST" action="">
  <input type="text" name="title" placeholder="New Task" required>
  <input type="submit" value="Add Task">
</form>

<hr>
<h3>Current Tasks:</h3>
<?php
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row["id"] . ". " . $row["title"] .
             " — " . $row["status"] . "<br>";
    }
} else {
    echo "No tasks yet.";
}
$conn->close();
?>

✅ You can add tasks from the form and instantly see them below.

🧾 Module 9 Quiz

💪 Challenge Task – Task List App (Full CRUD)

Objective: Build a fully functional Task Manager with create, view, update, and delete features.

Features:

• Add new tasks
• Mark tasks as completed
• Delete tasks
• Display all tasks in a styled HTML table

Steps:

• Create database streetgeek_db → table tasks (id, title, status).
• Create tasks-crud.php.
• Combine all the operations:

<?php
$conn = new mysqli("localhost", "root", "", "streetgeek_db");

// Create
if (isset($_POST["add"])) {
    $title = htmlspecialchars($_POST["title"]);
    $stmt  = $conn->prepare("INSERT INTO tasks (title, status) VALUES (?, 'pending')");
    $stmt->bind_param("s", $title);
    $stmt->execute();
}

// Update (mark completed)
if (isset($_GET["complete"])) {
    $id = (int) $_GET["complete"];
    $conn->query("UPDATE tasks SET status='completed' WHERE id=$id");
}

// Delete
if (isset($_GET["delete"])) {
    $id = (int) $_GET["delete"];
    $conn->query("DELETE FROM tasks WHERE id=$id");
}

$result = $conn->query("SELECT * FROM tasks");
?>
<h2>StreetGeek Task List</h2>

<form method="POST" action="">
  <input type="text" name="title" placeholder="New task" required>
  <input type="submit" name="add" value="Add Task">
</form>

<table border="1" cellpadding="8" cellspacing="0" style="margin-top:10px;">
  <tr><th>ID</th><th>Title</th><th>Status</th><th>Actions</th></tr>
  <?php while ($row = $result->fetch_assoc()): ?>
    <tr>
      <td><?php echo $row["id"]; ?></td>
      <td><?php echo htmlspecialchars($row["title"]); ?></td>
      <td><?php echo $row["status"]; ?></td>
      <td>
        <a href="?complete=<?php echo $row["id"]; ?>">✅ Complete</a> |
        <a href="?delete=<?php echo $row["id"]; ?>">🗑 Delete</a>
      </td>
    </tr>
  <?php endwhile; ?>
</table>
<?php $conn->close(); ?>

✅ You’ve now created your first full CRUD application using PHP + MySQL.

🧾 Submission Checklist

• ✅ insert-task.php → adds data correctly
• ✅ view-tasks.php → displays records
• ✅ update-task.php → updates successfully
• ✅ delete-task.php → removes records
• ✅ task-manager.php or tasks-crud.php → full CRUD interface
• ✅ Quiz completed

🏁 Next Step: In Module 10, you’ll go beyond data management to build user sessions and
authentication systems — learning how to keep users logged in, track activity, and create login/logout systems
just like professional sites and WordPress itself.