Model Class

Overview

The Model class handles all database operations in MINI. It uses PDO for secure database access with prepared statements. Located at: application/model/model.php

In MINI, all database methods are in a single Model class for simplicity. In larger applications, you might create separate model classes for different entities.

Class Definition

model.php

class Model
{
    /**
     * @param object $db A PDO database connection
     */
    function __construct($db)
    {
        try {
            $this->db = $db;
        } catch (PDOException $e) {
            exit('Database connection could not be established.');
        }
    }
}

Constructor

`__construct($db)`

Initializes the model with a PDO database connection.

PDO

required

A PDO database connection object, passed from the Controller.

Example:

// In Controller class:
$this->model = new Model($this->db);

Read Operations

`getAllSongs()`

Retrieve all songs from the database.

public function getAllSongs()
{
    $sql = "SELECT id, artist, track, link FROM song";
    $query = $this->db->prepare($sql);
    $query->execute();

    // fetchAll() is the PDO method that gets all result rows
    return $query->fetchAll();
}

return

array

Array of song objects. Each object has properties: id, artist, track, link.

Usage:

// In controller:
$songs = $this->model->getAllSongs();

// In view:
foreach ($songs as $song) {
    echo htmlspecialchars($song->artist, ENT_QUOTES, 'UTF-8');
    echo htmlspecialchars($song->track, ENT_QUOTES, 'UTF-8');
}

`getSong($song_id)`

Get a single song by ID.

public function getSong($song_id)
{
    $sql = "SELECT id, artist, track, link FROM song WHERE id = :song_id LIMIT 1";
    $query = $this->db->prepare($sql);
    $parameters = array(':song_id' => $song_id);

    $query->execute($parameters);

    // fetch() is the PDO method that gets exactly one result
    return $query->fetch();
}

song_id

int

required

The ID of the song to retrieve.

return

object|false

Song object with properties: id, artist, track, link. Returns false if not found.

Usage:

// In controller:
$song = $this->model->getSong(17);

if ($song) {
    echo $song->artist; // Access as object property
}

`getAmountOfSongs()`

Get the total count of songs.

public function getAmountOfSongs()
{
    $sql = "SELECT COUNT(id) AS amount_of_songs FROM song";
    $query = $this->db->prepare($sql);
    $query->execute();

    // fetch() gets exactly one result
    return $query->fetch()->amount_of_songs;
}

return

int

Total number of songs in the database.

Create Operations

`addSong($artist, $track, $link)`

Insert a new song into the database.

public function addSong($artist, $track, $link)
{
    $sql = "INSERT INTO song (artist, track, link) VALUES (:artist, :track, :link)";
    $query = $this->db->prepare($sql);
    $parameters = array(':artist' => $artist, ':track' => $track, ':link' => $link);

    $query->execute($parameters);
}

artist

string

required

The artist name.

track

string

required

The track/song name.

link

string

required

A link to the song (YouTube, Spotify, etc.).

PDO automatically escapes all input, protecting against SQL injection. You don’t need to manually sanitize parameters.

Usage:

// In controller:
if (isset($_POST['submit_add_song'])) {
    $this->model->addSong(
        $_POST['artist'],
        $_POST['track'],
        $_POST['link']
    );
    
    header('location: ' . URL . 'songs');
}

Update Operations

`updateSong($artist, $track, $link, $song_id)`

Update an existing song.

public function updateSong($artist, $track, $link, $song_id)
{
    $sql = "UPDATE song SET artist = :artist, track = :track, link = :link WHERE id = :song_id";
    $query = $this->db->prepare($sql);
    $parameters = array(
        ':artist' => $artist, 
        ':track' => $track, 
        ':link' => $link, 
        ':song_id' => $song_id
    );

    $query->execute($parameters);
}

artist

string

required

The updated artist name.

track

string

required

The updated track name.

link

string

required

The updated link.

song_id

int

required

The ID of the song to update.

Usage:

// In controller:
if (isset($_POST['submit_update_song'])) {
    $this->model->updateSong(
        $_POST['artist'],
        $_POST['track'],
        $_POST['link'],
        $_POST['song_id']
    );
    
    header('location: ' . URL . 'songs');
}

Delete Operations

`deleteSong($song_id)`

Delete a song from the database.

public function deleteSong($song_id)
{
    $sql = "DELETE FROM song WHERE id = :song_id";
    $query = $this->db->prepare($sql);
    $parameters = array(':song_id' => $song_id);

    $query->execute($parameters);
}

song_id

int

required

The ID of the song to delete.

This is a permanent deletion. In production applications, consider soft deletes or adding confirmation dialogs.

Usage:

// In controller:
public function delete($song_id)
{
    // In a real app, add authentication and authorization checks
    $this->model->deleteSong($song_id);
    
    header('location: ' . URL . 'songs');
}

Debugging Queries

MINI includes a helper function to debug PDO queries:

$sql = "SELECT id, artist, track FROM song WHERE id = :song_id LIMIT 1";
$query = $this->db->prepare($sql);
$parameters = array(':song_id' => $song_id);

// Debug: see the actual SQL that will be executed
echo Helper::debugPDO($sql, $parameters);
// Output: SELECT id, artist, track FROM song WHERE id = '5' LIMIT 1

$query->execute($parameters);

Use Helper::debugPDO() during development to see the exact SQL query with parameters substituted. Remove these calls in production.

Creating Custom Model Methods

You can add your own methods to the Model class:

// Search songs by artist
public function searchSongsByArtist($search_term)
{
    $sql = "SELECT id, artist, track, link FROM song WHERE artist LIKE :search";
    $query = $this->db->prepare($sql);
    $parameters = array(':search' => '%' . $search_term . '%');
    
    $query->execute($parameters);
    return $query->fetchAll();
}

// Get songs with pagination
public function getSongsPaginated($offset, $limit)
{
    $sql = "SELECT id, artist, track, link FROM song LIMIT :offset, :limit";
    $query = $this->db->prepare($sql);
    
    // Bind parameters with explicit types for LIMIT clause
    $query->bindValue(':offset', (int)$offset, PDO::PARAM_INT);
    $query->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    
    $query->execute();
    return $query->fetchAll();
}

// Get songs ordered by date
public function getRecentSongs($limit = 10)
{
    $sql = "SELECT id, artist, track, link FROM song ORDER BY id DESC LIMIT :limit";
    $query = $this->db->prepare($sql);
    $query->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    
    $query->execute();
    return $query->fetchAll();
}

PDO Best Practices

Always Use Prepared Statements

Never concatenate user input directly into SQL queries:

// NEVER DO THIS (SQL Injection vulnerability):
$sql = "SELECT * FROM song WHERE id = " . $_GET['id'];
$query = $this->db->query($sql);

// ALWAYS DO THIS:
$sql = "SELECT * FROM song WHERE id = :id";
$query = $this->db->prepare($sql);
$query->execute([':id' => $_GET['id']]);

Use Named Placeholders

Named placeholders (:name) are more readable than positional placeholders (?):

// Named (recommended):
$parameters = array(':artist' => $artist, ':track' => $track);

// Positional (less clear):
$parameters = array($artist, $track);

Handle Fetch Results Properly

// fetchAll() returns an array (even if empty)
$songs = $query->fetchAll();
if (count($songs) > 0) { /* ... */ }

// fetch() returns object or FALSE
$song = $query->fetch();
if ($song) { /* ... */ }

Don't Sanitize Input, Escape Output

PDO handles input escaping automatically. Your job is to escape output in views:

// In model: Store raw input (PDO handles escaping)
$this->model->addSong($_POST['artist'], $_POST['track'], $_POST['link']);

// In view: Escape output to prevent XSS
echo htmlspecialchars($song->artist, ENT_QUOTES, 'UTF-8');

Advanced Patterns

Transactions

public function transferData($from_id, $to_id, $amount)
{
    try {
        $this->db->beginTransaction();
        
        // Multiple queries
        $this->deductFromAccount($from_id, $amount);
        $this->addToAccount($to_id, $amount);
        
        $this->db->commit();
    } catch (Exception $e) {
        $this->db->rollBack();
        throw $e;
    }
}

Joins

public function getSongsWithAlbums()
{
    $sql = "SELECT s.id, s.artist, s.track, a.album_name 
            FROM song s 
            LEFT JOIN album a ON s.album_id = a.id";
    $query = $this->db->prepare($sql);
    $query->execute();
    
    return $query->fetchAll();
}

Last Insert ID

public function addSongAndGetId($artist, $track, $link)
{
    $sql = "INSERT INTO song (artist, track, link) VALUES (:artist, :track, :link)";
    $query = $this->db->prepare($sql);
    $parameters = array(':artist' => $artist, ':track' => $track, ':link' => $link);
    
    $query->execute($parameters);
    
    // Get the auto-increment ID
    return $this->db->lastInsertId();
}

Controller Class

Learn how controllers use models

Database Guide

Complete database usage guide

CRUD Operations

Step-by-step CRUD tutorial

Helper Functions

PDO debugging utilities

Core Classes

Helpers

Model Class

Overview

Class Definition

Constructor

`__construct($db)`

Read Operations

`getAllSongs()`

`getSong($song_id)`

`getAmountOfSongs()`

Create Operations

`addSong($artist, $track, $link)`

Update Operations

`updateSong($artist, $track, $link, $song_id)`

Delete Operations

`deleteSong($song_id)`

Debugging Queries

Creating Custom Model Methods

PDO Best Practices

Advanced Patterns

Transactions

Joins

Last Insert ID

See Also

Controller Class

Database Guide

CRUD Operations

Helper Functions

Build docs developers (and LLMs) love

Core Classes

Helpers

​Overview

​Class Definition

​Constructor

​__construct($db)

​Read Operations

​getAllSongs()

​getSong($song_id)

​getAmountOfSongs()

​Create Operations

​addSong($artist, $track, $link)

​Update Operations

​updateSong($artist, $track, $link, $song_id)

​Delete Operations

​deleteSong($song_id)

​Debugging Queries

​Creating Custom Model Methods

​PDO Best Practices

​Advanced Patterns

​Transactions

​Joins

​Last Insert ID

​See Also

Controller Class

Database Guide

CRUD Operations

Helper Functions

Build docs developers (and LLMs) love

Overview

Class Definition

Constructor

`__construct($db)`

Read Operations

`getAllSongs()`

`getSong($song_id)`

`getAmountOfSongs()`

Create Operations

`addSong($artist, $track, $link)`

Update Operations

`updateSong($artist, $track, $link, $song_id)`

Delete Operations

`deleteSong($song_id)`

Debugging Queries

Creating Custom Model Methods

PDO Best Practices

Advanced Patterns

Transactions

Joins

Last Insert ID

See Also