Database API

The SLAED CMS Database API provides a comprehensive interface for interacting with the database layer, offering both traditional query methods and modern prepared statements for security.

Database Connection

SLAED CMS uses a global database connection object that is available throughout the system:

global $db, $prefix;

// $db - Database connection object
// $prefix - Table prefix (configured during installation)

Basic Queries

The traditional query methods are still available for simple operations:

global $db, $prefix;

// Basic query execution
$result = $db->sql_query("SELECT * FROM {$prefix}_table");

// Fetch single row as array
$row = $db->sql_fetchrow($result);

// Fetch single row as associative array
$assoc_row = $db->sql_fetchassoc($result);

// Get number of rows
$num_rows = $db->sql_numrows($result);

// Get last insert ID
$insert_id = $db->sql_insertid();

// Escape string (deprecated - use prepared statements)
$safe_string = $db->sql_escape_string($string);

Prepared Statements

For security and performance, prepared statements are recommended for all database operations:

SELECT with Prepared Statements

// SELECT with prepared statement
$stmt = $db->prepare("SELECT id, title, content FROM {$prefix}_news WHERE category = ? AND active = ?");
$stmt->bind_param("ii", $category_id, $active);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['title'];
}

INSERT with Prepared Statements

// INSERT with prepared statement
$stmt = $db->prepare("INSERT INTO {$prefix}_news (title, content, author_id, created) VALUES (?, ?, ?, NOW())");
$stmt->bind_param("ssi", $title, $content, $author_id);
$stmt->execute();
$new_id = $stmt->insert_id;

UPDATE with Prepared Statements

// UPDATE with prepared statement
$stmt = $db->prepare("UPDATE {$prefix}_news SET title = ?, content = ? WHERE id = ?");
$stmt->bind_param("ssi", $title, $content, $id);
$stmt->execute();
$affected_rows = $stmt->affected_rows;

DELETE with Prepared Statements

// DELETE with prepared statement
$stmt = $db->prepare("DELETE FROM {$prefix}_news WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();

Transactions

SLAED CMS supports database transactions for ensuring data consistency:

// Begin transaction
global $db;
$db->begin_transaction();

try {
    // Multiple database operations
    $stmt1 = $db->prepare("INSERT INTO {$prefix}_table1 (data) VALUES (?)");
    $stmt1->bind_param("s", $data1);
    $stmt1->execute();
    
    $stmt2 = $db->prepare("UPDATE {$prefix}_table2 SET status = ? WHERE id = ?");
    $stmt2->bind_param("si", $status, $id);
    $stmt2->execute();
    
    // Commit transaction
    $db->commit();
    echo "Transaction completed successfully";
    
} catch (Exception $e) {
    // Rollback on error
    $db->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

Utility Functions

SLAED CMS provides several utility functions for common database operations:

Input Validation

// Safely retrieve and filter input variables
$id = getVar('get', 'id', 'num');          // Get numeric value from GET
$title = getVar('post', 'title', 'text');   // Get filtered text from POST
$email = getVar('post', 'email', 'email');  // Get email with validation
$website = getVar('post', 'website', 'url'); // Get URL with validation

Error Handling

// Check for database errors
if ($db->errno) {
    echo "Database error: " . $db->error;
}

// Get affected rows
$affected = $stmt->affected_rows;

// Check if query was successful
if ($result) {
    // Process results
} else {
    // Handle error
}