Database Interaction in Venddor IO

Introduction to Database Management

Navigating through the intricacies of database management in Venddor IO is vital for developers. This segment of our guide delves deep into the diverse facets associated with interfacing and manipulating databases within the Venddor IO framework.

Database Connection

Venddor IO handles database connections through a centralized system that ensures efficient resource management. The framework automatically establishes connections when needed and manages connection pooling to optimize performance.

Database Query Functions

Venddor IO provides a suite of functions for interacting with the database, making it easier to execute common operations:

• db_query(): Executes a raw SQL query
• db_get_array(): Retrieves multiple rows as an array
• db_get_row(): Fetches a single row
• db_get_field(): Returns a single field value
• db_get_fields(): Gets an array of field values
• db_get_hash_array(): Returns an array indexed by a specified field

Query Building with Placeholders

To prevent SQL injection and simplify query building, Venddor IO supports placeholders in SQL queries:

$result = db_get_array(
    "SELECT * FROM ?:products WHERE product_id > ?i AND status = ?s", 
    100, 
    'A'
);

Common placeholders include:

• ?: - Table prefix placeholder
• ?i - Integer value
• ?s - String value (sanitized)
• ?a - Array (handled appropriately based on context)
• ?u - Update set (for UPDATE queries)
• ?p - Already prepared data (use with caution)

Transaction Management

For operations that require multiple database changes to be atomic, Venddor IO provides transaction management functions:

db_start_transaction();
try {
    // Perform database operations
    db_query("INSERT INTO ?:products ...");
    db_query("UPDATE ?:product_prices ...");
    
    db_commit_transaction();
} catch (Exception $e) {
    db_rollback_transaction();
    throw $e;
}

Best Practices

When working with the Venddor IO database system, consider these best practices:

1. Always use placeholders to prevent SQL injection
2. Keep transactions as short as possible
3. Use appropriate indexes to optimize query performance
4. Avoid raw SQL when Venddor IO functions provide better alternatives
5. When debugging, use the built-in database profiler to identify slow queries