mysqli_extended (MySQL Improved Extension Extended)

Overview

mysqli_extended is a PHP class designed to simplify and enhance database interactions with MySQL. It inherits functionality from the built-in mysqli class and adds user-friendly methods for CRUD (Create, Read, Update, Delete) operations and other common tasks. This class aims to:

• Improve Ease of Use: By providing more intuitive method names and streamlined syntax, mysqli_extended makes it easier for developers to work with MySQL databases.
• Write Cleaner Code: The streamlined interface encourages developers to write more concise and readable database code.
• Enhance Efficiency: While not a core focus, some optimizations can potentially improve code efficiency compared to using the raw mysqli class directly (performance gains might depend on your specific usage scenarios).

Key Features

• Simplified CRUD Operations: The class provides dedicated methods for insert, select, update, and delete operations, simplifying database manipulation.
• Prepared Statements Support: The class provides an execute function that can execute both regular SQL queries and prepared statements.
  • fetchAll(): Fetches all rows from a result set as an associative array. Optionally accepts a callback function that can be used to modify each row before returning the results.
  • fetchAssoc(): Fetches a single row from a result set as an associative array. Similar to fetchAll, it allows an optional callback function for row manipulation.
• Abstracted Methods: Offers user-friendly methods for common tasks, improving readability and maintainability:
  • escapeString (for mysqli_real_escape_string) - Protects against SQL injection vulnerabilities by escaping special characters within strings.
  • getInsertId - Retrieves the ID of the last inserted row after a successful insert operation.
  • affectedRows - Returns the number of rows affected by the previous insert, update, or delete query.
  • numRows - Returns the number of rows returned by the last select query.
• Error Handling: Customized implementation of handling errors/exceptions during executions.

Prepared Statements Support (Highly Recommended):

$conn = new mysqli_extended(...);

$query = "INSERT INTO users (name, email) VALUES (?, ?);
$conn->execute($query, "John Doe", "[email protected]");

• mysqli_extended provides an execute function as a unified interface for executing database queries. This function can handle both regular SQL queries and prepared statements. However, the class strongly encourages the use of prepared statements with parameter binding for several reasons:
  • Enhanced Security: Prepared statements prevent SQL injection vulnerabilities by separating the SQL query structure from the data being inserted.
  • Improved Readability: Explicit parameter binding makes your code easier to understand and maintain.
  • Reduced Risk of Errors: Parameter binding reduces the chance of syntax errors caused by manual string concatenation.

execute Function and Prepared Statements:

• The execute function showcases the seamless integration of prepared statements within the class. Here's a breakdown of its key steps:
  1. Prepares the Statement: It uses the provided query string to create a prepared statement using the prepare method (likely internal to the class).
  2. Binds Parameters (if provided): If arguments are passed to execute, it iterates through them, determines their data types, and binds them to the prepared statement using bind_param.
  3. Executes the Statement: After successful preparation and potential parameter binding, the function executes the prepared statement.
  4. Error Handling: Throughout the process, the function throws exceptions for errors during statement preparation, parameter binding, or execution.
  5. Returns the Object: If successful, the function returns the mysqli_extended object itself, allowing for method chaining

Installation

The mysqli_extended class is provided as a single file for easy integration. Here's how to install it in your project:

1. Download the mysqli_extended class files.
2. Include the downloaded file in your project using require_once or include_once.

Example Usage

// Including the class file
require_once  'path/to/mysqli_extended.php';

// Assuming you've connected to your MySQL database
$conn = new mysqli_extended($hostname, $username, $password, $database);
...
$records = $conn->execute("SELECT * FROM users")->fetchAll();
$user = $conn->select("users", "*", "id = 1")->fetchAssoc(function($x) {
    unset($x['password']);
    return $x;
});

execute(string $query, array $params?)

$conn->execute("SELECT * FROM users WHERE username = ? AND password = ?", "test", "test");
// or
$conn->execute("SELECT * FROM users WHERE username = ? AND password = ?", ["test", "test"]);
// or
$conn->execute("SELECT * FROM users");

CRUD methods leverage the execute function internally, but they abstract away the need to write the specific SQL queries for each CRUD operation.

insert(string $table, array $values)

$data = ["column_name" => "input value", ...];
$conn->insert("users", $data);

update(string $table, array $values, $condition?, $opr?)

$opr (operator) is AND by default, it is used when $condition is an array type and has multiple elements on it. Expects "AND" or "OR" value strings $condition can also be a string of a valid SQL conditional statement

$condition = ["column" => "value", ...];
$conn->update("users", $data, $condition);
// or
$conn->update("users", $data, "id = 1");
// or
$conn->update("users", $data);

select(string $table, array | string $columns, $condition?, $opr?)

$conn->select("users", "name, username", ...);
// or
$conn->select("users", ["name","username"], ...);
// fetching all columns
$all = "*"; // can be: "" or "*" or "[]" or []
$conn->select("users", $all, ...);

delete(string $table, array | string $condition, $opr?)

$conn->delete("users", "id = 1");
// or
$conn->delete("users", ["name" => "test", "username" => "test"], "AND");

fetchAll($callback?)

$results = $conn->fetchAll();
// or
$results = $conn->fetchAll(function($x) {
    // modify data here...
    return $x;
});
// output: [[...], [...], ...]

fetchAssoc($callback?)

$row = $conn->fetchAssoc();
// output: [...] (single row)

getInsertId() or lastInsertID()

$conn->insert(...);
$conn->getInsertId(); // 1
// $conn->lastInsertID();

escapeString(string $str)

$value = $conn->escapeString($str);

Security Considerations

• Always escape user input before including it in your SQL queries to prevent SQL injection vulnerabilities.
• Properly handle and validate all user input to avoid potential security issues.

Dedication

This class is inspired by the work of David Adams (Super-fast PHP MySQL Database Class)