Database Results

🎣 PHP Database Results: Catching Data Like Fish!

Imagine you’re a fisherman with a magical net. Your database is the ocean, full of fish (data). Today, we’ll learn how to catch exactly the fish you want!

🐟 The Story of Data Fisherman Pete

Pete has a fishing net (PHP code) and an ocean (database). Let’s follow his adventures!

1. Fetching Single Row - Catching ONE Special Fish

Sometimes Pete only needs ONE fish. Maybe the biggest one, or one with a special name.

🎯 What is it?

When you ask the database a question and only want ONE answer back.

Real Life Example

• You search for YOUR profile on a website → ONE result
• You look up ONE specific order → ONE result

The Magic Spell (Code)

// Prepare the fishing net
$stmt = $pdo->prepare(
  "SELECT * FROM users
   WHERE id = ?"
);

// Throw the net with bait (id = 5)
$stmt->execute([5]);

// Catch ONE fish
$user = $stmt->fetch();

// Show what we caught!
echo $user['name'];

🧠 Simple Breakdown

1. prepare() = Get your net ready
2. execute([5]) = Throw it looking for fish #5
3. fetch() = Pull out ONE fish
4. $user[‘name’] = Look at the fish’s name tag

2. Fetching All Rows - Catching MANY Fish!

Now Pete wants ALL the fish in one area!

🎯 What is it?

When you want EVERY matching result, not just one.

Real Life Example

• Show ALL products in a store
• List ALL comments on a post

The Magic Spell (Code)

$stmt = $pdo->prepare(
  "SELECT * FROM products"
);
$stmt->execute();

// Catch ALL fish at once!
$products = $stmt->fetchAll();

// Count your fish
echo count($products) . " items!";

// Look at each fish
foreach($products as $item) {
  echo $item['name'];
}

🎣 fetch() vs fetchAll()

3. Fetch Modes - How Do You Want Your Fish Served?

Pete can get his fish served different ways!

🍽️ The Menu of Fetch Modes

graph TD
    A["Fetch Modes"] --> B["FETCH_ASSOC"]
    A --> C["FETCH_NUM"]
    A --> D["FETCH_BOTH"]
    A --> E["FETCH_OBJ"]
    B --> B1["name => 'Pete'"]
    C --> C1["0 => 'Pete'"]
    D --> D1["Both ways!"]
    E --> E1["->name = 'Pete'"]

Mode 1: FETCH_ASSOC (Named Tags)

$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name']; // Pete
echo $row['age'];  // 25

Like fish with NAME labels!

Mode 2: FETCH_NUM (Numbered)

$row = $stmt->fetch(PDO::FETCH_NUM);
echo $row[0]; // Pete (first column)
echo $row[1]; // 25 (second column)

Like fish with NUMBER labels!

Mode 3: FETCH_OBJ (Object Style)

$row = $stmt->fetch(PDO::FETCH_OBJ);
echo $row->name; // Pete
echo $row->age;  // 25

Like fish that talk back!

🏆 Pro Tip

Most developers use FETCH_ASSOC because names are easier to remember than numbers!

4. Affected Rows - How Many Fish Did We Change?

Pete sometimes doesn’t catch fish. He CHANGES them! Maybe paints them or moves them.

🎯 What is it?

After UPDATE or DELETE, you want to know: “How many fish did I affect?”

The Magic Spell (Code)

// Paint all red fish blue
$stmt = $pdo->prepare(
  "UPDATE fish SET color = 'blue'
   WHERE color = 'red'"
);
$stmt->execute();

// How many fish changed?
$changed = $stmt->rowCount();
echo "$changed fish painted!";

When You Need This

• “Did my update work?” → Check if rowCount > 0
• “How many items deleted?” → rowCount tells you
• “Did anything change?” → rowCount === 0 means nothing changed

⚠️ Important Note

rowCount() works for UPDATE, DELETE, INSERT. For SELECT, it may not work reliably on all databases!

5. Last Insert ID - What Number Did My New Fish Get?

When Pete adds a NEW fish to his collection, each fish gets a special number!

🎯 What is it?

After INSERT, the database gives your new row an automatic ID number. You need to know what it is!

Why Do You Need This?

• Add a user → Need their ID to add their profile
• Create an order → Need order ID for order items
• Save a post → Need post ID for tags

The Magic Spell (Code)

// Add a new fish
$stmt = $pdo->prepare(
  "INSERT INTO fish (name, color)
   VALUES (?, ?)"
);
$stmt->execute(['Nemo', 'orange']);

// What ID did Nemo get?
$nemoId = $pdo->lastInsertId();
echo "Nemo's ID is: $nemoId";

🔑 Key Points

1. Call lastInsertId() on $pdo, not $stmt
2. Call it RIGHT AFTER the INSERT
3. Only works with auto-increment columns

6. Transactions - All or Nothing Fishing!

Pete wants to trade 3 fish with his friend. But ONLY if ALL trades work!

🎯 What is it?

A transaction is a GROUP of database operations that must ALL succeed. If ONE fails, NONE happen!

Real Life Example

• Bank transfer: Take $100 from Account A, Give $100 to Account B
• If EITHER fails, NEITHER should happen!

graph TD
    A["Start Transaction"] --> B["Take money from A"]
    B --> C{Success?}
    C -->|Yes| D["Give money to B"]
    C -->|No| E["ROLLBACK!"]
    D --> F{Success?}
    F -->|Yes| G["COMMIT!"]
    F -->|No| E
    G --> H["Both changes saved!"]
    E --> I["Nothing changed!"]

The Magic Spell (Code)

try {
  // Start the group
  $pdo->beginTransaction();

  // Step 1: Take from account A
  $pdo->exec(
    "UPDATE accounts
     SET balance = balance - 100
     WHERE id = 1"
  );

  // Step 2: Give to account B
  $pdo->exec(
    "UPDATE accounts
     SET balance = balance + 100
     WHERE id = 2"
  );

  // All good? Save everything!
  $pdo->commit();
  echo "Transfer complete!";

} catch (Exception $e) {
  // Something failed? Undo ALL!
  $pdo->rollBack();
  echo "Transfer cancelled!";
}

🧩 The Three Magic Words

7. Database Error Handling - When Fishing Goes Wrong!

Sometimes the ocean is stormy! Pete needs to handle problems gracefully.

🎯 What is it?

Catching errors so your app doesn’t crash, and telling users what went wrong nicely.

Setting Up Error Mode

$pdo = new PDO($dsn, $user, $pass, [
  PDO::ATTR_ERRMODE =>
    PDO::ERRMODE_EXCEPTION
]);

The Three Error Modes

graph TD
    A["Error Modes"] --> B["SILENT"]
    A --> C["WARNING"]
    A --> D["EXCEPTION"]
    B --> B1["Errors hidden 👻"]
    C --> C1["Shows warning ⚠️"]
    D --> D1["Throws error 💥"]

Catching Errors Like a Pro

try {
  $stmt = $pdo->prepare($sql);
  $stmt->execute($params);
  $result = $stmt->fetchAll();

} catch (PDOException $e) {
  // Log the real error (for you)
  error_log($e->getMessage());

  // Show nice message (for user)
  echo "Oops! Something went wrong.";
}

⚠️ Security Warning!

NEVER show database errors to users!

// ❌ BAD - Shows secrets!
echo $e->getMessage();

// ✅ GOOD - Friendly message
echo "Please try again later.";

🎯 Quick Summary

🚀 Your Journey Map

graph TD
    A["🎣 Start Fishing"] --> B["Prepare Query"]
    B --> C["Execute Query"]
    C --> D{What do you need?}
    D -->|One fish| E["fetch"]
    D -->|All fish| F["fetchAll"]
    D -->|Changed count| G["rowCount"]
    D -->|New ID| H["lastInsertId"]
    E --> I["Choose Fetch Mode"]
    F --> I
    I --> J["Handle Errors!"]
    G --> J
    H --> J
    J --> K["🎉 Done!"]

🎉 You Did It!

Now you can:

• ✅ Catch one fish or many
• ✅ Get your fish served your way
• ✅ Know how many fish changed
• ✅ Track new fish IDs
• ✅ Make safe group changes
• ✅ Handle stormy errors

Remember: The database is your ocean. PHP is your net. Now go fishing! 🐟