Querying Data

Querying Data - Best Practices

Always Use Prepared Statements

Never use string concatenation for SQL queries. Always use prepared statements:

// ✅ CORRECT
$stmt = $db->prepare("SELECT * FROM clients WHERE id = ?");
$stmt->bind_param("i", $clientId);
$stmt->execute();
$result = $stmt->get_result();

// ❌ WRONG
$query = "SELECT * FROM clients WHERE id = " . $clientId;
$result = $db->query($query);

Joining Users and Clients

When querying client data, always join the users table:

$stmt = $db->prepare("
    SELECT c.*, u.first_name, u.last_name, u.email, u.phone
    FROM clients c
    INNER JOIN users u ON c.user_id = u.id
    WHERE c.id = ?
");
$stmt->bind_param("i", $clientId);
$stmt->execute();
$client = $stmt->get_result()->fetch_assoc();

Getting Product Pricing

Use the helper function to get product prices:

require_once ROOT_PATH . '/includes/helpers.php';

// Get price for specific billing cycle
$price = getProductPrice($productId, 'monthly');

// Get cheapest price
$cheapest = getCheapestProductPrice($productId);

Transaction Safety

Use transactions for multi-step operations:

$db->begin_transaction();
try {
    // Multiple database operations
    $db->query("INSERT INTO ...");
    $db->query("UPDATE ...");
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    throw $e;
}

Common Queries

Get Client with Services

$stmt = $db->prepare("
    SELECT c.*, u.first_name, u.last_name, u.email,
           COUNT(s.id) as service_count
    FROM clients c
    INNER JOIN users u ON c.user_id = u.id
    LEFT JOIN services s ON s.client_id = c.id
    WHERE c.id = ?
    GROUP BY c.id
");

Get Invoice with Items

$stmt = $db->prepare("
    SELECT i.*, 
           SUM(ii.amount) as item_total
    FROM invoices i
    LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
    WHERE i.id = ?
    GROUP BY i.id
");