Database Queries

Table of contents

  1. Overview
  2. DB Class
  3. Using Models
  4. Joins

1. Overview Table of Contents

There are two ways to perform database queries in this framework. You can use queries or the functions that comes with your models or base model classes.


2. DB Class Table of Contents

You can perform a query within this framework by using the query function from the DB class. The query function has 3 parameters:

  1. $sql - The database query we will submit to the database.
  2. $params - The values for the query. They are the fields of the table in our database. The default value is an empty array.
  3. $class - A default value of false, it contains the name of the class we will build based on the name of a model.

An example can be found in the findUserByAcl function from the Users model. An example is shown below:

/**
 * Retrieves a list of users who are assigned to a particular acl.
 *
 * @param string $acl The ACL we want to use in our query.
 * @return object Users who are assigned to a specific acl.
 */
public static function findUserByAcl($acl) {
    $aclName = '["'.$acl.'"]';
    return self::$_db->query("SELECT * FROM users WHERE acl = ?", [$aclName]);
}

All the user has to do is create a classic SQL query as the first parameter. Since we want to find a list of ACLs we use aclName as the parameter that we will bind using the PDO class. By using the built in query function the user does not have to be concerned with the actual binding of values or calling the execute function of the PDO class.


Here is another example shown below:

use Core\DB;
use Core\Helper;
$db = DB::getInstance();
$sql = "SELECT * FROM contacts";
$contacts = $db->query($sql);
Helper::dd($contacts);

Below is the result using the dd function:

SQL query example

Figure 1 - SQL query example

As shown in Figure 1 all the information returned from the database is represented as an object. The PDOStatement value has been expanded to show the actual query. The _result section shows all of your contacts.

You can learn more about SQL through this link to The Odin Project’s Database Course.


A. Create

The insert function performs our create operation on our database. An example is shown below:

use Core\DB;
use Core\Helper;
$db = DB::getInstance();

$fields = [
    'fname' => 'John',
    'lname' => 'Doe',
    'email' => 'example@email.com'
];
$contacts = $db->insert('contacts', $fields);
Helper::dd($contacts);

This function accepts two arguments:

  1. $table - The name of the table we want to perform the insert operation.
  2. $fields - An associative array of key value pairs. The key is the name of the database field and the value is the value we will set to a particular field. The default value is an empty array.


B. Read

Users can perform find operations using the DB class with the find function using parameters such as conditions, bind, order, limit, and sort. An example is shown below:

use Core\DB;
use Core\Helper;
$db = DB::getInstance();

$contacts = $db->find('contacts', [
    'conditions' => ["user_id = ?"],
    'bind' => ['1'],
    'limit' => 2,
    'sort' => 'DESC'
]);
Helper::dd($contacts);
DB Class Find Function

Figure 2 - DB Class Find Function

As shown above in figure 2, we need to first specify the table. In this case we want to look through our contacts table. Next, we set our parameters. Here we use the user_id field as the condition, bind to it the id of 1, limit the results to the first 2, and sort in descending order.

This function accepts the following parameters:

  1. $table - The name of the table that contains the records we want to retrieve.
  2. $params - An associative array that contains key value pair parameters for our query such as conditions, bind, limit, offset, join, order, and sort. The default value is an empty array.
  3. $class A default value of false, it contains the name of the class we will build based on the name of a model.

C. Update

You can used the DB class’ update function to update a record as shown below:

use Core\DB;
use Core\Helper;
$db = DB::getInstance();

$fields = [
    'fname' => 'John',
    'email' => 'example@email.com'
];
$contacts = $db->update('contacts', 3, $fields);

This function accepts 3 parameters:

  1. $table - The name of the table that contains the record we want to update.
  2. $id - The primary key for the record we want to remove from the database table.
  3. $fields - An associative array containing key value pairs containing information we want to update.


D. Delete

The delete function performs delete operations. This is the simples of all our functions to use as shown below:

$contacts = $db->delete('contacts', 3);

It accepts the following arguments:

  1. $table - The name of the table that contains the record we want to delete.
  2. $id The primary key for the record we want to remove from a database table.


E. Checking If Value Exists In Column

Sometimes you need to check if a value exists in a column in the form of an element in an array. The valueExistsInColumn makes this possible. Let’s check out the manageACLsAction below where we separate ACLs into used and unused.

public function manageACLsAction(): void {
    $acls = ACL::getACLs();
    $usedAcls = [];
    $unUsedAcls = [];
    foreach($acls as $acl) {
        if($acl->isAssignedToUsers()) {
            array_push($usedAcls, $acl);
        } else {
            array_push($unUsedAcls, $acl);
        }
    }

    $this->view->usedAcls = $usedAcls;
    $this->view->unUsedAcls = $unUsedAcls;
    $this->view->render('admindashboard/manage_acls');
}

Here, we want to create two separate arrays. One for used ACLs and another for unused. That way we can list them separately. We do this to prevent administrators from editing used ACLs. This doesn’t prevent administrators from generating queries from making any necessary changes from the MySQL command line or adding such feature through this framework. Just make sure you test appropriately in development before making you changes in production.


F. Summary

Many of these functions have their equivalent wrapper functions that will be described in the Using Models section. Here are the descriptions for additional functions:

  1. count - Getter function for the private _count variable.
  2. findFirst - Returns the first result performed by an SQL query.
  3. findTotal - Returns number of records in a table.
  4. first - Returns first result in the _result array.
  5. lastID - The primary key ID of the last insert operation.
  6. results - Returns value of query results. We usually chain this as a call with another function for our Model classes. Here is an example we will discuss in later sections: 
     $users = Users::findUserByAcl($acl->acl)->results();


3. Using Models Table of Contents

We will go over using the model class for updating the database. Each function will be discussed in the following sections.


A. assign 

$user->assign($_POST, ['csrf_token'], true); // blacklist example

This method allows mass assignment using an array. You can also pass a blacklist or whitelist to control which fields get updated.


B. delete

The delete function is the easiest to use. Simply call this function with your object as shown below:

$contact->delete()

When you implement the afterDelete and beforeDelete functions in your models they get called when you perform a delete operation.

C. find

The find function is the most commonly used function. It is a wrapper function for the DB class’ find function. Throughout this framework this function is used in several of our model classes to perform various tasks. It accepts one argument called $params. It is an associative array and contains the parameters for your query.

Within the Users model this function is used to find all users except the current logged in user. It’s usage is shown below:

public static function findAllUsersExceptCurrent($current_user_id, $params = []) {
    $conditions = [
        'conditions' => 'id != ?',
        'bind' => [(int)$current_user_id]
    ];
    // In case you want to add more conditions
    $conditions = array_merge($conditions, $params);
    return self::find($conditions);
}

It accepts two arguments:

  1. $current_user_id - The id of the currently logged in user.
  2. $params - Used to build conditions for database query. The default value is an empty array.

Since we want to find all users except current the conditions element of the $conditions array is set to the value id != ?. Finally, we set $current_user_id as the value we want to bind.

Before we move on we will go over a more complex usage. Within the ProfileImages model class we want to find all profile images for a particular user. This example is shown below:

public static function findByUserId($user_id) {
    return $images = self::find([
        'conditions' => 'user_id = ?',
        'bind' => ['user_id' => $user_id],
        'order' => 'sort'
    ]);
}

Besides the conditions we are already familiar with, this one accepts an order parameter whose value is the sort value in the profile_images table.


D. findAllByUserId

Searches a table where user_id is an index value. An example for using this function is as follows:

$contacts = Contacts::findAllByUserId((int)$this->currentUser->id, ['order'=>'lname, fname']);

It accepts two arguments:

  1. $user_id - The user ID.
  2. $params - Used to build conditions for database query. The default value is an empty array. In the example above we want to order by last name and first name.


E. findById

Use this function if you want to retrieve a record from a database using a record’s id field. An example is shown below:

$user = Users::findById((int)$id);

We cast the value is a type int for safety reasons.


F. findByTable 

$items = CartItem::findByTable('cart_items AS items', [...]);

Used for querying other tables or aliases. Example:

$items = Orders::findByTable('orders AS o', [
    'conditions' => 'o.user_id = ?',
    'bind' => [$user_id],
    'order' => 'o.created_at DESC'
]);


G. findFirst

This is a wrapper for the findFirst function found in the DB class. It accepts one argument called $params. It is an associative array and contains the parameters for your query. An example of is usage is shown below:

$user = self::findFirst([
    'conditions' => 'username = ?',
    'bind' => [$this->username]
]);

Here we search the users table for a particular username. Once we find the first record with a match the result is returned. Here we set username = ? as the condition and bind $this->username since this is the field we are interested in.

A function that uses this one is the findByIdAndUserId function in the Contacts model. Let’s take a closer look.

public static function findByIdAndUserId($contact_id, $user_id, $params = []) {
    $conditions = [
        'conditions' => 'id = ? AND user_id = ?',
        'bind' => [$contact_id, $user_id]
    ];
    $conditions = array_merge($conditions, $params);
    return self::findFirst($conditions);
}

We use this function to populate the contact details view by retrieving one contact whose id matches what we want to view along with the user who the contact is associated with. It accepts three parameters:

  1. $contact_id - The ID of the contact whose details we want.
  2. $user_id - The ID user associated with this contact. $user_id is a field that is used as an index for a one-to-many relationship.
  3. $params - Used to set additional conditions. The default value is an empty array.

With this function we use $contact_id and $user_id to set out binding values. Next we merge the $conditions and $params arrays and uses this updated $conditions array as the $parameters for the findFirst function.


H. findTotal

Use this function to find the total number of records based on parameters provided. Let’s look at this example from the the indexAction for the ContactsController.

$pagination = new Pagination($page, 10, Contacts::findTotal([
    'conditions' => 'user_id = ?',
    'bind'       => [$this->currentUser->id]
]));

When setting up the Pagination object we use this function to set the value for total items. In this case, we want to know how many contacts are associated with a particular users. user_id is the index and the current user’s id is the value we want to bind.


I. Lifecycle Hooks

  • onConstruct()
  • beforeSave() / afterSave()
  • beforeDelete() / afterDelete()

These can be optionally implemented in child models.


J. save

This function is most commonly used in the controllers. It is a function that performs insert and update operations. When you implement the afterSave and beforeSave functions in your model classes any tasks within those functions are performed when using this function.

There are two ways to use this function. You can call it standalone as follows:

$user->save()

You can also us it as a condition as part of an if statement.

if($user->save()) {
    Router::redirect('admindashboard/details/'.$user->id);
}

In the code block above we want to test if the user is saved before we perform a redirect within our controller’s action function.

Finally, this function checks if the record has an ID. If it does, it performs an update; otherwise, it inserts a new record.


K. timeStamps 

public function beforeSave(): void {
    $this->timeStamps();
}

Automatically sets created_at and updated_at.


L. Validation 

public function validator(): void {
    $this->runValidation(new UniqueValidator($this, ['field' => 'acl', 'message' => 'That acl already exists.  Please chose a new one.']));
    $this->runValidation(new RequiredValidator($this, ['field' => 'acl', 'message' => 'ACL name is required.'])); 
}

Validation must be defined in the validator() method. save() will not proceed if _validates is false.


M. Model Summary

Method Description
save() Inserts or updates record
delete() Deletes or soft-deletes record
find() Finds multiple records
findFirst() Finds first match
findById() Find by primary key
assign() Mass-assigns properties
timeStamps() Sets created/updated timestamps
validator() Runs before save to validate form data
getColumnsForSave() Returns associative array of DB fields


4. Joins Table of Contents

🔍 Understanding $conditions vs. Raw SQL

This framework allows complex SQL queries to be expressed as a structured $conditions array, which is internally parsed and converted into raw SQL by the DB class. Here’s how each part of the $conditions array translates into SQL:


🧱 $conditions Array

$conditions = [
    'columns' => 'products.*, 
                  ANY_VALUE(pi.url) AS url, 
                  ANY_VALUE(brands.name) AS brand',
    'joins' => [
        ['product_images', 'products.id = pi.product_id', 'pi'],
        ['brands', 'products.brand_id = brands.id']
    ],
    'conditions' => 'products.featured = 1 AND products.deleted = 0 AND pi.sort = 0',
    'group' => 'products.id'
];
return self::find($conditions);


🧾 Equivalent Raw SQL

$sql = "
    SELECT 
        products.*, 
        ANY_VALUE(pi.url) AS url, 
        ANY_VALUE(brands.name) AS brand
    FROM products
    INNER JOIN product_images pi ON products.id = pi.product_id
    INNER JOIN brands ON products.brand_id = brands.id
    WHERE products.featured = 1 
      AND products.deleted = 0 
      AND pi.sort = 0
    GROUP BY products.id
";
return $this->query($sql)->results();


🧩 Mapping Breakdown

$conditionskey SQL Component
'columns' SELECT products.id, products.name, ...
'joins' INNER JOIN product_images pi ... INNER JOIN brands ...
'conditions' WHERE products.featured = 1 AND ...
'group' GROUP BY products.id


✅ Why Use $conditions?

  • Cleaner code: Easier to read and modify than raw SQL.
  • Safer queries: Auto-handles quoting and condition logic.
  • Dynamic behavior: Can be reused in models and passed around as arrays.


🧠 Notes for Developers The 'joins' array uses the format:

  • ['table_name', 'join_condition', 'alias', 'join_type (optional)'] If join_type is omitted, it defaults to INNER JOIN.
  • The columns, conditions, and group fields are used directly in the generated SQL string.
  • The framework’s DB::_read() method builds the final SQL using these parameters — including the GROUP BY clause (now that you’ve added it).