Skip to content

Observium Database Functions

This documentation page provides an overview of how the Observium database functions work. Understanding these functions is essential for anyone looking to extend Observium or troubleshoot issues related to data storage and retrieval.

Introduction

Observium primarily uses MySQL or MariaDB as its database management system for storing and managing data. Observium has built-in functions that handle common database operations, such as connecting to the database, querying, updating, and deleting data.

Fetching Multiple Rows

The dbFetchRows() function is used to fetch multiple rows from a database query as an array of associative arrays.

function dbFetchRows($sql, $parameters = array(), $print_query = FALSE)
{
  // Fetches multiple rows from a database query as an array of associative arrays
}

Arguments

  • $sql: The SQL query string.
  • $parameters (Optional): An array of parameters to bind to the SQL query. This helps to prevent SQL injection attacks and is useful when using placeholders in the $sql argument.
  • $print_query (Optional): A boolean value to determine whether the SQL query should be printed for debugging purposes. Default is FALSE.

Example

To fetch all rows from the devices table:

$sql = "SELECT * FROM devices";
$rows = dbFetchRows($sql);

foreach ($rows as $row) {
  echo "Device ID: " . $row['device_id'] . ", Hostname: " . $row['hostname'] . "\n";
}

Fetching a Single Row

The dbFetchRow() function is used to fetch a single row from a database query as an associative array.

function dbFetchRow($sql, $parameters = array(), $print_query = FALSE)
{
  // Fetches a single row from a database query as an associative array
}

Arguments

  • $sql: The SQL query string.
  • $parameters (Optional): An array of parameters to bind to the SQL query. This helps to prevent SQL injection attacks and is useful when using placeholders in the $sql argument.
  • $print_query (Optional): A boolean value to determine whether the SQL query should be printed for debugging purposes. Default is FALSE.

Example

To fetch a row with a specific device_id from the devices table:

$sql = "SELECT * FROM devices WHERE device_id = ?";
$parameters = array(6);
$row = dbFetchRow($sql, $parameters);

if ($row) {
  echo "Device ID: " . $row['device_id'] . ", Hostname: " . $row['hostname'] . "\n";
} else {
  echo "No device with ID 6 found in the devices table.\n";
}

Fetching a Single Cell

The dbFetchCell() function is used to fetch a single cell from a database query.

function dbFetchCell($sql, $parameters = array(), $print_query = FALSE)
{
  // Fetches a single cell from a database query
}

Arguments

  • $sql: The SQL query string.
  • $parameters (Optional): An array of parameters to bind to the SQL query. This helps to prevent SQL injection attacks and is useful when using placeholders in the $sql argument.
  • $print_query (Optional): A boolean value to determine whether the SQL query should be printed for debugging purposes. Default is FALSE.

Example

To fetch the hostname for a specific device_id from the devices table:

$sql = "SELECT hostname FROM devices WHERE device_id = ?";
$parameters = array(6);
$hostname = dbFetchCell($sql, $parameters);

if ($hostname) {
  echo "Hostname for device ID 6: " . $hostname . "\n";
} else {
  echo "No device with ID 6 found in the devices table.\n";
}

Inserting Data

To insert data into the database, Observium uses the dbInsert() function. This function accepts two arguments: an associative array containing the data to insert, and the name of the table in which to insert the data. The function returns the ID of the inserted row or false if there is an error.

function dbInsert($data, $table)
{
  // Insert data into the specified table and return the ID of the inserted row
  // Returns false if there is an error
}

Arguments

  • $data: An associative array containing the data to insert. The keys of the array should correspond to the column names in the database table.
  • $table: The name of the table in which to insert the data.

Example

To insert a new device into the devices table:

$data = [
  'hostname' => 'new_device.example.com',
  'sysName' => 'New Device',
  'os' => 'linux',
  'hardware' => 'x86_64',
  'version' => '1.0',
  'features' => 'N/A',
  'location' => 'Data Center 1'
];

$inserted_id = dbInsert($data, 'devices');
echo "Inserted device with ID: {$inserted_id}\n";

Updating Data

The dbUpdate() function is used to update existing data in the database. This function takes three arguments: an associative array containing the updated data, the name of the table to update, and a string containing the SQL WHERE clause to determine which rows to update. The function returns the number of affected rows or false if there is an error.

function dbUpdate($data, $table, $where)
{
  // Update data in the specified table using the provided WHERE clause
  // Returns the number of affected rows or false if there is an error
}

Arguments

  • $data: An associative array containing the updated data. The keys of the array should correspond to the column names in the database table.
  • $table: The name of the table in which to update the data.
  • $where: A string containing the SQL WHERE clause used to determine which rows to update.

Example

To update the location of a specific device in the devices table:

$data = [
  'location' => 'Data Center 2'
];

$affected_rows = dbUpdate($data, 'devices', 'device_id = 5');
echo "Updated {$affected_rows} rows\n";

Deleting Data

The dbDelete() function is used to delete data from the database. This function accepts two arguments: the name of the table from which to delete the data, and a string containing the SQL WHERE clause to determine which rows to delete. The function returns the number of affected rows or false if there is an error.

function dbDelete($table, $where)
{
  // Delete data from the specified table using the provided WHERE clause
  // Returns the number of affected rows or false if there is an error
}

Arguments

  • $table: The name of the table from which to delete the data.
  • $where: A string containing the SQL WHERE clause used to determine which rows to delete.

Example

To delete a specific device from the devices table:

$affected_rows = dbDelete('devices', 'device_id = 5');
echo "Deleted {$affected_rows} rows\n";

With these database functions, you can interact with the Observium database to store, retrieve, update, and delete data. Remember to always handle database operations carefully and validate user input to prevent SQL injection attacks.

Inserting Multiple Rows

The dbInsertMulti() function is used to insert multiple rows of data into the specified database table in a single query. This function is particularly useful for improving performance when inserting a large amount of data at once.

function dbInsertMulti($data, $table, $columns = NULL, $print_query = FALSE)
{
  // Attempts to insert the data into the specified table
  // Check for boolean false to determine whether insert failed
}

Arguments

  • $data: A multi-dimensional array containing the data to be inserted. Each sub-array represents a row of data to be inserted. The keys of the sub-arrays should correspond to the column names in the database table.
  • $table: The name of the table in which to insert the data.
  • $columns (Optional): An array containing the column names in the same order as the keys in the $data array. If not provided, the function will use the keys from the first sub-array in $data.
  • $print_query (Optional): A boolean value to determine whether the SQL query should be printed for debugging purposes. Default is FALSE.

Example

To insert multiple rows of data into the devices table:

$data = [
  [
    'device_id' => 6,
    'hostname' => 'device1.example.com',
    'location' => 'Data Center 1'
  ],
  [
    'device_id' => 7,
    'hostname' => 'device2.example.com',
    'location' => 'Data Center 2'
  ]
];

$last_insert_id = dbInsertMulti($data, 'devices');
echo "Last inserted ID: {$last_insert_id}\n";

This function returns the ID of the last inserted row, or false if the insert operation fails. Note that when using this function, it's essential to ensure that the data is correctly formatted and validated to prevent SQL injection attacks.

Checking for Existing Rows

The dbExist() function is used to check if any rows exist in the specified table that match the given conditions. This function can be useful when determining if a row with specific criteria already exists in the database before performing other actions.

function dbExist($table, $where = NULL, $parameters = array(), $print_query = FALSE)
{
  // Returns true if a row exists in the specified table with the given conditions
}

Arguments

  • $table: The name of the table in which to search for the existing row.
  • $where (Optional): A string containing the WHERE clause to filter the rows. This should not include the WHERE keyword itself or any trailing semicolon.
  • $parameters (Optional): An array of parameters to bind to the WHERE clause in the SQL query. This helps to prevent SQL injection attacks and is useful when using placeholders in the $where argument.
  • $print_query (Optional): A boolean value to determine whether the SQL query should be printed for debugging purposes. Default is FALSE.

Example

To check if a row with a specific device_id exists in the devices table:

$where = 'device_id = ?';
$parameters = array(6);

if (dbExist('devices', $where, $parameters)) {
  echo "A device with ID 6 exists in the devices table.\n";
} else {
  echo "No device with ID 6 found in the devices table.\n";
}

This function returns a boolean value, true if a row matching the specified conditions exists in the table and false otherwise. Make sure to validate and escape any user-provided data used in the $where argument to prevent SQL injection attacks.