DALT.PHP
Framework Deep Dive3. Container & DB

Query Execution

How SQL queries are executed safely with prepared statements

Talking to the Database

Once connected, you need to run queries:

  • SELECT - Read data
  • INSERT - Create data
  • UPDATE - Modify data
  • DELETE - Remove data

DALT.PHP's Database class makes this simple and safe.

The Query Method

The main method for running queries:

public function query($query, $params = [])
{
    $this->statement = $this->connection->prepare($query);
    $this->statement->execute($params);
    return $this;
}

How It Works

1. Prepare the statement

$this->statement = $this->connection->prepare($query);

This tells the database: "Here's the SQL I want to run, but don't run it yet."

2. Execute with parameters

$this->statement->execute($params);

This says: "Now run it with these values."

3. Return $this for chaining

return $this;

Allows: $db->query(...)->find()

Prepared Statements: The Security Foundation

The Problem: SQL Injection

Dangerous code:

$id = $_GET['id'];
$query = "SELECT * FROM posts WHERE id = $id";
$db->query($query);

What an attacker can do:

Normal: ?id=1
Query:  SELECT * FROM posts WHERE id = 1

Attack: ?id=1 OR 1=1
Query:  SELECT * FROM posts WHERE id = 1 OR 1=1
Result: Returns ALL posts!

Attack: ?id=1; DROP TABLE posts--
Query:  SELECT * FROM posts WHERE id = 1; DROP TABLE posts--
Result: Deletes your entire table!

The Solution: Parameter Binding

Safe code:

$id = $_GET['id'];
$query = "SELECT * FROM posts WHERE id = :id";
$db->query($query, ['id' => $id]);

What happens:

Attack: ?id=1 OR 1=1

Step 1: Prepare
SQL: SELECT * FROM posts WHERE id = :id

Step 2: Bind
:id → "1 OR 1=1" (treated as a string)

Step 3: Execute
SQL: SELECT * FROM posts WHERE id = '1 OR 1=1'
Result: No match (looking for id literally equal to that string)

Why it's safe:

  • Parameters are escaped automatically
  • Treated as data, not SQL code
  • No way to inject malicious SQL

The Three Fetch Methods

After running a query, you need to get results:

1. find() - Get One Row

public function find()
{
    return $this->statement->fetch();
}

Usage:

$post = $db->query('SELECT * FROM posts WHERE id = :id', ['id' => 1])
    ->find();

// Result: ['id' => 1, 'title' => 'Hello', 'body' => 'World']
// Or: false if not found

When to use:

  • Getting a single record
  • You expect 0 or 1 result

Important: Returns false if no match, not null.

2. findOrFail() - Get One or Die

public function findOrFail()
{
    $result = $this->find();
    if (!$result) {
        abort();
    }
    return $result;
}

Usage:

$post = $db->query('SELECT * FROM posts WHERE id = :id', ['id' => 1])
    ->findOrFail();

// Result: ['id' => 1, 'title' => 'Hello', 'body' => 'World']
// Or: Aborts with 404 if not found

When to use:

  • Showing a specific resource
  • You expect it to exist
  • Want automatic 404 handling

Why this is useful:

Without findOrFail():

$post = $db->query('...')->find();
if (!$post) {
    abort(404);
}
// Use $post

With findOrFail():

$post = $db->query('...')->findOrFail();
// Use $post (no need to check)

3. get() - Get All Rows

public function get()
{
    return $this->statement->fetchAll();
}

Usage:

$posts = $db->query('SELECT * FROM posts ORDER BY created_at DESC')
    ->get();

// Result: [
//     ['id' => 1, 'title' => 'First'],
//     ['id' => 2, 'title' => 'Second'],
//     ['id' => 3, 'title' => 'Third'],
// ]

When to use:

  • Getting multiple records
  • Listing resources
  • Always returns an array (empty if no results)

Query Examples

SELECT One Record

$user = $db->query('SELECT * FROM users WHERE email = :email', [
    'email' => 'user@example.com'
])->find();

if ($user) {
    echo "Welcome, " . $user['email'];
} else {
    echo "User not found";
}

SELECT All Records

$posts = $db->query('SELECT * FROM posts ORDER BY created_at DESC')
    ->get();

foreach ($posts as $post) {
    echo $post['title'];
}

SELECT with Multiple Parameters

$posts = $db->query('
    SELECT * FROM posts 
    WHERE user_id = :user_id 
    AND published = :published
    ORDER BY created_at DESC
', [
    'user_id' => 5,
    'published' => 1
])->get();

INSERT

$db->query('
    INSERT INTO posts (title, body, user_id) 
    VALUES (:title, :body, :user_id)
', [
    'title' => 'New Post',
    'body' => 'Content here',
    'user_id' => 1
]);

Tip

No ->find() or ->get() needed for INSERT.

UPDATE

$db->query('
    UPDATE posts 
    SET title = :title, body = :body 
    WHERE id = :id
', [
    'title' => 'Updated Title',
    'body' => 'Updated content',
    'id' => 1
]);

DELETE

$db->query('DELETE FROM posts WHERE id = :id', [
    'id' => 1
]);

Complex Query with JOIN

$posts = $db->query('
    SELECT posts.*, users.email 
    FROM posts 
    JOIN users ON posts.user_id = users.id 
    WHERE posts.published = :published
    ORDER BY posts.created_at DESC
', [
    'published' => 1
])->get();

Parameter Binding Syntax

$db->query('SELECT * FROM posts WHERE id = :id', [
    'id' => 1
]);

Benefits:

  • ✅ Clear what each parameter is
  • ✅ Order doesn't matter
  • ✅ Can reuse parameters

Positional Parameters

$db->query('SELECT * FROM posts WHERE id = ?', [1]);

When to use:

  • Single parameter
  • Very simple queries

Drawbacks:

  • ❌ Order matters
  • ❌ Less readable
  • ❌ Can't reuse parameters

Method Chaining

The query() method returns $this, enabling chaining:

$post = $db->query('SELECT * FROM posts WHERE id = :id', ['id' => 1])
    ->find();

How it works:

$db->query(...)  // Returns $db
   ->find()      // Called on $db

Why this is nice:

  • Reads like English: "query then find"
  • No intermediate variables needed
  • Fluent interface pattern

Understanding PDO Under the Hood

What prepare() Does

$this->statement = $this->connection->prepare($query);

PDO sends the SQL to the database server:

  1. Database parses the SQL
  2. Database creates an execution plan
  3. Database waits for parameters

Benefits:

  • Query is compiled once
  • Can execute multiple times with different params
  • Database can optimize the query

What execute() Does

$this->statement->execute($params);

PDO sends the parameters:

  1. Database binds parameters to placeholders
  2. Database executes the query
  3. Results are ready to fetch

What fetch() and fetchAll() Do

$this->statement->fetch();      // Get one row
$this->statement->fetchAll();   // Get all rows

PDO retrieves results from the database.

Fetch modes:

  • FETCH_ASSOC - Associative array (what DALT uses)
  • FETCH_NUM - Numeric array
  • FETCH_OBJ - Object
  • FETCH_BOTH - Both associative and numeric (default, wasteful)

DALT sets FETCH_ASSOC globally:

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC

Error Handling

PDO is configured to throw exceptions:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

What this means:

try {
    $db->query('SELECT * FROM nonexistent_table')->get();
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
}

Without exception mode:

$result = $db->query('SELECT * FROM nonexistent_table');
if ($result === false) {
    // Check error manually
    $error = $db->getConnection()->errorInfo();
}

Exception mode is cleaner and harder to ignore errors.

Common Patterns

Check if Record Exists

$exists = $db->query('SELECT 1 FROM users WHERE email = :email', [
    'email' => $email
])->find();

if ($exists) {
    // Email is taken
}

Count Records

$result = $db->query('SELECT COUNT(*) as count FROM posts')->find();
$count = $result['count'];

Get Last Insert ID

$db->query('INSERT INTO posts (title) VALUES (:title)', [
    'title' => 'New Post'
]);

$id = $db->getConnection()->lastInsertId();

Transaction

$db->getConnection()->beginTransaction();

try {
    $db->query('INSERT INTO posts ...');
    $db->query('INSERT INTO tags ...');
    $db->getConnection()->commit();
} catch (Exception $e) {
    $db->getConnection()->rollBack();
    throw $e;
}

Key Takeaways

  1. Always use parameter binding - Never concatenate user input
  2. Three fetch methods - find(), findOrFail(), get()
  3. Method chaining - query()->find() is clean and readable
  4. PDO does the heavy lifting - Prepare, execute, fetch
  5. Exceptions on errors - Can't ignore database problems

What's Good Here

✅ Simple, clean API
✅ Prepared statements by default (secure)
✅ Method chaining (readable)
✅ findOrFail() convenience method
✅ Forces you to learn SQL (educational benefit)

Design Note

Query builders, models, and eager loading are intentionally omitted. DALT forces you to write raw SQL so you understand what's actually happening. ORMs like Eloquent are powerful but hide the database layer. Once you understand SQL with DALT, you'll appreciate what ORMs do for you.

Next, explore how migrations work and how the database schema is managed.

These would require a more complex database layer (like Eloquent).

Database Connection

How database connections are created and managed

Migrations

How database schema is created and versioned with migrations

On this page

Talking to the DatabaseThe Query MethodHow It WorksPrepared Statements: The Security FoundationThe Problem: SQL InjectionThe Solution: Parameter BindingThe Three Fetch Methods1. find() - Get One Row2. findOrFail() - Get One or Die3. get() - Get All RowsQuery ExamplesSELECT One RecordSELECT All RecordsSELECT with Multiple ParametersINSERTTipUPDATEDELETEComplex Query with JOINParameter Binding SyntaxNamed Parameters (Recommended)Positional ParametersMethod ChainingUnderstanding PDO Under the HoodWhat prepare() DoesWhat execute() DoesWhat fetch() and fetchAll() DoError HandlingCommon PatternsCheck if Record ExistsCount RecordsGet Last Insert IDTransactionKey TakeawaysWhat's Good HereDesign Note