1. Home
  2. tinyCampaign
  3. tinyCampaign Query

tinyCampaign Query

When using the tinyCampaign (tinyC) 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_tinyc_function() {
    $app = \Liten\Liten::getInstance();
}

Or

function custom_tinyc_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:

$app->db

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.

Table

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

$cpgn = $app->db->table('campaign');

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

$cpgn = $app->db->campaign();

Insert

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

$cpgn->insert([
    "subject" => "Test Campaign",
    "from_name" => "tinyC",
    "from_email" => "no_replay@tc.com,
    "sendstart" => "2017-01-30 12:00:00"
]);

Update

$cpgn->set([
    "subject" => "tinyC is Better Than Ever"
])
    ->where("subject", "Test Campaign")
    ->update();

Save

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

Insert

$cpgn = $app->db->campaign();
$cpgn->acadProgTitle = "Test Campaign";
$cpgn->sendstart = "2017-01-29 21:30:00";
$cpgn->save();

Update

$cpgn->acadProgTitle = "tinyC is Better Than Ever";
$cpgn->where("subject = 'Test Campaign'");
$cpgn->save();

Delete

$cpgn->where("subject", "Test Campaign")->delete();

Count

Count all the entries based on where() clause.

$count = $cpgn->where('subject', 'Test Campaign')->count();

Use count for a specific column name.

$count = $cpgn->where('subject', 'Test Campaign')->count('id');

Max

Max based on where() clause.

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

Min

Min based on where() clause.

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

Sum

Sum based on where() clause.

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

Avg

Average based on where clause.

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

Aggregate

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

FindOne

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

$cpgn->where('id', 364)->findOne();

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

$cpgn->setStructure('id');
$cpgn->findOne(364);

Retrieving the entry.

if ($cpgn) {
    echo " $cpgn->subject";
    
    // On a retrieved entry you can perform update or delete.
    $cpgn->sendstart = $cpgn->NOW();
    $cpgn->save();
}

Find

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

$campaign = $cpgn->where('subject', 'Test Campaign')->find();

foreach ($campaign as $message) {
    echo "{$message->html}";

// On a retrieved entry you can perform update or delete
$cpgn->sendstart = $cpgn->NOW();
$cpgn->save();
}

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

$cpgn->where('subject', 'Test Campaign');

$result = $cpgn->find(function ($data) {
    $newResult = [];
    
    foreach ($data as $d) {
        $d["new_subject"] = "{$data["subject"]}";
        $newResult[] = $d;
    }
    
    return $newResult;
});

return $result;

Select

Select All

$cpgn->select()

Select Columns

$cpgn->select("id, subject")
     ->select("sendstart, sendfinish, viewed");

Where

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.

Examples

$cpgn->where("subject", "Test Campaign");
 
$cpgn->where("id > ?", 25);
 
$cpgn->where("id in (?, ?, ?)", "1", "364", "500");
 
$cpgn->where("(owner, status)", [ ['15'], ['ready', 'processing'] ]);

Aliases

There where aliases can help shorten the where examples above.

Primary key

$cpgn->wherePK(456);

Not Equal To

$cpgn->whereNot('id', 456);

Like

$cpgn->whereLike('subject', 'Test%');

Not Like

$cpgn->whereNotLike('subject', 'Test%');

Greater Than

$cpgn->whereGt('sendstart', '2014-09-14');

Greater Than Equal To

$cpgn->whereGte('sendstart', '2014-09-14');

Less Than

$cpgn->whereLt('sendstart', '2014-09-14');

Less Than Equal To

$cpgn->whereLte('sendstart', '2014-09-14');

Where In

$cpgn->whereIn('status', [ 'ready', 'processing' ]);

Where Not In

$cpgn->whereNotIn('status', [ 'ready', 'processing' ]);

Where Null

$cpgn->whereNull('sendfinish');

Where Not Null

$cpgn->whereNotNull('sendfinish');

Where with OR and AND

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

_and_()

$cpgn->where("id", 456)->_and_()->whereGte("sendstart", '2014-09-14');

_or_()

$cpgn->where("id", 456)->_or_()->whereGte("id", 456)->_or_()->where("status", "sent");

Order, Group, Limit, Offset

$cpgn->orderBy('id', 'DESC');
 
$cpgn->groupBy('status');
 
$cpgn->limit(10);
 
$cpgn->offset(10);

Joins

/**
 * Defaults to LEFT JOIN, for others, use INNER, RIGHT, etc as the
 * $join_operator
 *
 * join( $tablename, $constraint, $table_alias , $join_operator )
 */
 
$prog->_join('campaign_list', 'campaign.id = campaign_list.lid');

$prog->_join('campaign_list', 'campaign.id = clist.list', 'clist', 'INNER');

 

 

Updated on January 31, 2017

Was this article helpful?

Related Articles