Working with Database

$db = App::resolve(Core\Database::class);

// Select all
$posts = $db->query('SELECT * FROM posts')->get();

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

// Select with conditions
$published = $db->query('SELECT * FROM posts WHERE published = ?', [1])->get();

// Insert and get ID
$db->query(
    'INSERT INTO posts (title, body) VALUES (?, ?)',
    ['My Title', 'My Body']
);

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

$db->query(
    'UPDATE posts SET title = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
    ['New Title', 1]
);

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

// Multiple conditions
$posts = $db->query(
    'SELECT * FROM posts WHERE published = ? AND user_id = ?',
    [1, $userId]
)->get();

// LIKE search
$posts = $db->query(
    'SELECT * FROM posts WHERE title LIKE ?',
    ['%' . $search . '%']
)->get();

// IN clause
$posts = $db->query(
    'SELECT * FROM posts WHERE id IN (?, ?, ?)',
    [1, 2, 3]
)->get();

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

// Limit
$posts = $db->query(
    'SELECT * FROM posts LIMIT 10'
)->get();

// Pagination
$page = 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;

$posts = $db->query(
    'SELECT * FROM posts LIMIT ? OFFSET ?',
    [$perPage, $offset]
)->get();

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

// Sum
$total = $db->query('SELECT SUM(views) as total FROM posts')->find()['total'];

// Average
$avg = $db->query('SELECT AVG(rating) as avg FROM posts')->find()['avg'];

// Max/Min
$max = $db->query('SELECT MAX(created_at) as max FROM posts')->find()['max'];

-- Migration: create_comments_table.sql
CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

// Get post with comments
$post = $db->query('SELECT * FROM posts WHERE id = ?', [$id])->find();
$comments = $db->query('SELECT * FROM comments WHERE post_id = ?', [$id])->get();

// Or use JOIN
$results = $db->query(
    'SELECT posts.*, comments.body as comment_body, comments.created_at as comment_date
     FROM posts
     LEFT JOIN comments ON posts.id = comments.post_id
     WHERE posts.id = ?',
    [$id]
)->get();

-- posts_tags pivot table
CREATE TABLE IF NOT EXISTS posts_tags (
    post_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

// Get post with tags
$tags = $db->query(
    'SELECT tags.* FROM tags
     INNER JOIN posts_tags ON tags.id = posts_tags.tag_id
     WHERE posts_tags.post_id = ?',
    [$postId]
)->get();

// Attach tag to post
$db->query(
    'INSERT INTO posts_tags (post_id, tag_id) VALUES (?, ?)',
    [$postId, $tagId]
);

try {
    $db->connection->beginTransaction();
    
    // Create post
    $db->query(
        'INSERT INTO posts (title, body) VALUES (?, ?)',
        ['Title', 'Body']
    );
    $postId = $db->connection->lastInsertId();
    
    // Attach tags
    foreach ($tagIds as $tagId) {
        $db->query(
            'INSERT INTO posts_tags (post_id, tag_id) VALUES (?, ?)',
            [$postId, $tagId]
        );
    }
    
    $db->connection->commit();
} catch (\Exception $e) {
    $db->connection->rollBack();
    throw $e;
}

// ✅ SAFE - Prepared statement
$post = $db->query('SELECT * FROM posts WHERE id = ?', [$_GET['id']])->find();

// ❌ DANGEROUS - SQL injection vulnerable
$post = $db->query("SELECT * FROM posts WHERE id = {$_GET['id']}")->find();

// Validate before querying
if (!is_numeric($_GET['id'])) {
    abort(400);
}

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

// Always escape when displaying
<?= htmlspecialchars($post['title']) ?>

-- Index frequently queried columns
CREATE INDEX IF NOT EXISTS idx_posts_published ON posts(published);
CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id);
CREATE INDEX IF NOT EXISTS idx_comments_post_id ON comments(post_id);

// Analyze query performance
$result = $db->query('EXPLAIN QUERY PLAN SELECT * FROM posts WHERE published = 1')->get();
print_r($result);

// Don't fetch all rows if you only need some
$posts = $db->query('SELECT * FROM posts LIMIT 10')->get();

// ✅ Better - only needed columns
$posts = $db->query('SELECT id, title FROM posts')->get();

// ❌ Wasteful - all columns
$posts = $db->query('SELECT * FROM posts')->get();

$sql = 'SELECT * FROM posts WHERE 1=1';
$params = [];

if (!empty($_GET['search'])) {
    $sql .= ' AND title LIKE ?';
    $params[] = '%' . $_GET['search'] . '%';
}

if (!empty($_GET['published'])) {
    $sql .= ' AND published = ?';
    $params[] = $_GET['published'];
}

$sql .= ' ORDER BY created_at DESC';

$posts = $db->query($sql, $params)->get();

$page = $_GET['page'] ?? 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;

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

// Get paginated results
$posts = $db->query(
    'SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?',
    [$perPage, $offset]
)->get();

$totalPages = ceil($total / $perPage);

-- Add deleted_at column
ALTER TABLE posts ADD COLUMN deleted_at DATETIME DEFAULT NULL;

// Soft delete
$db->query(
    'UPDATE posts SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?',
    [$id]
);

// Query only non-deleted
$posts = $db->query('SELECT * FROM posts WHERE deleted_at IS NULL')->get();

// Restore
$db->query('UPDATE posts SET deleted_at = NULL WHERE id = ?', [$id]);

php artisan make:migration add_views_to_posts

-- Add column
ALTER TABLE posts ADD COLUMN views INTEGER DEFAULT 0;

-- Add index
CREATE INDEX IF NOT EXISTS idx_posts_views ON posts(views);

# Run pending migrations
php artisan migrate

# Reset and re-run all
php artisan migrate:fresh