Building the Model
The model handles all database interactions for the countries module. In Trongate v2, models extend the Model class and are stored in the module directory as Countries_model.php.
The Complete Model
<?php
/**
* Countries_model - Handles data operations for country records.
*
* Demonstrates proper data conversion patterns and separation
* of concerns between database operations and presentation logic.
*/
class Countries_model extends Model {
private string $table_name = 'countries';
/**
* Return the list of searchable column names (used by the controller for validation).
*
* @return array<string>
*/
public function get_searchable_columns(): array {
return ['country_title', 'country_code'];
}
/**
* Get country form data from POST and prepare it for database or view display.
*
* @return array Form data with proper types
*/
public function get_data_from_post(): array {
return [
'country_title' => post('country_title', true),
'country_code' => post('country_code', true)
];
}
/**
* Retrieve a single record from the database by ID.
*
* @param int $update_id Record ID.
* @param bool $prepare_for_display Whether to pass the record through prepare_record_for_display().
* @return array|bool Associative array on success or false on failure.
*/
public function get_data_from_db(int $update_id, bool $prepare_for_display = false): array|bool {
$record_obj = $this->db->get_where($update_id, $this->table_name);
if ($record_obj === false) {
return false;
}
if ($prepare_for_display === true) {
$record_obj = $this->prepare_record_for_display($record_obj);
}
return (array) $record_obj;
}
/**
* Retrieve a record for editing or delete confirmation (no display transformations).
*
* @param int $update_id Record ID.
* @return array|bool Associative array on success or false on failure.
*/
public function get_data_for_edit(int $update_id): array|bool {
return $this->get_data_from_db($update_id, false);
}
/**
* Find a record by ID and return it as an object.
*
* @param int $update_id Record ID.
* @return object|bool Record object on success or false on failure.
*/
public function find_by_id(int $update_id): object|bool {
return $this->db->get_where($update_id, $this->table_name);
}
/**
* Fetch paginated countries records.
*
* @param int $limit Records per page
* @param int $offset Records to skip
* @return array<object>
*/
public function fetch_records(int $limit, int $offset): array {
$sql = 'SELECT * FROM '.$this->table_name.' ORDER BY id LIMIT '.$limit.' OFFSET '.$offset;
return $this->db->query($sql, 'object');
}
/**
* Count all countries records in the database table.
*
* @return int Total number of countries records.
*/
public function count_all(): int {
return $this->db->count($this->table_name);
}
/**
* Search countries records across searchable columns.
*
* @param string $query The search query string.
* @param string $column Specific column to search, or empty string to search all.
* @param int $limit Maximum records to return.
* @param int $offset Records to skip.
* @return array<object>
*/
public function search_records(string $query, string $column, int $limit, int $offset): array {
$searchable_columns = $this->get_searchable_columns();
// Validate column against whitelist before interpolating into SQL.
if ($column !== '' && !in_array($column, $searchable_columns, true)) {
$column = '';
}
if ($column !== '') {
$sql = 'SELECT * FROM '.$this->table_name.' WHERE '.$column.' LIKE :query ORDER BY id LIMIT '.$limit.' OFFSET '.$offset;
} else {
$conditions = [];
foreach ($searchable_columns as $col) {
$conditions[] = $col.' LIKE :query';
}
$sql = 'SELECT * FROM '.$this->table_name.' WHERE ('.implode(' OR ', $conditions).') ORDER BY id LIMIT '.$limit.' OFFSET '.$offset;
}
return $this->db->query_bind($sql, ['query' => '%'.$query.'%'], 'object');
}
/**
* Count search results for countries records.
*
* @param string $query The search query string.
* @param string $column Specific column to search, or empty string to search all.
* @return int Number of matching records.
*/
public function count_search_results(string $query, string $column): int {
$searchable_columns = $this->get_searchable_columns();
// Validate column against whitelist before interpolating into SQL.
if ($column !== '' && !in_array($column, $searchable_columns, true)) {
$column = '';
}
if ($column !== '') {
$sql = 'SELECT COUNT(*) as total FROM '.$this->table_name.' WHERE '.$column.' LIKE :query';
} else {
$conditions = [];
foreach ($searchable_columns as $col) {
$conditions[] = $col.' LIKE :query';
}
$sql = 'SELECT COUNT(*) as total FROM '.$this->table_name.' WHERE ('.implode(' OR ', $conditions).')';
}
$result = $this->db->query_bind($sql, ['query' => '%'.$query.'%'], 'object');
return (int) ($result[0]->total ?? 0);
}
/**
* Prepare multiple countries records for display in list views.
*
* @param array $rows Array of country record objects from database
* @return array Array of objects with formatted display fields
*/
public function prepare_records_for_display(array $rows): array {
$prepared = [];
foreach ($rows as $row) {
$prepared[] = $this->prepare_record_for_display($row);
}
return $prepared;
}
/**
* Prepare raw country database data for display in views.
*
* @param object $record_obj Raw data from database
* @return object Enhanced data with formatted fields
*/
public function prepare_record_for_display(object $record_obj): object {
$record_obj->country_title = trim($record_obj->country_title);
$record_obj->country_code = trim($record_obj->country_code);
return $record_obj;
}
/**
* Create a new country record.
*
* @param array $data Country data
* @return int Returns the ID of the newly created record
*/
public function create_new_record(array $data): int {
return $this->db->insert($data, $this->table_name);
}
/**
* Update an existing country record.
*
* @param int $update_id The ID of the record to update
* @param array $data The data to update
* @return void
*/
public function update_record(int $update_id, array $data): void {
$this->db->update($update_id, $data, $this->table_name);
}
/**
* Delete a country record.
*
* @param int $update_id The ID of the record to delete
* @return void
*/
public function delete_record(int $update_id): void {
$this->db->delete($update_id, $this->table_name);
}
}Method by Method
get_searchable_columns()
Returns an array of column names that the search feature can query. Both country_title and country_code are searchable. This method is called by the controller to validate the user's column selection before building SQL, and by the model's own search_records() and count_search_results() methods. Centralising the list here ensures that adding or removing a searchable column only requires one change.
get_data_from_post()
Retrieves submitted form data and sanitises it. The true parameter passed to post() tells Trongate to strip HTML tags and encode special characters. This method is called in two places: when displaying an empty create form (no previous data), and in the submit() method to build the array for database insertion or update.
get_data_from_db(int $update_id, bool $prepare_for_display = false)
Fetches a single record by its primary key using $this->db->get_where(). If no record is found it returns false instead of terminating - this lets the controller decide how to handle missing records (typically by showing the "not found" page).
The optional $prepare_for_display parameter lets callers request display-ready data in a single call. This is useful for the show() method, which needs formatted data for the detail view.
get_data_for_edit(int $update_id)
A convenience wrapper around get_data_from_db() that always returns raw data (no display transformations). Used by the delete confirmation flow to verify the record exists without altering its data.
find_by_id(int $update_id)
Returns the raw database object for a record, or false if not found. Used by submit_delete() to verify that the record still exists before attempting deletion.
fetch_records(int $limit, int $offset)
Returns a paginated set of records sorted by id (the order in which they were inserted). The LIMIT and OFFSET values come from the controller's get_limit() and get_offset() helpers, which in turn read the current page number from the URL segment and the user's per-page preference from the session.
count_all()
Returns the total number of records. The controller's pagination helper calls this to calculate how many pages are needed.
search_records() and count_search_results()
These methods handle searching across the configured searchable columns. They accept a query string and an optional column name. If a specific column is provided, the search is scoped to that column; otherwise all searchable columns are searched with OR.
Both methods use query_bind() with named parameters (the :query placeholder) to prevent SQL injection. The column name is validated against the whitelist from get_searchable_columns() before being interpolated into the SQL.
prepare_records_for_display() and prepare_record_for_display()
These methods let you transform raw database data before sending it to the view. In this simple example they trim whitespace from both fields, but in a real-world module you might use them to format dates, join related data, or compute derived values. The plural method iterates over an array and delegates to the singular method, keeping your code clean and reusable.
create_new_record()
Inserts a new record and returns the auto-generated id. The controller uses this return value to redirect the user appropriately after creation.
update_record() and delete_record()
Simple wrappers around $this->db->update() and $this->db->delete(). Keeping these in the model rather than calling the database methods directly from the controller maintains clean separation of concerns.
Naming Convention
Remember that the model file must be named after the module: Countries_model.php. The class inside the file must match the filename: class Countries_model extends Model.
We're continually improving the Trongate documentation. If anything is incorrect, unclear, incomplete, or could be better, we'd genuinely appreciate your input.
Share your thoughts in the Documentation Feedback.