eduTrac SIS Query

Are you new to the eduTrac SIS online user's manual? If so, click here for a starting point. If you are looking for technical documentation, then check out the Wiki.


When using the eduTrac SIS query functions, you don’t need to know SQL (Structured Query Language). If you do know SQL, then great but if not, you only need to familiarize yourself with the different query functions that can be used..

The first thing you must understand about querying the database is that you must call the app’s global scope. For example if you are using a query in a custom function, then you need to call the $app’s global scope which can be called in one of two ways:

function custom_etsis_function() {
    $app = \Liten\Liten::getInstance();


function custom_etsis_function() {
    global $app;

The first example is the preferred and recommended way to call the app’s global scope.

Now that you have access to the $app global scope, you need to append that with the connection to the database:


Once you create a connection to the database, you can start building your query. All the information that follows should help you with writing the queries you need.


Connect to a database table by calling the table() method.

$prog = $app->db->table('acad_program');

An alternative to above is calling the database table name as a method.

$prog = $app->db->acad_program();


When calling the insert(array $data) method, $data can be passed as a dimensional array to insert a new record.

    "acadProgCode" => "GMA.6938",
    "acadProgTitle" => "Program Title",
    "statusDate" => $prog->NOW(),
    "deptCode" => 'PSYCH'


    "acadProgTitle" => "New Program Title"
    ->where("acadProgTitle", "Program Title")


Save() is a shortcut to insert() or update().


$prog = $app->db->acad_program();
$prog->acadProgTitle = "Program Title";
$prog->programDesc = "This is the academic program's description text.";


$prog->acadProgTitle = "New Program Title";
$prog->where("acadProgTitle = 'Program Title'");


$prog->where("acadProgTitle", "Program Title")->delete();


Count all the entries based on where() clause.

$count = $prog->where('deptCode', 'PSYCH')->count();

Use count for a specific column name.

$count = $prog->where('deptCode', 'PSYCH')->count('acadProgID');


Max based on where() clause.

$max = $prog->where($x, $y)->max($columnName);


Min based on where() clause.

$min = $prog->where($x, $y)->min($columnName);


Sum based on where() clause.

$sum = $prog->where($x, $y)->sum($columnName);


Average based on where clause.

$avg = $prog->where($x, $y)->avg($columnName);


$agg = $prog->where($x, $y)->aggregate("GROUP_CONCAT $columnName");


Returns a single record is found otherwise it will return false.

$prog->where('acadProgID', 364)->findOne();

You can achieve the same above by using only the primary key and dropping the where clause.


Retrieving the entry.

if ($prog) {
    echo " $prog->acadProgTitle";
    // On a retrieved entry you can perform update or delete.
    $prog->startDate = $prog->NOW();


Find returns an ArrayIterator of rows found, otherwise it will return false.

$acad_prog = $prog->where('acadProgTitle', 'Program Title')->find();

foreach ($acad_program as $program) {
    echo "{$program->acadProgDesc}";

// On a retrieved entry you can perform update or delete
$prog->startDate = $prog->NOW();

Find also accepts a closure ( find(Closure $callback) ) to perform data manipulation.

$prog->where('acadProgTitle', 'New Program Title');

$result = $prog->find(function ($data) {
    $newResult = [];
    foreach ($data as $d) {
        $d["new_prog_title"] = "{$data["acadProgTitle"]}";
        $newResult[] = $d;
    return $newResult;

return $result;


Select All


Select Columns

$prog->select("acadProgCode, acadProgTitle")
     ->select("acadProgDesc, startDate, currStatus");


Where can be used to setup the where clauses and they work with find(), findOne(), update(), and delete(). This is the same for the where aliases as well.Repetitive call to where and it’s aliases will append to each other using the AND ( _and_() )operator. Use _or_() to mimic the OR operator.


$prog->where("acadProgTitle", "Program Title");
$prog->where("acadProgID > ?", 25);
$prog->where("deptCode in (?, ?, ?)", "ECE", "MATH", "EDUC");
$prog->where("(currStatus, acadYearCode)", [ ['A'], ['2014', '2015'] ]);


There where aliases can help shorten the where examples above.

Primary key


Not Equal To

$prog->whereNot('acadProgID', 456);


$prog->whereLike('acadProgTitle', 'Prog%');

Not Like

$prog->whereNotLike('acadProgTitle', 'Prog%');

Greater Than

$prog->whereGt('startDate', '2014-09-14');

Greater Than Equal To

$prog->whereGte('startDate', '2014-09-14');

Less Than

$prog->whereLt('startDate', '2014-09-14');

Less Than Equal To

$prog->whereLte('startDate', '2014-09-14');

Where In

$prog->whereIn('deptCode', [ 'EDUC', 'MATH' ]);

Where Not In

$prog->whereNotIn('deptCode', [ 'EDUC', 'MATH' ]);

Where Null


Where Not Null


Where with OR and AND

Use _and_() / _or_() chained to any where clauses.


$prog->where("acadProgID", 456)->_and_()->whereGte("startDate", '2014-09-14');


$prog->where("acadProgID", 456)->_or_()->whereGte("acadProgID", 456)->_or_()->where("deptCode", "MATH");

Order, Group, Limit, Offset

$prog->orderBy('acadProgID', 'DESC');


 * Defaults to LEFT JOIN, for others, use INNER, RIGHT, etc as the
 * $join_operator
 * join( $tablename, $constraint, $table_alias , $join_operator )
$prog->_join('department', 'acad_program.deptCode = department.deptCode');

$prog->_join('department', 'acad_program.deptCode = dept.deptCode', 'dept', 'INNER');



Last Modified: Jan 5, 2017 @ 6:19 PM

Was this article helpful?

Related Articles

You must be logged in to reply to this topic.