Database - S-PHP

Overview

The Database class in S-PHP provides a PDO-based database connection with support for prepared statements, protecting your application from SQL injection attacks.

Database Configuration

Database credentials are stored in app/config/config.php and loaded from environment variables:

app/config/config.php

return [
    'host' => $_ENV['DB_HOST'] ?? 'localhost',
    'port' => $_ENV['DB_PORT'] ?? '3306',
    'database' => $_ENV['DB_DATABASE'] ?? 'new',
    'username' => $_ENV['DB_USERNAME'] ?? 'root',
    'password' => $_ENV['DB_PASSWORD'] ?? 'root',
];

Database Class

The Database class automatically creates a PDO connection when instantiated.

Constructor

__construct

method

$config

array

required

Configuration array containing database connection details (host, port, database, username, password)

Sphp/Core/Database.php

public function __construct($config)
{
    $dsn = 'mysql:host=' . $config['host'] . ';port='. $config['port'] .';dbname=' . $config['database'];

    $this->connection = new \PDO($dsn, $config['username'], $config['password']);
}

Connection Property

$connection

PDO

The PDO database connection instance used for all queries

Creating a Database Instance

You can create a database instance manually:

use Sphp\Core\Database;

$config = require('../app/config/config.php');
$db = new Database($config);

However, in most cases, you’ll access the database through controllers or models where it’s already initialized:

class HomeController extends Controller
{
    public function index()
    {
        // $this->db is automatically available
        $users = $this->db->query('SELECT * FROM users');
    }
}

Query Method

The query() method executes SQL queries using prepared statements.

query

method

$query

string

required

SQL query with placeholders (?) for parameters

$params

array

default:"array()"

Array of values to bind to query placeholders

Sphp/Core/Database.php

public function query($query, $params = array())
{
    $statement = $this->connection->prepare($query);

    $statement->execute($params);

    return $statement->fetchAll(\PDO::FETCH_ASSOC);
}

Return Value

The method returns an array of associative arrays, where each element represents a database row:

[
    ['id' => 1, 'name' => 'John', 'email' => '[email protected]'],
    ['id' => 2, 'name' => 'Jane', 'email' => '[email protected]'],
]

Using Prepared Statements

Prepared statements protect against SQL injection by separating SQL logic from data.

SELECT Queries

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

// Select with WHERE clause
$activeUsers = $db->query(
    'SELECT * FROM users WHERE verified = ?',
    [1]
);

// Multiple conditions
$result = $db->query(
    'SELECT * FROM users WHERE verified = ? AND role = ?',
    [1, 'admin']
);

// With ORDER BY and LIMIT
$recent = $db->query(
    'SELECT * FROM posts WHERE status = ? ORDER BY created_at DESC LIMIT ?',
    ['published', 10]
);

INSERT Queries

$db->query(
    'INSERT INTO users (name, email, password) VALUES (?, ?, ?)',
    ['John Doe', '[email protected]', password_hash('secret', PASSWORD_BCRYPT)]
);

UPDATE Queries

$db->query(
    'UPDATE users SET name = ?, verified = ? WHERE id = ?',
    ['Jane Doe', 1, 123]
);

DELETE Queries

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

Working with Query Results

Accessing Results

$users = $db->query('SELECT * FROM users WHERE verified = ?', [1]);

// Loop through results
foreach ($users as $user) {
    echo $user['name'] . ' - ' . $user['email'];
}

// Get first result
$firstUser = $users[0] ?? null;

// Check if results exist
if (!empty($users)) {
    // Process results
}

// Count results
$count = count($users);

Single Record Queries

$user = $db->query(
    'SELECT * FROM users WHERE id = ?',
    [123]
);

// Access the first (and only) result
$userData = $user[0] ?? null;

if ($userData) {
    echo $userData['name'];
}

Complex Queries

JOIN Operations

$query = '
    SELECT u.*, p.title, p.content
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.verified = ?
    ORDER BY p.created_at DESC
';

$results = $db->query($query, [1]);

Aggregate Functions

// Count records
$result = $db->query(
    'SELECT COUNT(*) as total FROM users WHERE verified = ?',
    [1]
);
$total = $result[0]['total'];

// Other aggregates
$stats = $db->query('
    SELECT 
        COUNT(*) as total,
        AVG(age) as average_age,
        MAX(created_at) as last_signup
    FROM users
    WHERE verified = ?
', [1]);

Using Database in Controllers

app/Controllers/UserController.php

use Sphp\Core\Controller;
use Sphp\Core\View;

class UserController extends Controller
{
    public function index()
    {
        $users = $this->db->query(
            'SELECT id, name, email FROM users WHERE verified = ?',
            [1]
        );
        
        View::render('users/index.php', ['users' => $users]);
    }
    
    public function show($id)
    {
        $user = $this->db->query(
            'SELECT * FROM users WHERE id = ?',
            [$id]
        );
        
        if (empty($user)) {
            // Handle not found
            View::render('404.html');
            return;
        }
        
        View::render('users/show.php', ['user' => $user[0]]);
    }
}

Using Database in Models

The base Models class uses the Database class internally:

Sphp/Core/Models.php

class Models
{
    protected $db;
    
    public function __construct()
    {
        $this->env = require('../app/config/config.php');
        $this->db = new Database($this->env);
    }
    
    public function select(array $columns, array $where = [], string $orderBy = '', int $limit = 0)
    {
        // Build query...
        return $this->db->query($query, $params);
    }
}

Security Features

Prepared Statements

All queries use PDO prepared statements with parameter binding to prevent SQL injection.

Automatic Escaping

PDO automatically escapes parameters, eliminating manual sanitization needs.

Type Safety

Parameters are properly typed and bound to prevent type juggling attacks.

Connection Security

PDO connection uses secure MySQL connection protocols.

Best Practices

Always Use Parameters

// UNSAFE - Never do this!
$id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $id";
$result = $db->query($query);

// SAFE - Always use prepared statements
$id = $_GET['id'];
$result = $db->query('SELECT * FROM users WHERE id = ?', [$id]);

Use Models When Possible

For standard CRUD operations, use the Models class instead of raw queries:

// Instead of:
$users = $this->db->query('SELECT * FROM users WHERE verified = ?', [1]);

// Use:
$userModel = new Users();
$users = $userModel->select(['*'], ['verified' => 1]);

Handle Empty Results

$users = $db->query('SELECT * FROM users WHERE id = ?', [$id]);

if (empty($users)) {
    // Handle no results found
    return null;
}

$user = $users[0];

Use Transactions for Multiple Operations

try {
    $db->connection->beginTransaction();
    
    $db->query('INSERT INTO users (...) VALUES (...)', [...]);
    $db->query('INSERT INTO user_profiles (...) VALUES (...)', [...]);
    
    $db->connection->commit();
} catch (Exception $e) {
    $db->connection->rollBack();
    throw $e;
}

Environment Variables

Set up your .env file for database configuration:

.env

DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=my_database
DB_USERNAME=my_user
DB_PASSWORD=my_password

Getting Started

Core Concepts

Building Applications

CLI Commands

Advanced

​Overview

​Database Configuration

​Database Class

​Constructor

​Connection Property

​Creating a Database Instance

​Query Method

​Return Value

​Using Prepared Statements

​SELECT Queries

​INSERT Queries

​UPDATE Queries

​DELETE Queries

​Working with Query Results

​Accessing Results

​Single Record Queries

​Complex Queries

​JOIN Operations

​Aggregate Functions

​Using Database in Controllers

​Using Database in Models

​Security Features