SQL Query Examples

Complete examples of executing raw SQL queries using Miko's database layer.

---

Basic SQL Queries

SELECT Queries

use Miko\Database\DB;

// Simple SELECT
$users = DB::query("SELECT * FROM users");

// SELECT with WHERE
$users = DB::query(
    "SELECT * FROM users WHERE IsActive = ? AND Role = ?",
    [true, 'admin']
);

// SELECT specific columns
$users = DB::query(
    "SELECT Id, Name, Email FROM users WHERE CreatedDate > ?",
    ['2024-01-01']
);

Named Parameters

// Using named parameters
$user = DB::query(
    "SELECT * FROM users WHERE Email = :email AND IsActive = :active",
    ['email' => 'john@example.com', 'active' => true]
);

---

INSERT Queries

Single Insert

$affected = DB::execute(
    "INSERT INTO users (Name, Email, Password, Role, IsActive) VALUES (?, ?, ?, ?, ?)",
    ['John Doe', 'john@example.com', password_hash('secret', PASSWORD_DEFAULT), 'user', true]
);

// Get last inserted ID
$userId = DB::lastInsertId();
echo "Created user with ID: $userId";

Multiple Insert

$sql = "INSERT INTO users (Name, Email, Role) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)";
$affected = DB::execute($sql, [
    'User 1', 'user1@example.com', 'user',
    'User 2', 'user2@example.com', 'user',
    'User 3', 'user3@example.com', 'admin'
]);

echo "Inserted $affected rows";

Insert with SELECT

$affected = DB::execute(
    "INSERT INTO user_archives (UserId, Name, Email, ArchivedAt)
     SELECT Id, Name, Email, NOW() FROM users WHERE IsActive = 0"
);

---

UPDATE Queries

Simple Update

$affected = DB::execute(
    "UPDATE users SET IsActive = ? WHERE Id = ?",
    [true, 1]
);

echo "Updated $affected rows";

Update Multiple Columns

$affected = DB::execute(
    "UPDATE users SET Name = ?, Email = ?, UpdatedDate = NOW() WHERE Id = ?",
    ['John Updated', 'john.updated@example.com', 1]
);

Conditional Update

// Update all inactive users who haven't logged in for 1 year
$affected = DB::execute(
    "UPDATE users SET Status = 'archived' WHERE IsActive = 0 AND LastLoginAt < ?",
    [date('Y-m-d', strtotime('-1 year'))]
);

Update with JOIN

$affected = DB::execute(
    "UPDATE orders o
     INNER JOIN users u ON o.UserId = u.Id
     SET o.Status = 'cancelled'
     WHERE u.IsActive = 0"
);

---

DELETE Queries

Simple Delete

$affected = DB::execute("DELETE FROM sessions WHERE Id = ?", [123]);

Conditional Delete

// Delete expired sessions
$affected = DB::execute(
    "DELETE FROM sessions WHERE ExpiredAt < ?",
    [date('Y-m-d H:i:s')]
);

echo "Deleted $affected expired sessions";

Delete with Limit

// Delete oldest 1000 log entries
$affected = DB::execute(
    "DELETE FROM logs ORDER BY CreatedAt ASC LIMIT 1000"
);

---

Scalar Values

Get single values from queries:

// Count
$count = DB::scalar("SELECT COUNT(*) FROM users WHERE IsActive = ?", [true]);
echo "Active users: $count";

// Sum
$total = DB::scalar(
    "SELECT SUM(TotalAmount) FROM orders WHERE Status = ?",
    ['completed']
);
echo "Total revenue: $total";

// Single value
$name = DB::scalar("SELECT Name FROM users WHERE Id = ?", [1]);
echo "User name: $name";

// Average
$avgAge = DB::scalar("SELECT AVG(Age) FROM users WHERE Age IS NOT NULL");

// Max/Min
$maxPrice = DB::scalar("SELECT MAX(Price) FROM products");
$minPrice = DB::scalar("SELECT MIN(Price) FROM products WHERE IsActive = 1");

---

First Row

Get first matching row:

$user = DB::first("SELECT * FROM users WHERE Email = ?", ['john@example.com']);

if ($user) {
    echo "Found user: " . $user['Name'];
    echo "Email: " . $user['Email'];
} else {
    echo "User not found";
}

---

JOIN Queries

Inner Join

$orders = DB::query(
    "SELECT o.*, u.Name as CustomerName, u.Email as CustomerEmail
     FROM orders o
     INNER JOIN users u ON o.UserId = u.Id
     WHERE o.Status = ?
     ORDER BY o.CreatedDate DESC",
    ['pending']
);

foreach ($orders as $order) {
    echo "Order #{$order['Id']} - {$order['CustomerName']} - \${$order['TotalAmount']}\n";
}

Left Join

$users = DB::query(
    "SELECT u.*, p.Bio, p.Avatar
     FROM users u
     LEFT JOIN profiles p ON u.Id = p.UserId
     WHERE u.IsActive = 1"
);

Multiple Joins

$orderDetails = DB::query(
    "SELECT 
        o.OrderNumber,
        o.CreatedDate,
        u.Name as CustomerName,
        p.Name as ProductName,
        oi.Quantity,
        oi.UnitPrice,
        (oi.Quantity * oi.UnitPrice) as LineTotal
     FROM orders o
     INNER JOIN users u ON o.UserId = u.Id
     INNER JOIN order_items oi ON o.Id = oi.OrderId
     INNER JOIN products p ON oi.ProductId = p.Id
     WHERE o.Id = ?",
    [$orderId]
);

---

GROUP BY Queries

Simple Grouping

$roleStats = DB::query(
    "SELECT Role, COUNT(*) as UserCount
     FROM users
     WHERE IsActive = 1
     GROUP BY Role
     ORDER BY UserCount DESC"
);

foreach ($roleStats as $stat) {
    echo "{$stat['Role']}: {$stat['UserCount']} users\n";
}

Group with Having

// Find customers who spent more than $1000
$bigSpenders = DB::query(
    "SELECT u.Id, u.Name, u.Email, SUM(o.TotalAmount) as TotalSpent
     FROM users u
     INNER JOIN orders o ON u.Id = o.UserId
     WHERE o.Status = 'completed'
     GROUP BY u.Id, u.Name, u.Email
     HAVING TotalSpent > ?
     ORDER BY TotalSpent DESC",
    [1000]
);

Multiple Aggregates

$monthlySales = DB::query(
    "SELECT 
        YEAR(CreatedDate) as Year,
        MONTH(CreatedDate) as Month,
        COUNT(*) as OrderCount,
        SUM(TotalAmount) as Revenue,
        AVG(TotalAmount) as AvgOrderValue,
        MAX(TotalAmount) as MaxOrder,
        MIN(TotalAmount) as MinOrder
     FROM orders
     WHERE Status = 'completed'
     GROUP BY YEAR(CreatedDate), MONTH(CreatedDate)
     ORDER BY Year DESC, Month DESC
     LIMIT 12"
);

---

Subqueries

WHERE IN Subquery

// Users who have placed orders
$customers = DB::query(
    "SELECT * FROM users
     WHERE Id IN (SELECT DISTINCT UserId FROM orders)"
);

// Users who haven't placed orders
$nonCustomers = DB::query(
    "SELECT * FROM users
     WHERE Id NOT IN (SELECT DISTINCT UserId FROM orders)"
);

Correlated Subquery

// Users with their order count
$users = DB::query(
    "SELECT u.*,
        (SELECT COUNT(*) FROM orders WHERE UserId = u.Id) as OrderCount,
        (SELECT SUM(TotalAmount) FROM orders WHERE UserId = u.Id AND Status = 'completed') as TotalSpent
     FROM users u
     WHERE u.IsActive = 1"
);

Subquery in FROM

$topCustomers = DB::query(
    "SELECT * FROM (
        SELECT u.Id, u.Name, u.Email, SUM(o.TotalAmount) as TotalSpent
        FROM users u
        INNER JOIN orders o ON u.Id = o.UserId
        WHERE o.Status = 'completed'
        GROUP BY u.Id, u.Name, u.Email
     ) as customer_totals
     WHERE TotalSpent > 500
     ORDER BY TotalSpent DESC
     LIMIT 10"
);

---

UNION Queries

// Combine results from multiple tables
$notifications = DB::query(
    "SELECT Id, Title, 'order' as Type, CreatedAt FROM order_notifications WHERE UserId = ?
     UNION ALL
     SELECT Id, Title, 'system' as Type, CreatedAt FROM system_notifications WHERE UserId = ?
     UNION ALL
     SELECT Id, Title, 'promo' as Type, CreatedAt FROM promo_notifications WHERE UserId = ?
     ORDER BY CreatedAt DESC
     LIMIT 20",
    [$userId, $userId, $userId]
);

---

Transactions

DB::beginTransaction();

try {
    // Create order
    DB::execute(
        "INSERT INTO orders (UserId, OrderNumber, TotalAmount, Status) VALUES (?, ?, ?, ?)",
        [$userId, 'ORD-' . time(), 0, 'pending']
    );
    $orderId = DB::lastInsertId();
    
    // Add order items
    $total = 0;
    foreach ($items as $item) {
        DB::execute(
            "INSERT INTO order_items (OrderId, ProductId, Quantity, UnitPrice) VALUES (?, ?, ?, ?)",
            [$orderId, $item['product_id'], $item['quantity'], $item['price']]
        );
        $total += $item['quantity'] * $item['price'];
        
        // Update stock
        DB::execute(
            "UPDATE products SET Stock = Stock - ? WHERE Id = ?",
            [$item['quantity'], $item['product_id']]
        );
    }
    
    // Update order total
    DB::execute("UPDATE orders SET TotalAmount = ? WHERE Id = ?", [$total, $orderId]);
    
    DB::commit();
    echo "Order created successfully";
    
} catch (Exception $e) {
    DB::rollback();
    echo "Order failed: " . $e->getMessage();
}

---

Practical Examples

User Authentication

function authenticateUser(string $email, string $password): ?array
{
    $user = DB::first(
        "SELECT Id, Name, Email, Password, Role, IsActive FROM users WHERE Email = ?",
        [$email]
    );
    
    if (!$user) {
        return null;
    }
    
    if (!$user['IsActive']) {
        return null;
    }
    
    if (!password_verify($password, $user['Password'])) {
        return null;
    }
    
    // Update last login
    DB::execute(
        "UPDATE users SET LastLoginAt = NOW() WHERE Id = ?",
        [$user['Id']]
    );
    
    unset($user['Password']);
    return $user;
}

Dashboard Statistics

function getDashboardStats(): array
{
    return [
        'users' => [
            'total' => DB::scalar("SELECT COUNT(*) FROM users"),
            'active' => DB::scalar("SELECT COUNT(*) FROM users WHERE IsActive = 1"),
            'new_today' => DB::scalar("SELECT COUNT(*) FROM users WHERE DATE(CreatedDate) = CURDATE()")
        ],
        'orders' => [
            'total' => DB::scalar("SELECT COUNT(*) FROM orders"),
            'pending' => DB::scalar("SELECT COUNT(*) FROM orders WHERE Status = 'pending'"),
            'today' => DB::scalar("SELECT COUNT(*) FROM orders WHERE DATE(CreatedDate) = CURDATE()")
        ],
        'revenue' => [
            'today' => DB::scalar("SELECT COALESCE(SUM(TotalAmount), 0) FROM orders WHERE Status = 'completed' AND DATE(CreatedDate) = CURDATE()"),
            'this_month' => DB::scalar("SELECT COALESCE(SUM(TotalAmount), 0) FROM orders WHERE Status = 'completed' AND YEAR(CreatedDate) = YEAR(CURDATE()) AND MONTH(CreatedDate) = MONTH(CURDATE())"),
            'this_year' => DB::scalar("SELECT COALESCE(SUM(TotalAmount), 0) FROM orders WHERE Status = 'completed' AND YEAR(CreatedDate) = YEAR(CURDATE())")
        ]
    ];
}

Search with Pagination

function searchProducts(string $query, int $page = 1, int $perPage = 20): array
{
    $offset = ($page - 1) * $perPage;
    $searchTerm = "%$query%";
    
    // Get total count
    $total = DB::scalar(
        "SELECT COUNT(*) FROM products WHERE Name LIKE ? OR Description LIKE ?",
        [$searchTerm, $searchTerm]
    );
    
    // Get results
    $products = DB::query(
        "SELECT Id, Name, Price, Stock, ImageUrl
         FROM products
         WHERE Name LIKE ? OR Description LIKE ?
         ORDER BY Name
         LIMIT ? OFFSET ?",
        [$searchTerm, $searchTerm, $perPage, $offset]
    );
    
    return [
        'items' => $products,
        'total' => $total,
        'page' => $page,
        'per_page' => $perPage,
        'total_pages' => ceil($total / $perPage)
    ];
}

---

Security Best Practices

// ALWAYS use prepared statements
$users = DB::query("SELECT * FROM users WHERE Email = ?", [$email]);

// NEVER concatenate user input
// BAD: $users = DB::query("SELECT * FROM users WHERE Email = '$email'");

// Validate and sanitize input
$email = filter_var($input, FILTER_VALIDATE_EMAIL);
if (!$email) {
    throw new Exception('Invalid email');
}

// Use type casting for numeric values
$id = (int)$_GET['id'];
$user = DB::first("SELECT * FROM users WHERE Id = ?", [$id]);