Download the PHP package decmuc/pdodb without Composer

PDOdb – Modern PDO MySQL Database Class for PHP 8+

PDOdb is a modern, secure, and fully compatible rewrite of the popular and awesome PHP-MySQLi-Database-Class by ThingEngineer – designed as a direct replacement but built entirely on PHP’s native PDO extension.

The goal of this project is to provide a close to 1:1 logic-compatible alternative to the original class, allowing existing projects to migrate seamlessly to a safer, cleaner, and future-proof implementation – without rewriting business logic or adjusting method calls in most cases, except for a few legacy features that were intentionally dropped due to security, portability, or PDO-incompatibility.

Why switch from the original?

While the original MysqliDb class was a great utility for many years, it is no longer actively maintained and relies on the outdated mysqli_* extension. Its architecture can pose security and maintainability risks in modern applications.

PDOdb addresses these concerns and offers numerous improvements:

🔑 Key Features

• 🔄 ThingEngineer-inspired API Familiar method names and structure, modernized for PDO and security. Some legacy features were dropped intentionally.

• 🔐 Built on native PDO Replaces mysqli_* with secure and flexible PDO, using real prepared statements.

• 🧱 Modern PHP: Clean, typed, and robust Written from scratch in PHP 8+, with strict types, union types, and modern error handling.

• 🧩 Namespaced and PSR-3 compatible Ships with proper namespacing (decMuc\PDOdb) and optional PSR-3 logger support.

• ♻️ Extensible and modular Easily add custom functionality or override behavior thanks to clean class design.

• 🛡️ Secure by design Prepared statements, typed parameters, and internal validation reduce common attack vectors like SQL injection.

• 🔍 Support for complex queries JOINs, subqueries, pagination, transactions, and custom SQL – all handled seamlessly.

• 🧪 Trace and debug utilities Built-in query tracing, debug output, and caller info to help during development.

• 🔁 Multi-connection support Switch between multiple database connections on the fly ($db->connection('read'), etc.).

• 🔌 Simultaneous connections to different databases Easily connect to and switch between multiple databases (e.g. master/slave, customer-specific) via $db->addConnection() and $db->connection().

• 🚀 Bulk operations Supports insertMulti() and insertBulk() for efficient mass inserts.

• 📦 100% self-contained No external dependencies – just drop it into your project or install via Composer.

📚 Table of Contents

• Installation
• ⚠️ Warnings
• Objects Mapping (coming soon)
• Initialization
• Insert Query
• InsertMulti Query
• InsertBulk Query
• Update Query
• Select Query
• Delete Query
• Insert Data
• Insert XML
• Pagination
• Raw Queries
• Query Keywords
• Where Conditions
• Order Conditions
• Group Conditions
• Properties Sharing
• Joining Tables
• Subqueries
• SQL Conditions (EXISTS, NOT EXISTS, HAS, ...)
• Lock / Unlock
• Transactions
• Debug
  • Debugging
  • Trace and Profiling

Installation

You can install PDOdb in two ways:

🔹 1. Via Composer (recommended) Use the latest development version to always stay up to date:

Alternatively, you can use a tagged release (if available):

ℹ️ This will automatically register the autoloader and allow you to use decMuc\PDOdb\PDOdb in your project.

🔹 2. Manual installation If you don't use Composer, simply download the class and include it manually:

⚠️ Be sure to include all required class files if you separate them into multiple files/folders.

⚠️ WARNING

PDOdb is designed to mimic the behavior of ThingEngineer's MysqliDb for compatibility purposes – but just like the original class, it is not bulletproof if misused.

Here are a few important things to keep in mind:

🔐 Always validate user input – just because the class uses prepared statements internally does not mean it's safe to pass unchecked values into dynamic query components (like table names or raw SQL).

❗ Never pass untrusted input directly into methods like:

🛡️ PDOdb protects values via bound parameters, but does not validate structure, table names, or SQL fragments unless you explicitly implement those checks (e.g. via _validateTableName() or strict method usage).

❗ We strongly recommend that you whitelist table names and sanitize all dynamic input, even when using this class in trusted environments.

✅ PDOdb provides internal protection mechanisms like:

• Table name validation (_validateTableName())
• Strict parameter types
• Optional debug/tracing
• Prefix safety

Remember: even the safest database layer can't protect you from insecure logic. Security starts with architecture.

Objects Mapping (coming soon)

Initialization

You can initialize the database handler in multiple ways – either using classic parameter passing or via an associative configuration array.

Example 1: Simple Initialization with Parameters

Example 2: Initialization with Config Array

Example 3: Using an existing PDO instance

Example 4: Reusing a globally shared PDOdb instance

Multiple database connection

If you need to connect to multiple databases use following method:

To select database use connection() method

Example: Initialisation for Multi-Tenant Environments If your application handles multiple customers (tenants) with separate databases, you can encapsulate the logic in a custom factory or helper class:

Insert Query

Inserts a single row into the database.

🔁 Return value Returns the inserted ID on success (if primary key is auto-increment).

Returns true for success without auto-increment.

Returns false on failure.

🧩 Insert with SQL Functions You can pass SQL functions directly by using $db->func(), $db->now() or $db->currentDate():

Insert with on duplicate key update

InsertMulti Query

If all datasets share the same structure, the call can be simplified:

⚠️ Note: insertMulti() inserts each row individually using prepared statements. While it's secure and flexible, it is slower for large data sets. For performance-critical bulk inserts, consider using insertBulk(), which generates a single optimized SQL statement for all rows.

InsertBulk Query

The insertBulk() method allows you to insert many rows in a single SQL query, making it significantly faster than repeated single inserts or even insertMulti() in larger datasets.

This method is ideal for high-performance imports, batch processing, and when inserting thousands of records at once.

⚠️ Warning Unlike insertMulti(), insertBulk() does not return the inserted IDs, but instead gives you the total number of successfully inserted rows (row count). This trade-off ensures better speed and less overhead.

Example: Inserting 1000 products efficiently

📊 Benchmark: insertBulk vs insertMulti

We’ve included a live performance comparison script to demonstrate the time difference between insertMulti() and insertBulk():

👉 See examples/multi-bulk-benchmark.php

This benchmark inserts 1000 records using both methods and shows the execution time and total rows inserted for each approach.

Replace Query

The replace() method works similarly to insert(), but uses the SQL command REPLACE INTO instead of INSERT.

This means:

• If a row with the same primary key or unique key already exists, it will be deleted and replaced.

• If no matching key exists, it behaves like a normal INSERT.

This method is useful for upserts, but note that REPLACE deletes the existing row first, which may affect foreign keys or triggers.

Example: Replace a user

⚠️ Be aware

• REPLACE is not the same as ON DUPLICATE KEY UPDATE. It deletes the row and inserts a new one.

• Auto-increment values may change.

• Associated rows in foreign tables may break unless ON DELETE CASCADE is defined.

Update Query

update() also support limit parameter:

Select Query

After executing a get()-like method, the number of returned rows will be available in $db->count.

Select specific columns

Select a single row

Select a single row with custom expressions

Select a single value

Select a single column from multiple rows

Insert Data

❌ Legacy: loadData() and loadXml() [Removed]

These two methods were part of the original ThingEngineer MySQLi wrapper and allowed direct file imports via:

• LOAD DATA INFILE (CSV import)
• LOAD XML INFILE (XML import)

⚠️ Why were they removed?

• They rely on non-portable SQL features that bypass PDO

• They require special MySQL privileges (FILE, local_infile, etc.)

• They are incompatible with prepared statements and the security model of PDO

• They caused inconsistent behavior on shared hosting or managed environments

✅ Alternative

If you need to import CSV or XML data:

Use standard PHP functions (fgetcsv(), XMLReader, etc.)

Insert using $db->insert() or $db->insertMulti() with prepared data

Pagination

Use paginate() instead of get() to automatically handle page-based results.

Each call to paginate() updates:

$db->totalPages — number of available pages

$db->totalCount — total number of matching records

$db->count — number of results in current page

ℹ️ You can combine where(), join(), and all other query builders just like with get().

Raw Queries

You can execute any SQL query directly using rawQuery() with optional bindings:

🛠️ Helper methods for raw queries Get a single row (associative array):

Get a single value (column):

Get a single column from multiple rows:

✅ IMPORTANT!!! Correct usage Always use placeholders and bound values to avoid SQL injection:

❌ Wrong usage — do not do this:

Advanced Example Even complex UNION queries are supported:

Query Keywords

To add LOW PRIORITY | DELAYED | HIGH PRIORITY | IGNORE and the rest of the mysql keywords to INSERT (), REPLACE (), GET (), UPDATE (), DELETE() method or FOR UPDATE | LOCK IN SHARE MODE into SELECT ():

Multiple options:

In SELECT queries:

ℹ️ You can chain multiple calls to setQueryOption() if needed — each call replaces the previous options unless you manually merge them.

Method Chaining You can use method chaining to fluently build queries:

This helps keep your code clean and readable when building dynamic conditions.

Where Conditions

For a 1:1 migration of the old where() conditions from the ThingEngineer MySQLi class, you can continue to use the familiar methods. They have been improved and largely remain compatible but do not provide 100% protection against SQL injection — especially in cases like:

Since MySQL often parses integer columns by extracting the leading numeric value (e.g., 1 from 1); DROP...), such injections may silently pass through.

Recommendation

We recommend using the new, typed where* methods which offer enhanced security, such as:

• whereInt() — safe integer filtering

• whereString() — validated string values

• whereDate() — validated date input

• whereIsNull() / whereIsNotNull() — explicit NULL checks

• whereBool() — strictly typed boolean values

• whereIn() / whereNotIn() — safe set membership checks

These methods prevent common attack vectors by enforcing strict type checks and ensure that malicious input cannot be converted into dangerous SQL commands.

Simple equality check:

Custom comparison operators:

Multiple conditions:

AND / OR logic (grouped conditions):

LIKE conditions:

BETWEEN conditions:

Nested conditions using closures:

This will generate:

Warning about old where() methods

⚠️ Warning: The legacy where() methods (compatible with ThingEngineer MySQLi) are not 100% secure. They rely on parameterization but lack strict type enforcement, allowing edge cases like id = 1); DROP TABLE users -- to bypass protections because MySQL interprets the initial numeric part and ignores the rest.

*Secure typed `where()` methods**

✅ All the following typed where*() methods implement strict type validation and proper parameter binding, greatly reducing the risk of SQL injection. They should be preferred for all new code and migration efforts:

NEW whereInt() and orWhereInt() These methods enable safe and type-checked integer filtering in your SQL WHERE clauses.

What whereInt() blocks whereInt() blocks any non-integer or malformed input to prevent injection and invalid queries.

In these cases, whereInt() will raise an error or reject the input, preventing unsafe SQL from being executed.

NEW whereString() and orWhereString() These methods enable safe and type-checked string filtering in your SQL WHERE clauses.

What whereString() blocks

whereString() blocks any input containing invalid characters or potential injection payloads.

In these cases, whereString() will raise an error or reject the input, preventing unsafe SQL from being executed.

NEW whereDate() and orWhereDate() These methods enable safe and validated date filtering in your SQL WHERE clauses.

What whereDate() blocks

whereDate() blocks any input that is not a valid date or datetime string.

In these cases, whereDate() will raise an error or reject the input, preventing unsafe SQL from being executed.

NEW whereBool() and orWhereBool() These methods enable safe and strictly typed boolean filtering in your SQL WHERE clauses.

What whereBool() blocks

whereBool() blocks any input that is not strictly boolean or convertible to 0 or 1.

In these cases, whereBool() will raise an error or reject the input, preventing unsafe SQL from being executed.

NEW whereIn() and orWhereIn() These methods enable safe and parameterized filtering by sets in your SQL WHERE clauses.

What whereIn() blocks

whereIn() blocks empty arrays and ensures all values are safely bound as parameters, preventing injection.

In these cases, whereIn() will raise an error or reject the input, preventing unsafe SQL from being executed.

NEW whereNotIn() and orWhereNotIn()

These methods enable safe and parameterized exclusion filtering by sets in your SQL WHERE clauses.

What whereNotIn() blocks

whereNotIn() blocks empty arrays and ensures all values are safely bound as parameters, preventing injection.

In these cases, whereNotIn() will raise an error or reject the input, preventing unsafe SQL from being executed.

NEW whereIsNull() and orWhereIsNull() These methods allow you to filter records where a specific column is NULL.

NEW whereIsNotNull() and orWhereIsNotNull() These methods filter records where a column is NOT NULL.

Notes: whereIsNull(),orWhereIsNull(),whereIsNotNull() and orWhereIsNotNull()

• These methods do not accept a value argument – they simply append IS NULL or IS NOT NULL to the WHERE clause.
• Works with all SQL-capable fields, including strings, numbers, and dates.
• Safe to combine with other where*() and orWhere*() methods.

Sanitization: $db->escape()

In most cases, you don't need to escape or quote values manually — all values are automatically bound using prepared statements. This protects your queries from SQL injection by design.

However, if you ever need to manually escape a value (e.g., for debugging or building custom dynamic SQL), you can use the $db->escape() method:

⚠️ Note: $db->escape() has no functionality — it is a dummy method by design. It does not provide any protection against SQL injection and should not be relied on for sanitization.

If needed, you can enable real escaping by uncommenting the placeholder implementation inside the class, but this is strongly discouraged.

✅ When using this library as intended — via prepared statements and bindings — escape() is completely obsolete. All input is handled safely and efficiently without the need for manual escaping.

✅ Correct usage (safe):

❌ Wrong usage (vulnerable):

Always prefer bindings over manual escaping. Use escape() only in edge cases like debugging, logging, or custom output formatting.

Ordering method

You can sort the results using orderBy():

Order by specific values:

Using setPrefix()? If you're working with table prefixes and using fully-qualified column names in orderBy(), make sure to wrap the table name in backticks:

Grouping method

Properties sharing

It is also possible to copy properties

Subqueries

You can build subqueries and use them in WHERE, JOIN, INSERT, and other SQL statements. Subquery without alias (for use in INSERT, UPDATE, or WHERE):

Subquery with alias (for use in JOINs):

Subquery in WHERE:

Subquery in INSERT:

Subquery in SELECTS:

Subquery in JOIN:

JOINs and Join Conditions

The join() method allows you to perform SQL joins between tables. You can combine this with joinWhere() and joinOrWhere() to add conditional logic directly into the JOIN ON clause.

Basic JOIN Example Join the products table with the users table using a LEFT JOIN on tenantID:

Join Conditions (AND inside ON clause) To add additional conditions within the JOIN, use joinWhere():

🟢 joinWhere() targets the ON clause of the JOIN, not the global WHERE.

Join Conditions (OR inside ON clause) To use OR logic within the JOIN ON clause, use joinOrWhere():

Common Mistake: Mixing with where()

This puts the condition after the JOIN, which can lead to incorrect results in LEFT JOINs. Use joinWhere() if the condition should be part of the JOIN logic itself. ✅ Best Practice: Use joinWhere() and joinOrWhere() only when the condition must apply during the JOIN phase (i.e., in the ON clause), especially for LEFT JOIN, RIGHT JOIN, and OUTER JOIN scenarios.

SQL Conditions (EXISTS, NOT EXISTS, HAS, ...)

You can use subqueries inside WHERE clauses by combining them with operators like EXISTS, NOT EXISTS, IN, NOT IN, etc. This is useful when you need to check for the existence of related rows in another table.

EXISTS condition

EXISTS condition

IN condition using subquery

NOT IN condition using subquery

🟢 You can also use this pattern with multi-table JOINs or with aggregate conditions (e.g. COUNT, SUM) if supported by your SQL server.

Lock / Unlock

You can manually lock one or more tables using the default lock mode (WRITE by default).

Set the lock type globally:

Locking a single table:

Locking multiple tables:

Unlock all tables:

⚠️ Note: If an error occurs during a locked transaction, you are responsible for unlocking the tables manually.

Coming Soon: unlockOnFailure() In a future release, the method unlockOnFailure() will automatically release table locks if an exception or failure is detected during query execution. This helps prevent deadlocks and stale locks in long-running processes.

Transaction

Transactions allow you to execute multiple queries with full rollback/commit control. This is especially useful for ensuring data integrity across dependent operations.

Transaction Helpers ⚠️ Transactions only work on InnoDB tables!

Basic example — rollback on failure:

You can chain multiple statements inside the transaction block. If any one of them fails, you can safely roll back everything:

✅ You can also use unlockOnFailure() to automatically release table locks when something fails (see Locking section).

🛠️ Helper Methods

🔍 Debugging You can control the internal debug behavior using:

➡️ Enables exception logging (e.g. file log or stdout, depending on implementation)

➡️ Enables deep debugging, which also outputs:

• Final SQL query
• Bound parameters (e.g. directly via echo during runtime)

Even without $db->debug(), you can still inspect the last error manually:

⏱️ Query Execution Time Benchmarking

To measure how long each query takes, you can enable the trace mode using:

Optionally, provide a second argument to strip a common path prefix from file names in the trace output:

Now run some queries:

Trace output example:

Special Thanks

This project was originally inspired by the excellent work of ThingEngineer and his legendary PHP MySQLi Database Class. I’ve been a long-time user and fan of that class — and this rewrite in PDO is built with great respect for its simplicity and power.

However, times change — and so does PHP. With this project, I aim to bring the same developer experience to modern PDO-based applications, with enhanced error handling, multi-connection support, subquery building, and full compatibility with legacy syntax.

Thanks again to ThingEngineer for all the years of solid groundwork. 🙏

📝 License / Lizenz

This project is licensed under the MIT License. See LICENSE for details.