PHP-MySQL Integration: CRUD, Security, and Real-World Data Handling

TAKEAWAYS:

• PHP connects to MySQL using PDO or mysqli, with prepared statements to prevent SQL injection.
• CRUD operations (Create, Read, Update, Delete) are implemented via INSERT, SELECT, UPDATE, and DELETE queries.
• Form validation (client-side + server-side) ensures data integrity before database insertion.
• Real-world applications include user authentication (e.g., eSewa), order processing (Daraz), and inventory management (NTC).
• Security best practices include escaping inputs, using HTTPS, and restricting database permissions.
• Error handling with try-catch blocks and mysql_error() ensures robust database operations.

1. PHP and MySQL: The Dynamic Duo

PHP and MySQL are the backbone of dynamic websites. PHP acts as the server-side script to process user requests, while MySQL stores and retrieves data efficiently.

How PHP Connects to MySQL

PHP uses two primary extensions to interact with MySQL:

1. MySQL Improved (mysqli): Object-oriented or procedural interface.
2. PHP Data Objects (PDO): Database-agnostic, supports multiple databases (MySQL, PostgreSQL, etc.).

flowchart TD
    A["PHP Script"] -->|"User Request"| B["MySQL Database"]
    B -->|"Query"| C["Result Set"]
    C -->|"Processed Data"| A

Example: Connecting to MySQL with PDO

<?php
$host = "localhost";
$dbname = "FOHSS";
$username = "root";
$password = "";

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

Trace of Connection Process

2. CRUD Operations: The Heart of Database Interaction

CRUD stands for Create, Read, Update, Delete—the four fundamental operations for database management.

A. Create (INSERT)

Inserts new records into a table. Example: Storing BCA Entrance Exam Data

$stmt = $conn->prepare("INSERT INTO applicants (name, email, mobile, password, gender, faculty, dob)
                       VALUES (:name, :email, :mobile, :password, :gender, :faculty, :dob)");
$stmt->execute([
    ':name' => $_POST['name'],
    ':email' => $_POST['email'],
    ':mobile' => $_POST['mobile'],
    ':password' => password_hash($_POST['password'], PASSWORD_DEFAULT),
    ':gender' => $_POST['gender'],
    ':faculty' => $_POST['faculty'],
    ':dob' => $_POST['dob']
]);

State After Insertion (Database Table)

| id | name      | email               | mobile   | password                     | gender | faculty      | dob         |
|----|-----------|---------------------|----------|------------------------------|--------|--------------|-------------|
| 1  | Ram Prasad| ram@example.com     | 9800000000 | $2y$10$...                  | M      | Science      | 2000-01-01  |

B. Read (SELECT)

Retrieves data from the database. Example: Fetching All Applicants

$stmt = $conn->query("SELECT * FROM applicants");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "Name: " . $row['name'] . "<br>";
}

State After SELECT (Result Set)

[
    { "id": 1, "name": "Ram Prasad", "email": "ram@example.com", ... },
    { "id": 2, "name": "Sita Devi", "email": "sita@example.com", ... }
]

C. Update (UPDATE)

Modifies existing records. Example: Updating Mobile Number

$stmt = $conn->prepare("UPDATE applicants SET mobile = :mobile WHERE id = :id");
$stmt->execute([
    ':mobile' => $_POST['new_mobile'],
    ':id' => $_GET['id']
]);

D. Delete (DELETE)

Removes records. Example: Deleting an Applicant

$stmt = $conn->prepare("DELETE FROM applicants WHERE id = :id");
$stmt->execute([':id' => $_GET['id']]);

3. Form Handling and Validation

Forms are the bridge between users and databases. Validation ensures only clean data is stored.

HTML Form for BCA Registration

<form method="post" action="register.php">
    <input type="text" name="name" placeholder="Full Name" required>
    <input type="email" name="email" placeholder="Email" required>
    <input type="tel" name="mobile" placeholder="Mobile" required>
    <input type="password" name="password" placeholder="Password" required>
    <select name="faculty">
        <option value="Science">Science</option>
        <option value="Management">Management</option>
    </select>
    <input type="date" name="dob" required>
    <button type="submit">Register</button>
</form>

Server-Side Validation in PHP

if (empty($_POST['name']) || strlen($_POST['name']) > 40) {
    die("Name must be 1-40 characters.");
}
if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
    die("Invalid email format.");
}

Validation Rules Table

4. Security: Preventing SQL Injection and XSS

SQL Injection occurs when user input is directly embedded in SQL queries. Example of Vulnerable Code:

// UNSAFE: Directly embedding user input
$email = $_POST['email'];
$query = "SELECT * FROM users WHERE email = '$email'";

Fixed with Prepared Statements:

// SAFE: Using PDO prepared statements
$stmt = $conn->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute([':email' => $_POST['email']]);

Cross-Site Scripting (XSS) Prevention Escape output using htmlspecialchars():

echo htmlspecialchars($_POST['name'], ENT_QUOTES, 'UTF-8');

5. Real-World Applications

A. eSewa: User Authentication

• Idea Used: Secure password hashing (password_hash()) and prepared statements for login queries.
• How: When users register, their passwords are hashed and stored. During login, the input password is hashed and compared to the stored hash.

B. Daraz: Order Processing

• Idea Used: CRUD operations for managing orders, inventory, and user profiles.
• How: When you place an order, Daraz uses INSERT to add it to the orders table, UPDATE to reduce stock, and SELECT to fetch order status.

C. NTC: Traffic Route Optimization

• Idea Used: Storing and querying route data in MySQL.
• How: NTC’s website might use SELECT to fetch the fastest route between two cities based on user input.

Example: Daraz Order Queue (FIFO)

flowchart TD
    A["User Places Order"] --> B["Add to Queue (INSERT)"]
    B --> C["Process Payment (UPDATE)"]
    C --> D["Update Inventory (UPDATE)"]
    D --> E["Notify User (SELECT)"]

6. Error Handling and Debugging

Use try-catch blocks to handle database errors gracefully. Example:

try {
    $stmt = $conn->query("SELECT * FROM nonexistent_table");
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Common Errors and Fixes

7. Best Practices

1. Use Prepared Statements: Always use prepare() and execute() to prevent SQL injection.
2. Validate Input: Never trust user input. Validate on both client and server sides.
3. Sanitize Output: Use htmlspecialchars() to prevent XSS attacks.
4. Limit Database Permissions: Grant only necessary privileges to the database user.
5. Use Transactions: For critical operations (e.g., transfers), use BEGIN, COMMIT, and ROLLBACK.

Exam Tip

1. CRUD Questions: Expect questions on designing forms and writing PHP/MySQL code for all four operations. Always include:

   • HTML form with proper input types (text, email, password, date, select).
   • Server-side validation (e.g., empty(), filter_var()).
   • PDO prepared statements for security.
   • Error handling with try-catch.

2. Security: Focus on SQL injection prevention (prepared statements) and XSS prevention (htmlspecialchars()). Past exams often test vulnerable vs. secure code.

3. Real-World Scenarios: Relate your answers to systems like eSewa (authentication), Daraz (order processing), or NTC (data queries). For example:

   • "How would you design a Daraz order system?" → Use CRUD with orders and inventory tables.
   • "How does eSewa secure user passwords?" → Hashing with password_hash().

4. Code Structure: Organize your PHP code clearly:

   • Database connection at the top.
   • Form handling in the middle.
   • Validation and queries in separate functions if possible.

5. SQL Queries: Write queries with proper syntax and aliases. For example:

   SELECT a.name, a.email, COUNT(o.id) as order_count
   FROM applicants a
   LEFT JOIN orders o ON a.id = o.user_id
   GROUP BY a.id;
   

Visual Summary of PHP-MySQL Workflow

sequenceDiagram
    User->>+Form: Submits Data
    Form->>+PHP: POST Request
    PHP->>+Database: PDO Connection
    Database-->>-PHP: Connection Established
    PHP->>+Database: Prepared Query (INSERT/UPDATE)
    Database-->>-PHP: Success/Failure
    PHP->>+User: Display Result or Error