### Connect to DBMS (PHP)

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Demonstrates how to establish a connection to a database using DbSimple. It shows examples using the static connect method and the constructor with different DSN syntaxes, including PDO DSN.

```PHP
require_once "DbSimple/Generic.php";
$DB = DbSimple_Generic::connect("pgsql://login:password@host/database");

// OR

require_once "DbSimple/Connect.php";
$DB = new DbSimple_Connect("pgsql://login:password@host/database");

// OR using PDO DSN syntax
require_once "DbSimple/Connect.php";
$DB = new DbSimple_Connect("mysqli:unix_socket=/cloudsql/app:instance;user=root;pass=;dbname=testdb");
```

--------------------------------

### Macro-Substitutions in SQL Queries #2 (PHP)

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

An example showcasing dynamic SQL generation using DbSimple's macro-substitution feature. This snippet is incomplete but illustrates the pattern of embedding conditional SQL logic.

```PHP
$rows = $DB->select('
        SELECT *
        FROM 
            goods g

```

--------------------------------

### DbSimple API Documentation

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Documentation for the core DbSimple library methods, covering connection, data retrieval, query execution, and transaction management. It details function signatures, parameters, and return types for various database operations.

```APIDOC
DbSimple Core API:

connect(string $dsn)
  Static function to connect to any database using DSN syntax.
  Parameters:
    - dsn: The Data Source Name string (e.g., "pgsql://login:password@host/database").
  Returns: A DbSimple connection object.

select(string $query [, $arg1...])
  Executes a SELECT query and returns the result as a 2D-array.
  Parameters:
    - query: The SQL query string, supporting placeholders.
    - $arg1...: Placeholder arguments for the query.
  Returns: A 2D-array representing the result set, or FALSE on error.

selectRow(string $query [, $arg1...])
  Fetches a single row from the result of a query.
  Parameters:
    - query: The SQL query string.
    - $arg1...: Placeholder arguments.
  Returns: An associative array representing the first row, or FALSE.

selectCol(string $query [, $arg1...])
  Fetches a single column from the result of a query.
  Parameters:
    - query: The SQL query string.
    - $arg1...: Placeholder arguments.
  Returns: An array containing values from the first column, or FALSE.

selectCell(string $query [, $arg1...])
  Fetches a single cell (one value) from the result of a query.
  Parameters:
    - query: The SQL query string.
    - $arg1...: Placeholder arguments.
  Returns: The scalar value of the first cell, or FALSE.

selectPage(int &$total, string $query [, $arg1...])
  Fetches a 2D-array of rows for a specific page and calculates the total number of found rows.
  Parameters:
    - total: A reference to an integer variable that will be populated with the total count of matching rows.
    - query: The SQL query string, often including LIMIT clauses for pagination.
    - $arg1...: Placeholder arguments.
  Returns: A 2D-array of rows for the current page, or FALSE on error.

query(string $query [, $arg1...])
  Executes a non-SELECT query (e.g., INSERT, UPDATE, DELETE).
  For INSERT queries, it returns the last inserted ID.
  Parameters:
    - query: The SQL query string.
    - $arg1...: Placeholder arguments.
  Returns: The last inserted ID for INSERTs, or TRUE for other successful queries, or FALSE on error.

transaction(mixed $parameters = null)
  Starts a new database transaction.
  Parameters:
    - parameters: Optional parameters for transaction control.
  Returns: TRUE on success, FALSE on failure.

commit()
  Commits the current database transaction.
  Returns: TRUE on success, FALSE on failure.

rollback()
  Rolls back the current database transaction.
  Returns: TRUE on success, FALSE on failure.

Reserved Aliases/Comments:
  - ARRAY_KEY*: Modify result representation format.
  - Attributed SQL comments (e.g., for caching).
```

--------------------------------

### SQL Query Placeholders and Array Handling

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Illustrates various placeholder types for constructing SQL queries safely and efficiently. Includes list-based placeholders for IN clauses, associative placeholders for SET clauses, identifier placeholders for table/column names, and prefix-based placeholders.

```APIDOC
List-based Placeholder (?a):
  Used to insert multiple values into a query, typically for IN clauses.
  Example:
    $ids = array(1, 101, 303);
    $DB->select('SELECT name FROM tbl WHERE id IN(?a)', $ids);
    // Resulting SQL: SELECT name FROM tbl WHERE id IN(1, 101, 303)

Associative Placeholder (?a):
  Used to insert key-value pairs, commonly for UPDATE SET clauses.
  Example:
    $row = array(
      'id'   => 10,
      'date' => "2006-03-02"
    );
    $DB->query('UPDATE tbl SET ?a', $row);
    // MySQL: UPDATE tbl SET `id`='10', `date`='2006-03-02'

Identifier Placeholder (?#):
  Used to insert SQL identifiers (table or column names), automatically quoted.
  Example:
    $DB->select('SELECT ?# FROM tbl', 'date');
    // MySQL: SELECT `date` FROM tbl
    // FireBird: SELECT "date" FROM tbl

Identifier-list Placeholder (?#):
  Used to insert multiple SQL identifiers, automatically quoted.
  Example:
    $user = array('user_id', 'user_name', 'user_age');
    $DB->query('INSERT INTO user(?#) VALUES(?a)', array_keys($user), array_values($user));

Prefix-based Placeholder (?_):
  Used to automatically prepend a defined table prefix to table names.
  Example:
    $DB->setIdentPrefix('phpbb_');
    $DB->select('SELECT * FROM ?_user');
    // Resulting SQL: SELECT * FROM phpbb_users
```

--------------------------------

### Fetch One Page of Results (PHP)

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Shows how to use the `selectPage` method to retrieve a subset of rows for pagination. This method also populates a variable with the total count of matching records, essential for pagination controls.

```PHP
// Variable $totalNumberOfUsers will hold total number of found rows.
$rows = $DB->selectPage(
    $totalNumberOfUsers,
    'SELECT * FROM ?_user LIMIT ?d, ?d',
    $pageOffset, $numUsersOnPage
);
```

--------------------------------

### Fetch All Result Rows (PHP)

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Illustrates how to execute a SELECT query using DbSimple's `select` method and iterate over the returned 2D-array of rows. It shows accessing data by column name.

```PHP
$rows = $DB->select('SELECT * FROM ?_user LIMIT 10');
foreach ($rows as $numRow => $row) {
    echo $row['user_name'];
}
```

--------------------------------

### Macro-Substitutions in SQL Queries (PHP)

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Demonstrates DbSimple's feature of conditional macro-blocks (`{}`) within SQL queries. This allows for dynamic query construction based on PHP conditions, like including an `activated_at` filter only if a POST variable is set.

```PHP
$rows = $DB->select('
        SELECT *
        FROM goods
        WHERE 
            category_id = ?
          { AND activated_at > ? } 
        LIMIT ?d
    ',
    $categoryId,
    (empty($_POST['activated_at'])? DBSIMPLE_SKIP : $_POST['activated_at']),
    $pageSize
);
```

--------------------------------

### Conditional SQL Query Parts

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Demonstrates how to include conditional parts within SQL queries using curly braces. This allows for dynamic query construction based on runtime conditions, such as checking if POST parameters are empty.

```PHP
$DB->select(
        'SELECT category_id, category_name FROM category c JOIN category_group g ON c.id = g.category_id AND 1 = ? { AND c.name = ? } LIMIT ?d',
        (empty($_POST['cat_name'])? DBSIMPLE_SKIP : 1),
        (empty($_POST['cat_name'])? DBSIMPLE_SKIP : $_POST['cat_name']),
        $pageSize
    );
```

--------------------------------

### Query Logging

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Allows logging of executed SQL queries and their performance metrics. A custom logger function can be registered to capture query details, including execution time and returned rows.

```PHP
$DB->setLogger('myLogger');
$rows = $DB->select('SELECT * FROM U_GET_PARAM_LIST');

function myLogger($db, $sql)
{
  $caller = $db->findLibraryCaller();
  $tip = "at ".@$caller['file'].' line '.@$caller['line'];
  // Print the query (better to use Debug_HackerConsole)
  echo "<xmp title=\"$tip\">\\
"; print_r($sql); echo "</xmp>\\
";
}

// Output example:
// SELECT * FROM U_GET_PARAM_LIST;
//   --- 13 ms = 4+3+6; returned 30 row(s);
```

--------------------------------

### Prepare-Execute Optimization

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

The DbSimple library optimizes repeated queries by automatically preparing statements only once, even when executed multiple times within a loop. This enhances performance for batch operations.

```PHP
foreach ($array as $item) {
  // DbSimple understands that it should execute "prepare" only once!
  $DB->query('INSERT INTO tbl(field) VALUES(?)', $item);
}
```

--------------------------------

### Query Result Fetching Methods

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Provides various methods for retrieving query results tailored to different needs. This includes fetching a single row, a single cell (value), an entire column, multi-dimensional arrays, and linked tree structures.

```APIDOC
Single Row Fetching (selectRow):
  Fetches a single row from the query result as an associative array.
  Example:
    $row = $DB->selectRow('SELECT * FROM ?_user WHERE user_id=?', $uid);

Single Cell Fetching (selectCell):
  Fetches the value of a single cell (the first column of the first row) from the query result.
  Example:
    $userName = $DB->selectCell('SELECT user_name FROM ?_user WHERE user_id=?', $uid);

Single Column Fetching (selectCol):
  Fetches all values from a single column as a numerically indexed array.
  Can also fetch a column with custom keys using 'AS ARRAY_KEY'.
  Example:
    $cities = $DB->selectCol('SELECT city_name FROM ?_cities');
    $citiesById = $DB->selectCol('SELECT city_id AS ARRAY_KEY, city_name FROM ?_cities');

Multi-dimensional Associative Array Fetching:
  Fetches results into a multi-dimensional array by specifying multiple 'AS ARRAY_KEY_N' aliases.
  Example:
    $messagesByTopics = $DB->select(
        'SELECT 
            message_topic_id AS ARRAY_KEY_1,
            message_id AS ARRAY_KEY_2,
            message_subject, message_text
        FROM 
            ?_message'
    );
    // Access: $messagesByTopics[topicId][messageId] = messageData

Linked Tree Fetching:
  Fetches data into a structure suitable for representing hierarchical relationships, using 'AS ARRAY_KEY' and 'AS PARENT_KEY'.
  Example:
    $forest = $DB->select(
      'SELECT 
        person_id        AS ARRAY_KEY, 
        person_father_id AS PARENT_KEY,
        *
      FROM ?_person'
    );
```

--------------------------------

### Error Handling and Disabling

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Provides mechanisms for handling database errors, including setting custom error handlers and temporarily disabling error reporting for specific operations using the '@' operator.

```APIDOC
Custom Error Handler:
  Set a custom function to handle database errors.
  Example:
    $DB = DbSimple_Generic::connect('mysql://test:test@localhost1/non-existed-db');
    $DB->setErrorHandler('databaseErrorHandler');

    function databaseErrorHandler($message, $info) {
        if (!error_reporting()) return;
        echo "SQL Error: $message<br><pre>"; print_r($info); echo "</pre>";
        exit();
    }

Temporary Error Disabling:
  Use the '@' operator to suppress errors for a specific query and handle them manually.
  Example:
    if (!@$DB->query('INSERT INTO tbl(id, field) VALUES(1, ?)', $field)) {
      // Reaction on query error
      // Error context available via $DB->error property
      $DB->query('UPDATE tbl SET field=? WHERE id=1', $field);
    }
```

--------------------------------

### Query Result Caching

Source: https://github.com/jay-izex-llc/dbsimple/blob/master/README.txt

Enables caching of query results to improve performance. Results can be cached for a specified duration or invalidated based on table modification timestamps.

```APIDOC
Time-Based Caching:
  Cache results for a specific duration using a special comment in the query.
  Example:
    // CACHE: 10h 20m 30s
    $row = $DB->select('SELECT * FROM table WHERE id=123');

  A custom cacher function can be set using setCacher().
  Example:
    $DB->setCacher('myCacher');
    function myCacher($key, $value) {
        // Store or retrieve from cache
    }

Dependency-Based Caching:
  Cache results until specified table modification timestamps change.
  Example:
    // CACHE: 10h 20m 30s, forum.modified, topic.modified
    $row = $DB->select(
        'SELECT * 
        FROM forum JOIN topic ON topic.forum_id=forum.id 
        WHERE id=123'
    );
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.