Using SystemDB

$SystemDB is a global variable in SCHLIX CMS that lets you perform queries to the database. In your code, simply add global $SystemDB before add

A. Retrieval of data

All data will be retrieved as an associative array.

(i) Multiple Row

If you expect multiple rows to be returned from your SQL statement, simply use getQueryResultArray() method

    /**
     * Return a list of approved comments
     * @global \SCHLIX\cmsDatabase $SystemDB
     * @param string $app
     * @param string $unique_id
     * @return array
     */
    public function getApprovedComments($app, $unique_id) {
        global $SystemDB;

        $sanitized_app = sanitize_string($app);
        $sanitized_unique_id = sanitize_string($unique_id);

        $sql = "SELECT * FROM app_simplecomments_items WHERE status = 1 AND category_id = 1 AND app={$sanitized_app} AND app_unique_item_id = {$sanitized_unique_id} ORDER BY id DESC";
        return $SystemDB->getQueryResultArray($sql);
    } 

(ii) Single Row

If you expect only a single row to be returned from your SQL statement, simply use getQueryResultSingleRow() method.

    /**
     * Return category ID by full directory
     * @global \SCHLIX\cmsDatabase $SystemDB
     * @param string $full_dir
     * @return int
     */
    private function getCategoryIDByFullDirectory($full_dir)
    {
        global $SystemDB;

        $sanitized_parent_path = sanitize_string($full_dir);
        $sql = "SELECT {$this->field_category_id} FROM  {$this->table_categories} WHERE full_dir = {$sanitized_parent_path}";
        $parent = $SystemDB->getQueryResultSingleRow($sql);
        $parent_id = (int) $parent[$this->field_category_id];
        return $parent_id;

    }

B. Insert/Update/Delete query

To execute an SQL statement, use the query() method

For example:

            $sql = "UPDATE {$this->table_items} SET status = 1, category_id = 1 WHERE {$this->field_id} = 2";
            $SystemDB->query($sql);

C. Sanitizing string to prevent SQL injection

To prevent SQL injection, use sanitize_string() method like the examples above or use prepared statements. You don't need to call bindParam as the data from the associative array will be automatically bound to the variable name. SCHLIX CMS still uess MySQLi class instead of PDO, however the syntax is compatible with that of PDO.

For example:

 $data['id'] = 20;
 $sql = "SELECT * FROM my_table WHERE id = :id";
 $result = $SystemDB->getQueryResultArray($sql, $data);

For a more complete information, please view the API documentation about \SCHLIX\cmsDatabase