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.
Table of Contents
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
}