Step 1: Introduction to Databases

A database is a structured collection of information. In web development, MySQL is one of the most widely used relational database management systems (RDBMS). Key concepts:

1. Database → a container for data. Example: company_db

2. Table → stores specific types of data. Example: employees

3. Row (Record) → a single entry in a table

4. Column (Field) → attributes of the record. Example: name, email, salary

In relational databases, data is organized systematically and can be queried efficiently.

---

Step 2: Install MySQL and PHP

Before we begin coding:

1. Install XAMPP, WAMP, or MAMP (includes Apache + PHP + MySQL).

2. Start Apache and MySQL from your control panel.

3. Open phpMyAdmin to manage databases graphically, or use the terminal.

---

Step 3: Establish a PHP-MySQL Connection

Create a file named db_connect.php:

<?php
$servername = "localhost";
$username = "root"; // default for local server
$password = ""; // default for local server
$dbname = "company_db";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

// Create database if it does not exist
$sql = "CREATE DATABASE IF NOT EXISTS $dbname";
if ($conn->query($sql) === TRUE) {
    echo "Database '$dbname' is ready.";
} else {
    echo "Error creating database: " . $conn->error;
}

// Select the database
$conn->select_db($dbname);
?>

Explanation:

• mysqli → PHP extension for MySQL

• connect_error → checks if the connection fails

• CREATE DATABASE IF NOT EXISTS → ensures idempotency

---

Step 4: Create a Table

Now, create a table to store employees:

$sql = "CREATE TABLE IF NOT EXISTS employees (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    position VARCHAR(50),
    salary DECIMAL(10,2)
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'employees' created successfully.";
} else {
    echo "Error creating table: " . $conn->error;
}

Explanation:

• INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY → unique identifier for each employee

• VARCHAR(50) → text fields with max 50 characters

• DECIMAL(10,2) → numerical field for salaries with 2 decimal places

• NOT NULL → ensures that field must have a value

---

Step 5: Insert Records

Add some employees:

$sql = "INSERT INTO employees (name, email, position, salary) VALUES
('Alice Smith', 'alice@example.com', 'Developer', 5000.00),
('Bob Johnson', 'bob@example.com', 'Designer', 4500.00)";

if ($conn->query($sql) === TRUE) {
    echo "Records added successfully.";
} else {
    echo "Error inserting records: " . $conn->error;
}

Explanation:

• Each row represents a single employee record

• Use commas to insert multiple records at once

---

Step 6: Read Records

Retrieve all employees:

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

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: ".$row["id"]." - Name: ".$row["name"]." - Email: ".$row["email"]." - Position: ".$row["position"]." - Salary: ".$row["salary"]."<br>";
    }
} else {
    echo "No records found.";
}

Explanation:

• $result->num_rows → number of rows returned

• $row = $result->fetch_assoc() → fetch a single record as an associative array

---

Step 7: Update Records

Modify an employee’s salary:

$sql = "UPDATE employees SET salary=5500.00 WHERE name='Alice Smith'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully.";
} else {
    echo "Error updating record: " . $conn->error;
}

Explanation:

• UPDATE table SET column=value WHERE condition → modifies existing data

• Always include WHERE to prevent updating all rows unintentionally

---

Step 8: Delete Records

Remove an employee:

$sql = "DELETE FROM employees WHERE name='Bob Johnson'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully.";
} else {
    echo "Error deleting record: " . $conn->error;
}

Explanation:

• DELETE FROM table WHERE condition → removes specific records

• Always double-check your condition to avoid accidental data loss

---

Step 9: Close Connection

After all operations:

$conn->close();
echo "Connection closed.";

Explanation:

• Properly closing the connection ensures resources are freed

• Essential for maintaining server performance

---

Step 10: Summary

In this tutorial, we learned:

1. How to connect PHP to MySQL

2. How to create a database and table

3. How to insert, read, update, and delete records

4. Why structured queries are important in relational databases

This forms the foundation for building dynamic web applications with a database backend