Database Basics

🏠 PHP Database Operations: Your First House Keys

Imagine your database is like a house full of treasures. To get inside, you need the right keys and know how to open doors safely. Today, we’re learning how PHP talks to databases!

🗝️ The Big Picture

Think of it this way:

• Database = A big house with many rooms full of information
• PHP = You, standing outside, wanting to get in
• Connection = The key that unlocks the door
• Query = Asking for something inside the house

1. MySQLi Connection 🚪

MySQLi is like having a special key made just for MySQL houses.

What You Need (The Key Parts)

$host = "localhost";   // Address
$user = "root";        // Your name
$pass = "secret";      // Password
$db   = "my_shop";     // Which room?

Opening the Door

$conn = new mysqli(
    $host,
    $user,
    $pass,
    $db
);

// Did the door open?
if ($conn->connect_error) {
    die("Can't get in!");
}
echo "Welcome inside!";

🎯 Simple Explanation

• new mysqli() = “Please make me a key”
• We give it 4 things: where, who, password, which room
• If the key doesn’t work, we stop and show an error

2. PDO Connection 🔐

PDO is like a master key that works on many different house types—MySQL, PostgreSQL, SQLite, and more!

The Magic Key

$pdo = new PDO(
    "mysql:host=localhost;dbname=my_shop",
    "root",
    "secret"
);

🎯 Why PDO is Special

3. DSN Format 📝

DSN = Data Source Name

It’s like writing an address on an envelope:

mysql:host=localhost;dbname=my_shop;charset=utf8mb4
  ↑         ↑              ↑            ↑
driver   location       room name    language

Breaking It Down

graph TD
    A["DSN String"] --> B["Driver: mysql"]
    A --> C["Host: localhost"]
    A --> D["Database: my_shop"]
    A --> E["Charset: utf8mb4"]

📦 Different House Types (Drivers)

// MySQL house
"mysql:host=localhost;dbname=shop"

// PostgreSQL house
"pgsql:host=localhost;dbname=shop"

// SQLite house (file on disk)
"sqlite:/path/to/database.db"

4. Connection Options ⚙️

When you enter a house, you can set rules for how you behave inside.

PDO Options (House Rules)

$options = [
    // Show real errors
    PDO::ATTR_ERRMODE =>
        PDO::ERRMODE_EXCEPTION,

    // Get data as arrays
    PDO::ATTR_DEFAULT_FETCH_MODE =>
        PDO::FETCH_ASSOC,

    // Real prepared statements
    PDO::ATTR_EMULATE_PREPARES =>
        false
];

$pdo = new PDO($dsn, $user, $pass, $options);

🎯 What Each Rule Does

5. Query Execution 📤

A query is like asking a question to the house:

• “What’s in the kitchen?” → SELECT
• “Put this in the bedroom!” → INSERT
• “Change the carpet color!” → UPDATE
• “Throw out the old chair!” → DELETE

Simple Query (MySQLi)

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

// Read each item
while ($row = $result->fetch_assoc()) {
    echo $row['name'];
}

Simple Query (PDO)

$result = $pdo->query(
    "SELECT * FROM products"
);

// Read each item
foreach ($result as $row) {
    echo $row['name'];
}

🚨 Warning!

Never put user input directly in queries! Bad people can break in!

// ❌ DANGEROUS!
$pdo->query("SELECT * FROM users
    WHERE name = '$userInput'");

// ✅ We'll learn the safe way next!

6. Prepared Statements 🛡️

Prepared statements are like having a security guard check everyone before they enter.

The Two-Step Dance

graph TD
    A["Step 1: Prepare"] --> B["Create template with ?"]
    B --> C["Step 2: Execute"]
    C --> D["Fill in the blanks safely"]

PDO Example

// Step 1: Prepare the template
$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE id = ?"
);

// Step 2: Execute with real value
$stmt->execute([5]);

// Get the result
$user = $stmt->fetch();

MySQLi Example

// Step 1: Prepare
$stmt = $conn->prepare(
    "SELECT * FROM users WHERE id = ?"
);

// Step 2: Bind and execute
$stmt->bind_param("i", $userId);
$stmt->execute();

🎯 Why This Is Amazing

The ? is like a locked box. Whatever the user types goes INTO the box, never touches the house directly!

7. Binding Parameters 🔗

Binding = Connecting your values to the ? placeholders safely.

Two Styles of Placeholders

Style 1: Question Marks (Positional)

$stmt = $pdo->prepare(
    "INSERT INTO users (name, age)
     VALUES (?, ?)"
);
$stmt->execute(["Alice", 25]);

Style 2: Named Placeholders

$stmt = $pdo->prepare(
    "INSERT INTO users (name, age)
     VALUES (:name, :age)"
);
$stmt->execute([
    ':name' => "Alice",
    ':age'  => 25
]);

MySQLi Binding Types

$stmt = $conn->prepare(
    "INSERT INTO users (name, age)
     VALUES (?, ?)"
);

// "s" = string, "i" = integer
$stmt->bind_param("si", $name, $age);
$stmt->execute();

🎯 Type Letters in MySQLi

🎉 Full Working Example

Let’s put it all together!

<?php
// 1. Connection settings
$dsn = "mysql:host=localhost;
        dbname=shop;charset=utf8mb4";

// 2. Options for safety
$options = [
    PDO::ATTR_ERRMODE =>
        PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE =>
        PDO::FETCH_ASSOC
];

// 3. Connect!
try {
    $pdo = new PDO(
        $dsn, "root", "pass", $options
    );
} catch (PDOException $e) {
    die("Connection failed!");
}

// 4. Prepared statement (SAFE!)
$stmt = $pdo->prepare(
    "SELECT * FROM products
     WHERE price < :max"
);

// 5. Execute with value
$stmt->execute([':max' => 100]);

// 6. Show results
foreach ($stmt as $product) {
    echo $product['name'] . "\n";
}
?>

🧠 Quick Recap

graph TD
    A["Start"] --> B{Choose Driver}
    B -->|MySQL only| C["MySQLi"]
    B -->|Multiple DBs| D["PDO"]
    C --> E["Create Connection"]
    D --> E
    E --> F["Write Query with ?"]
    F --> G["Prepare Statement"]
    G --> H["Bind Parameters"]
    H --> I["Execute Safely!"]

🌟 Golden Rules

1. Always use PDO unless you have a specific reason for MySQLi
2. Never put user input directly in queries - use prepared statements!
3. Set error mode to EXCEPTION so you see problems immediately
4. Use UTF-8 charset to support all languages
5. Close connections when done (PHP does this automatically, but it’s good practice)

You did it! 🎊 You now know how to safely open the database door and talk to your data. The house is open, and you have the keys!