EMPS_DB: MySQL Abstraction Layer

The EMPS_DB class is the built‐in database abstraction layer for EMPS. It wraps MySQL operations in a set of easy-to-use methods, allowing you to interact with the database without writing raw SQL for every task. Although it currently supports only MySQL, future versions of EMPS may include abstraction layers for other SQL databases (such as PostgreSQL).

This documentation page explains the key features and usage patterns of the EMPS_DB class, with special attention to its most powerful capabilities: dynamic WHERE clause construction, row insertion/updating using structured arrays, and ensuring a row exists before updating it.

Overview

EMPS_DB provides:

  • Connection Management:
    Automatic connection to the MySQL database using configuration parameters. It handles setting character sets, autocommit mode, and disconnecting cleanly.

  • Query Execution & Timing:
    A unified query() method that executes SQL queries, logs errors, and optionally tracks execution times for performance monitoring.

  • Dynamic WHERE Clause Construction:
    The where_clause() method converts structured arrays into SQL WHERE clause strings. This allows you to build complex conditions using nested arrays and operators (e.g., $gt, $range, $like, $in, etc.).

  • Row Operations:
    Methods such as sql_insert_row(), sql_update_row(), and sql_ensure_row() let you insert and update rows using PHP arrays. This makes database updates more maintainable and less error-prone compared to constructing SQL strings manually.

  • Data Fetching and Type Conversion:
    Helper functions to fetch rows (either as associative arrays or numerically indexed arrays), plus automatic type conversion (for integer, float, etc.) using methods like row_types_plain().

  • Caching:
    Internal caching for table columns and row lookups speeds up repeated operations.

Connection and Initialization

Connecting to the Database

When an instance of EMPS_DB is created, it automatically calls the connect() method:

  • connect():
    Uses global configuration parameters ($emps_db_config) to establish a MySQL connection via mysqli_connect().
    • On success, it sets the character set (using SET NAMES) and autocommit mode.
    • Sets the operational flag to true.
    • On failure, it logs an error with details from mysqli_connect_error().

Disconnecting

  • disconnect():
    Closes the database connection with mysqli_close().

Query Execution and Error Handling

The query() Method

  • query($query):
    This is the core method to execute SQL queries.
    • It optionally measures query execution time if the constant EMPS_TIMING is enabled.
    • In case of an error, it logs detailed error messages along with a backtrace.
    • SQL errors are stored in the sql_errors array.
    • Query timing details are stored in the sql_timing array, and the cumulative SQL time is tracked in sql_time.

Escaping and Error Retrieval

  • sql_escape($txt):
    Wraps mysqli_real_escape_string() to safely escape strings before including them in SQL queries.
  • sql_error():
    Returns the last MySQL error via mysqli_error().

Dynamic WHERE Clause Construction

One of the most powerful features of EMPS_DB is its ability to build SQL WHERE clauses dynamically from structured arrays. This is achieved with the where_clause() method.

How It Works

You can build conditions using arrays. For example, consider the following condition array:

$tq = [];
$tq['status'] = 0;
$tq['tdt'] = ['$range' => [$sdt, $edt]];
$tq['debit'] = "62";
$tq['credit'] = ["90", "62"];

When you call:

$where = $emps->db->where_clause($tq);

The method iterates over each key/value pair and builds parts of the WHERE clause:

  • Simple Equality:
    For $tq['status'] = 0, it generates `status` = 0.
  • Range Conditions:
    For $tq['tdt'] = ['$range' => [$sdt, $edt]], it generates a clause like `tdt` >= $sdt and `tdt` <= $edt.
  • IN Clauses:
    When a value is an array without special operators, it builds an IN (...) clause.
  • Nested Clauses:
    The method supports special keys like $and, $or, and $nt to build nested and combined conditions.

The result is a complete SQL WHERE clause string that you can append to your queries.

Row Operations

Inserting Rows

  • sql_insert_row($table, $row):
    Inserts a new row into the specified table.
    • The $row parameter is a structured array (with a SET key holding the column values) that defines the data to insert.
    • If the cdt (creation date) and dt (update date) fields are not provided, they are set to the current timestamp.
    • The method builds the list of columns and values, executes the INSERT query, and returns the result.

Example:

$fa = [];
$fa['file_name'] = $file;
$fa['content_type'] = "audio/ogg";
$fa['md5'] = md5(uniqid(time()));
$fa['context_id'] = $record_context_id;
$fa['size'] = filesize($path);
$fa['ord'] = 100;
$emps->db->sql_insert_row("e_files", ['SET' => $fa]);

Updating Rows

  • sql_update_row($table, $row, $cond):
    Updates rows in a table based on a specified condition.
    • The $row parameter is again a structured array (with a SET key) that contains the new values.
    • If the dt field is not set (and updates are not disabled), it updates it with the current timestamp.
    • The method constructs the SQL UPDATE query and executes it.

Example:

$nr = $payload;
unset($nr['id'], $nr['cdt'], $nr['dt']);
$nr['apartment_id'] = $apartment_id;
$nr['building_id'] = $apartment['building_id'];
$nr['sdt'] = $emps->parse_time($nr['s_time']." 0:00");
$nr['edt'] = $emps->parse_time($nr['e_time']." 23:59:59");
$emps->db->sql_update_row("meters", ['SET' => $nr], "id = {$id}");

Ensuring a Row Exists

  • sql_ensure_row($table, $row, $single = false, $lock = false):
    Checks whether a row exists matching the given conditions.
    • If a matching row exists, it can optionally delete duplicates (if $single is set).
    • If no matching row is found, it inserts a new row.
    • Useful for ensuring that a record exists and then updating it.

Example:

$a = [];
$a['date'] = $date;
$a['currency_code'] = $code;
$row = $emps->db->sql_ensure_row("currency_rates", $a);
if ($row) {
    $nr = [];
    $nr['value'] = $value;
    $emps->db->sql_update_row("currency_rates", ['SET' => $nr], "id = {$row['id']}");
}

Data Fetching and Type Conversion

EMPS_DB provides various methods for retrieving data:

  • get_row($table, $where):
    Retrieves a single row that matches the given WHERE clause.
  • get_row_plain() and get_row_plain_cache():
    Lower-level methods for fetching rows without additional processing.
  • row_types_plain() / row_types():
    After fetching a row, these methods convert the values to the appropriate PHP types (e.g., integers, floats) based on the column type definitions.

This automatic type conversion makes it easier to work with database data in your PHP code.

Caching

To improve performance, EMPS_DB caches:

  • Table Columns:
    The list of columns for each table is cached in columns_cache to avoid running repeated SHOW COLUMNS queries.
  • Row Results:
    Fetched rows can be cached in row_cache for quick retrieval.

Use the clear_cache() method if you need to refresh these caches.

Putting It All Together: A Common Usage Pattern

Below is a typical workflow using EMPS_DB:

  1. Building a WHERE Clause:

    $query = [];
$query['transaction_id'] = $tr['id'];
$where = $emps->db->where_clause($query);

  2. Executing a Query and Fetching Results:

    $r = $emps->db->query("SELECT * FROM " . TP . "tr_analysis WHERE {$where}");
while ($ra = $emps->db->fetch_named($r)) {
   // Process each row, e.g. build an array of analysis records.
}

  3. Inserting or Updating Rows:

    Use sql_insert_row(), sql_update_row(), or sql_ensure_row() to manage rows based on structured arrays, as shown in the examples above.

Conclusion

The EMPS_DB class abstracts much of the complexity of interacting with MySQL. It provides:

  • A reliable connection and query execution layer.
  • Powerful dynamic WHERE clause generation from structured arrays.
  • Simplified row insertion, updating, and ensuring mechanisms.
  • Built-in caching and timing to improve performance and facilitate debugging.

For newcomers to EMPS, this abstraction layer means you can work with database records using high-level, structured commands rather than writing raw SQL every time—making your code cleaner, easier to maintain, and less prone to errors.

If you have further questions or need additional examples, please refer to the sample code in your project or consult the community resources for more in-depth discussions on EMPS_DB usage.